Problembehandlung von Transaktionsprotokollfehlern bei Azure SQL Managed Instance

  • Artikel

Gilt für:Azure SQL Managed Instance

Wenn das Transaktionsprotokoll voll ist und keine weiteren Transaktionen erfasst werden können, wird möglicherweise der Fehler 9002 oder 40552 angezeigt. Diese Fehler treten auf, wenn das Datenbanktransaktionsprotokoll, das von Azure SQL Managed Instance verwaltet wird, den Speicherplatzschwellenwert überschreitet und keine weiteren Transaktionen erfassen kann. Sie sind mit Problemen bei einem vollständigen Transaktionsprotokoll in SQL Server vergleichbar, müssen in SQL Server, Azure SQL-Datenbank und Azure SQL Managed Instance jedoch anders behandelt werden.

Hinweis

Dieser Artikel gilt für Azure SQL Managed Instance. Azure SQL Managed Instance basiert auf der neuesten stabilen Version der Microsoft SQL Server-Datenbank-Engine, weshalb sich die Inhalte größtenteils ähneln. Allerdings können sich die Optionen und Tools zur Problembehandlung von denen in SQL Server unterscheiden.

Weitere Informationen zur Problembehandlung für ein vollständiges Transaktionsprotokoll in Azure SQL-Datenbank finden Sie unter Problembehandlung von Transaktionsprotokollfehlern mit Azure SQL-Datenbank.

Weitere Informationen zur Problembehandlung beim Transaktionsprotokoll in SQL Server finden Sie unter Problembehandlung bei vollen Transaktionsprotokollen (SQL Server-Fehler 9002).

Automatisierte Sicherungen und das Transaktionsprotokoll

In Azure SQL Managed Instance werden Transaktionsprotokollsicherungen automatisch erstellt. Informationen zur Häufigkeit, Aufbewahrung und weitere Einzelheiten finden Sie unter Automatisierte Sicherungen. Informationen zum Nachverfolgen, wann automatisierte Sicherungen in SQL Managed Instance ausgeführt wurden, finden Sie unter Überwachen der Sicherungsaktivität.

Der Speicherort und der Name von Datenbankdateien können nicht verwaltet werden. Administratoren können jedoch Datenbankdateien und Einstellungen für die automatische Dateivergrößerung verwalten. Die typischen Ursachen und Lösungen bei Problemen mit dem Transaktionsprotokoll ähneln denen bei SQL Server.

Ähnlich wie bei SQL Server wird das Transaktionsprotokoll für jede Datenbank immer dann abgeschnitten, wenn eine Protokollsicherung erfolgreich abgeschlossen wird. Die Protokollkürzung löscht inaktive virtuelle Protokolldateien (VLFs) aus dem Transaktionsprotokoll und gibt so Speicherplatz in der Datei frei, ohne die Größe der Datei auf dem Datenträger zu verändern. Der leere Speicherplatz in der Protokolldatei kann dann für neue Transaktionen verwendet werden. Wenn die Protokolldatei nicht durch Protokollsicherungen abgeschnitten werden kann, wird sie vergrößert, um neue Transaktionen erfassen zu können. Erreicht die Protokolldatei in Azure SQL Managed Instance die maximal zulässige Größe, können neue Schreibtransaktionen nicht erfolgreich ausgeführt werden.

In Azure SQL Managed Instance können Sie unabhängig von der Rechenleistung Add-On-Speicher bis zu einer bestimmten Grenze erwerben. Weitere Informationen finden Sie unter Dateiverwaltung zur Freigabe von Speicherplatz.

Verhindertes Abschneiden des Transaktionsprotokolls

Informationen dazu, was das Abschneiden des Protokolls in einem bestimmten Fall verhindert, finden Sie unter log_reuse_wait_desc in sys.databases. Der Wartevorgang für die Protokollwiederverwendung informiert Sie darüber, welche Bedingungen oder Ursachen verhindern, dass das Transaktionsprotokoll durch eine reguläre Protokollsicherung abgeschnitten wird. Weitere Informationen finden Sie unter sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Die folgenden Werte von log_reuse_wait_desc in sys.databases können Aufschluss über den Grund geben, weshalb das Abschneiden des Transaktionsprotokolls der Datenbank verhindert wird:

