Problembehandlung von Transaktionsprotokollfehlern bei Azure SQL Managed Instance

Gilt für:Azure SQL Managed Instance

Möglicherweise werden Fehler 9002 oder 40552 angezeigt, wenn das Transaktionsprotokoll voll ist und keine neuen Transaktionen akzeptieren kann. Diese Fehler treten auf, wenn das Datenbanktransaktionsprotokoll, das von azure SQL Managed Instance verwaltet wird, Schwellenwerte für den Speicherplatz überschreitet und Transaktionen nicht weiterhin akzeptiert. 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.

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 Vergrößerung von Dateien 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 jeder Datenbank verkürzt, sobald 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, wächst die Protokolldatei, um neue Transaktionen aufzunehmen. 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 Kürzen des Transaktionsprotokolls

Um in einem bestimmten Fall herauszufinden, was die Logtrunkierung verhindert, siehe log_reuse_wait_desc in sys.databases. Die Wartezeit für die Protokollwiederverwendung informiert Sie darüber, welche Bedingungen oder Ursachen verhindern, dass das Transaktionsprotokoll durch ein reguläres Protokoll-Backup verkürzt 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önnten darauf hindeuten, warum die Protokollverkürzung der Datenbanktransaktionen verhindert wird:

Abschneiden des Protokolls wird durch eine aktive Transaktion verhindert

Das häufigste Szenario für ein Transaktionsprotokoll, das keine neuen Transaktionen akzeptieren kann, ist eine lange ausgeführte oder blockierte Transaktion.

Führen Sie diese Beispielabfrage aus, um nicht abgeschlossene oder aktive Transaktionen und deren Eigenschaften zu finden.

• 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 die Daten, die vom text Feld von sys.dm_exec_sql_text zurückgegeben werden, NULL sind, ist die Anforderung nicht aktiv, verfügt aber über eine ausstehende Transaktion. 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 Teil der Lösung sein. Allerdings muss die Ursache behandelt werden, aufgrund derer das Trunkieren 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 Managed Instance.

Der Sicherungsspeicher wird nicht von Ihrem SQL-verwalteten Instanzspeicherplatz abgezogen. Der Sicherungsspeicher ist unabhängig vom Instanzspeicherplatz und ist nicht begrenzt in der Größe.

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 von den Bedingungen ab, unter denen das Protokoll gefüllt wurde.

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

• Das Transaktionsprotokoll wird nicht verkürzt und ist gewachsen, sodass es den gesamten verfügbaren Platz ausfüllt.
  • 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 die Synchronisierung von Verfügbarkeitsgruppen kann die Trunkierung des Transaktionsprotokolls verhindern, siehe Verhinderte Trunkierung des Transaktionsprotokolls.
• Die reservierte Speicherkapazität der verwalteten SQL-Instanz ist erschöpft, und das Transaktionsprotokoll kann nicht erweitert werden.
  • Fügen Sie Speicherplatz bis zum Ressourcenlimit hinzu. Siehe Dateiverwaltung zur Freigabe von Speicherplatz.
• Die Größe des Transaktionsprotokolls ist auf einen festen Maximalwert festgelegt, oder die automatische Vergrößerung ist deaktiviert und kann daher nicht vergrößert werden.
  • Siehe MAXSIZE- und FILEGROWTH-Eigenschaften in ALTER DATABASE-Datei und Dateigruppen.

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.
• Wenn Sie Masseneinfügungen mithilfe des bcp.exe Hilfsprogramms oder der SqlBulkCopy Klasse ausführen (verfügbar in beiden Microsoft.Data.SqlClient und System.Data.SqlClient), versuchen Sie, die Optionen -b batchsize oder BatchSize zu verwenden, um die Anzahl der Zeilen zu begrenzen, die in jeder Transaktion auf den Server kopiert werden. Weitere Informationen finden Sie unter bcp Utility.
• Wenn Sie einen Index mit der ALTER INDEX-Anweisung neu aufbauen, verwenden Sie die Optionen SORT_IN_TEMPDB = ON, ONLINE = ON und RESUMABLE=ON. Bei fortsetzbaren Indizes erfolgt das Abschneiden von Protokollen häufiger. Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Nächste Schritte

• Verstehen und Beheben von Blockierungsproblemen
• Beheben von Konnektivitätsproblemen und anderen Fehlern mit Azure SQL-Datenbank und Azure SQL Managed Instance
• Das Beheben vorübergehender Verbindungsfehler in Azure SQL-Datenbank und SQL Managed Instance