Problembehandlung von Transaktionsprotokollfehlern mit Azure SQL-Datenbank

  • Artikel

Gilt für:Azure SQL-Datenbank

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 von Azure SQL-Datenbank verwaltete Datenbanktransaktionsprotokoll die Speicherschwelle überschreitet und keine weiteren Transaktionen mehr 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 konzentriert sich auf Azure SQL-Datenbank. Azure SQL-Datenbank basiert auf der letzten stabilen Version der Microsoft SQL Server-Datenbank-Engine, daher sind die Inhalte größtenteils gleich. Allerdings können sich die Optionen und Tools zur Problembehandlung von denen von SQL Server unterscheiden.

Weitere Informationen zur Problembehandlung für ein Transaktionsprotokoll in Azure SQL Managed Instance finden Sie unter Problembehandlung von Transaktionsprotokollfehlern mit Azure SQL Managed Instance.

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-Datenbank werden Transaktionsprotokollsicherungen automatisch erstellt. Informationen zur Häufigkeit, Aufbewahrung und weitere Einzelheiten finden Sie unter Automatisierte Sicherungen.

Der freie Speicherplatz, die Datenbankdateivergrößerung und der Dateispeicherort werden ebenfalls verwaltet, sodass sich die typischen Ursachen und Lösungen für Probleme mit dem Transaktionsprotokoll von denen in SQL Server unterscheiden.

Ähnlich wie bei SQL Server wird das Transaktionsprotokoll für jede Datenbank immer dann abgeschnitten, wenn eine Protokollsicherung erfolgreich abgeschlossen wird. Beim Abschneiden verbleibt leerer Speicherplatz in der Protokolldatei, der dann für neue Transaktionen verwendet werden kann. 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-Datenbank die maximal zulässige Größe, können neue Schreibtransaktionen nicht erfolgreich ausgeführt werden.

Informationen zur Größe des Transaktionsprotokolls finden Sie hier:

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;

Für Azure SQL-Datenbank wird empfohlen, eine Verbindung mit einer bestimmten Benutzerdatenbank und nicht mit der master-Datenbank herzustellen, um diese Abfrage auszuführen.

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-Datenbank kann dieses Verhalten auftreten, wenn Change Data Capture (CDC) aktiviert ist. Fragen Sie sys.dm_cdc_errors ab, und behandeln Sie mögliche Fehler. 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
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, 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 Transaktionsprotokoll in den elastischen Azure SQL-Datenbank-Pools nicht abgeschnitten werden kann, kann das Freigeben von Speicherplatz für den Pool für elastische Datenbanken 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.

Weitere Informationen zum Verwalten des Dateispeicherplatzes von Datenbanken und Pools für elastische Datenbanken finden Sie unter Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL-Datenbank.

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.

Probieren Sie die folgenden Methoden aus, um dieses Problem zu beheben:

  1. 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 SQL-Datenbankanwendungen mithilfe von Batchverarbeitung.
  2. 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.
  3. 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.
  4. 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).

Hinweis

Weitere Informationen zu Resource Governor-Fehlern finden Sie unter Fehler bei der Ressourcengovernance.

Nächste Schritte