log_reuse_wait_desc Diagnosis Reaktion erforderlich
NOTHING Typischer Zustand. Das Abschneiden des Protokolls wird nicht verhindert. Nein.
CHECKPOINT Für das Abschneiden des Protokolls ist ein Prüfpunkt erforderlich. Selten. Keine Reaktion erforderlich, es sei denn, der Zustand besteht dauerhaft. Hält der Zustand an, senden Sie eine Supportanfrage an den Azure-Support.
LOG BACKUP Eine vollständige Protokollsicherung ist erforderlich. Keine Reaktion erforderlich, es sei denn, der Zustand besteht dauerhaft. Hält der Zustand an, senden Sie eine Supportanfrage an den Azure-Support.
ACTIVE BACKUP OR RESTORE Eine Datenbanksicherung wird aktuell durchgeführt. Keine Reaktion erforderlich, es sei denn, der Zustand besteht dauerhaft. Hält der Zustand an, senden Sie eine Supportanfrage an den Azure-Support.
ACTIVE TRANSACTION Eine laufende Transaktion verhindert das Abschneiden des Protokolls. Die Protokolldatei kann aufgrund von aktiven Transaktionen und/oder Transaktionen ohne Commit nicht abgeschnitten werden. Siehe nächsten Abschnitt.
REPLICATION In Azure SQL Managed Instance kann es auftreten, wenn die Replikation oder CDC aktiviert sind. Bei anhaltenden Problemen, untersuchen Sie die Agents, die in CDC- oder Replikationsvorgänge involviert sind. Zur Problembehandlung bei CDC fragen Sie Aufträge in msdb.dbo.cdc_jobs ab. Falls keine Aufträge vorhanden sind, fügen Sie sie über sys.sp_cdc_add_job hinzu. Informationen zur Replikation finden Sie unter Suchen nach Fehlern bei der Transaktionsreplikation. Falls sich das Problem nicht lösen lässt, senden Sie eine Supportanfrage an den Azure-Support.
AVAILABILITY_REPLICA Die Synchronisierung mit dem sekundären Replikat wird ausgeführt. Keine Reaktion erforderlich, es sei denn, der Zustand besteht dauerhaft. Hält der Zustand an, senden Sie eine Supportanfrage an den Azure-Support.

Abschneiden des Protokolls wird durch eine aktive Transaktion verhindert

Die häufigste Ursache für Transaktionsprotokolle, in denen keine neuen Transaktionen erfasst werden können, sind Transaktionen mit langer Ausführungszeit oder blockierte Transaktionen.

Führen Sie diese Beispielabfrage aus, um nach Transaktionen ohne Commit oder aktiven Transaktionen und deren Eigenschaften zu suchen.

  • Gibt Informationen zu Transaktionseigenschaften aus sys.dm_tran_active_transactions zurück.
  • Gibt Sitzungsverbindungsinformationen aus sys.dm_exec_sessions zurück.
  • Gibt Anforderungsinformationen (für aktive Anforderungen) aus sys.dm_exec_requests zurück. Diese Abfrage kann auch verwendet werden, um blockierte Sitzungen zu ermitteln. Suchen Sie dazu nach request_blocked_by. Weitere Informationen finden Sie unter Sammeln von Blockierungsinformationen.
  • Gibt den Text der aktuellen Anforderung oder den Eingabepuffertext unter Verwendung der DMV sys.dm_exec_sql_text oder sys.dm_exec_input_buffer zurück. Wenn der im text-Feld von sys.dm_exec_sql_text zurückgegebene Wert NULL lautet, ist die Anforderung nicht aktiv, sondern weist eine ausstehende Transaktion auf. In diesem Fall enthält das event_info-Feld von sys.dm_exec_input_buffer die letzte an die Datenbank-Engine übergebene Anweisung.
SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

Dateiverwaltung zur Freigabe von Speicherplatz

Wenn das Abschneiden des Transaktionsprotokolls in Azure SQL Managed Instance verhindert wird, kann das Freigeben von Speicherplatz ein Lösungsansatz sein. Allerdings muss trotzdem die Ursache behandelt werden, aufgrund derer das Abschneiden der Transaktionsprotokolldatei verhindert wird. In einigen Fällen kann eine zeitintensive Transaktion durch das vorübergehende Freigeben von zusätzlichem Speicherplatz abgeschlossen werden, sodass sich die Bedingung, aufgrund derer das Abschneiden der Transaktionsprotokolldatei verhindert wurde, mit einer normalen Transaktionsprotokollsicherung auflösen lässt. Das Freigeben von Speicherplatz schafft jedoch möglicherweise nur vorübergehend Abhilfe, bis das Transaktionsprotokoll wieder größer wird.

In Azure SQL Managed Instance können Sie unabhängig von der Rechenleistung Add-On-Speicher bis zu einer bestimmten Grenze erwerben. Rufen Sie zum Beispiel im Azure-Portal die Seite Compute + Speicher auf, um den Speicherplatz in GB zu erhöhen. Informationen zu den Größenbeschränkungen für das Transaktionsprotokoll finden Sie unter Ressourceneinschränkungen für SQL Managed Instance Weitere Informationen finden Sie unter Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL-Datenbank.

Der Sicherungsspeicher wird nicht vom Speicher Ihrer SQL Managed Instance-Instanz abgezogen. Der Sicherungsspeicher ist unabhängig vom Instanzspeicherplatz, und seine Größe ist nicht begrenzt.

Fehler 9002: Das Transaktionsprotokoll für Datenbank ist voll.

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Fehler 9002 tritt in SQL Server und in Azure SQL Managed Instance aus denselben Gründen auf.

Die richtige Reaktion auf ein volles Transaktionsprotokoll hängt davon ab, aufgrund welcher Bedingungen das Protokoll gefüllt wurde.

Versuchen Sie die folgenden Methoden, um den Fehler 9002 zu beheben:

  • Das Transaktionsprotokoll wird nicht abgeschnitten und füllt den gesamten verfügbaren Speicherplatz aus.
    • Da die Sicherung des Transaktionsprotokolls in Azure SQL Managed Instance automatisch erfolgt, muss etwas anderes die Aktivität des Transaktionsprotokolls daran hindern, abgeschnitten zu werden. Eine unvollständige Replikation, CDC oder Verfügbarkeitsgruppensynchronisierung kann das Abschneiden verhindern, siehe Verhindertes Abschneiden des Transaktionsprotokolls.
  • Der reservierte Speicherplatz der SQL Managed Instance ist erschöpft, und das Transaktionsprotokoll kann nicht wachsen.
  • Die Größe des Transaktionsprotokolls ist auf einen festen Maximalwert festgelegt, oder die automatische Vergrößerung ist deaktiviert, und kann daher nicht wachsen.

Fehler 40552: Die Sitzung wurde aufgrund übermäßiger Belegung des Speicherplatzes für das Transaktionsprotokoll beendet

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

In Azure SQL Managed Instance tritt der Fehler 9002 zwar häufiger auf als der Fehler 40552, doch können beide auftreten.

Versuchen Sie die folgenden Methoden, um den Fehler 40552 zu beheben:

  • Das Problem kann bei jedem DML-Vorgang wie dem Einfügen, Aktualisieren oder Löschen auftreten. Überprüfen Sie die Transaktion, um unnötige Schreibvorgänge zu vermeiden. Versuchen Sie, die Anzahl der Zeilen zu verringern, die sofort ausgeführt werden, indem Sie Batchverarbeitung oder eine Aufteilung in mehrere kleinere Transaktionen implementieren. Weitere Informationen finden Sie unter Gewusst wie: Verbessern der Leistung von Anwendungen mithilfe von Batchverarbeitung.
  • Das Problem kann aufgrund von Vorgängen zum Neuerstellen des Indexes auftreten. Es lässt sich vermeiden, indem Sie sicherstellen, dass die folgende Formel wahr ist: (Anzahl der in der Tabelle betroffenen Zeilen) multipliziert mit (durchschnittliche Größe des aktualisierten Felds in Byte + 80) < 2 GB. Bei großen Tabellen kann es sinnvoll sein, Partitionen zu erstellen und die Indexwartung nur für einige Partitionen der Tabelle durchzuführen. Weitere Informationen finden Sie unter Erstellen partitionierter Tabellen und Indizes.
  • Versuchen Sie beim Durchführen von Masseneinfügungen mit dem Hilfsprogramm bcp.exe oder der System.Data.SqlClient.SqlBulkCopy-Klasse, die Option -b batchsize oder BatchSize zu verwenden, um die Anzahl der Zeilen zu beschränken, die bei jeder Transaktion auf den Server kopiert werden. Weitere Informationen finden Sie unter bcp Utility.
  • Wenn Sie einen Index mit der ALTER INDEX-Anweisung neu erstellen, verwenden Sie die Optionen SORT_IN_TEMPDB = ON, ONLINE = ON und RESUMABLE=ON. Bei fortsetzbaren Indizes erfolgt das Abschneiden von Protokollen. Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Nächste Schritte