Ein Transaktionsprotokoll wächst unerwartet oder wird voll in SQL Server

Zusammenfassung

Vergrößerung des Transaktionsprotokolls kann eines der folgenden Gründe Szenarien auftreten.


Hinweis In SQL Server 2005 und höher überprüfen Sie die Spalten Log_reuse_wait und Log_reuse_wait_desc sys.databases Katalog anzeigen, warum der Transaktionsprotokoll-Speicher nicht wiederverwendet wird und warum das Transaktionsprotokoll abgeschnitten werden kann.






Nicht festgeschriebene Transaktionen
Explizite Transaktionen werden nicht übergeben, wenn Sie keinen expliziten COMMIT oder ROLLBACK-Befehl ausgeben. Dies tritt am häufigsten bei eine Anwendung ein ABBRUCH oder Transact SQL KILL-Befehl ohne einen entsprechenden Rollbackbefehl. Abbruch der Transaktion auftritt, aber ist kein Rollback. Daher kann nicht SQL Server jede Transaktion abgeschnitten, die danach tritt auf, weil die abgebrochene Transaktion noch geöffnet ist. Die DBCC OPENTRAN Transact-SQL-Referenz können Sie prüfen, ob eine aktive Transaktion in einer Datenbank zu einem bestimmten Zeitpunkt. Weitere Informationen zu diesem Szenario finden die folgenden zu Artikeln der Microsoft Knowledge Base:

295108 unvollständige Transaktion kann zahlreiche sperren und Fall blockieren enthalten.

171224 verstehen Transact SQL KILL-Befehl

Außerdem finden Sie im Thema "DBCC OPENTRAN" in der SQL Server-Onlinedokumentation.

Szenarien, die Transaktionen führen:
  • Ein Anwendungsdesign, die davon ausgeht, dass alle Fehler ein Rollback auslösen.
  • Ein Anwendungsdesign, das nicht vollständig SQL Server Verhalten berücksichtigt, wenn sie einen zu benannten Transaktionen oder speziell geschachtelten benannten Transaktionen Rollback. Wenn Sie versuchen, eine innere benannten Transaktion zurücksetzen, erhalten Sie folgende Fehlermeldung:
    Server: Meldung 6401, Ebene 16, Status 1, Zeile 13 kann ein Rollback InnerTran. Keine Transaktion oder Sicherungspunkt dieses Namens wurde gefunden.
    Nachdem SQL Server diese Fehlermeldung generiert, weiterhin die nächste Anweisung. Dies ist beabsichtigt. Weitere Informationen finden Sie unter dem Thema "Geschachtelte Transaktionen" oder "In SQL Server" in der SQL Server-Onlinedokumentation.

    Folgendes wird empfohlen, beim Entwerfen der Anwendung:
    • Stift nur eine Transaktionseinheit (erwägen, dass ein anderer Prozess Ihre aufrufen kann).
    • Überprüfen Sie @@TRANCOUNT, bevor ein COMMIT, ROLLBACK, eine RÜCKGABE oder ähnlichen Befehl oder Anweisung ausgeben.
    • Schreiben Sie den Code mit der Annahme, dass eine andere @@TRANCOUNT möglicherweise "verschachtelt" und möchten für äußere @@TRANCOUNT zurückgesetzt, wenn ein Fehler auftritt.
    • Sicherungspunkt überprüfen und Markierungsoptionen für Transaktionen. (Diese heben keine Sperren!)
    • Führen Sie umfassende Tests.
  • Eine Anwendung, die Benutzerinteraktionen in Transaktionen. Dadurch die Transaktion zu lange geöffnet und dieser Ursachen blockieren und Transaktion Wachstum anmelden, da die offene Buchung kann nicht abgeschnitten werden und neue Transaktionen werden nach offene Transaktion im Protokoll hinzugefügt.
  • Eine Anwendung, die nicht überprüft, um sicherzustellen, dass keine offenen Transaktionen @@TRANCOUNT.
  • Netzwerkfehler oder sonstige Fehler, die Verbindung der Clientanwendung zu SQL Server ohne es zu schließen.
  • Verbindungs-pooling. Nachdem Arbeitsthreads erstellt wurden, verwendet SQL Server sie, wenn sie keine Verbindung bedienen. Wenn benutzerverbindung eine Transaktion startet und trennt die Verbindung vor dem Commit oder Rollback der Transaktion und eine Verbindung nach, die im gleichen Thread verwendet, bleibt die vorherige Transaktion öffnen. Dies führt zu Sperren der vorherigen Transaktion geöffnet und verhindert das Abschneiden von Transaktionen im Protokoll. Dies führt zu großen Protokolldateigrößen. Weitere Informationen, klicken Sie auf die folgenden Artikelnummer der Microsoft Knowledge Base:

    164221 zum Verbindungspooling in einer ODBC-Anwendung aktivieren

Wenn Microsoft SQL Server 2005 und höheren Versionen, SQL Server 2000 und SQL Server 7.0 die Option automatische Vergrößerung festgelegt ist, können Transaktionsprotokolldateien automatisch auf die maximale Größe der Protokolldatei von 2 Terabyte (TB) pro Protokolldatei erweitern.

Die Größe der Transaktionsprotokolldatei stabilisiert normalerweise, wenn darin die maximale Anzahl von Transaktionen, die zwischen Transaction Log abgeschnittenen Daten von auftreten können, die Prüfpunkte oder Transaktionsprotokollen ausgelöst werden.

In einigen Fällen kann das Transaktionsprotokoll sehr groß und Speicherplatz oder voll. In der Regel sinngemäß die folgende Fehlermeldung eine Transaktionsprotokolldatei Speicherplatz verwendet und kann nicht mehr erweitert:
Fehler 9002, Schweregrad: 17, Status: 2
Die Protokolldatei für Datenbank ' %. * ls ist voll.
Wenn Sie SQL Server 2005 verwenden, erhalten Sie eine Fehlermeldung, die der folgenden ähnelt:
Fehler 9002, Schweregrad: 17, Status: 2
Das Transaktionsprotokoll für die Datenbank ' %. * ls ist. Um herauszufinden, warum Speicherplatz im Protokoll verwendet werden kann, finden Sie die Log_reuse_wait_desc-Spalte-Datenbank
Neben dieser Fehlermeldung kann SQL Server aufgrund mangelnder Speicherplatz für die Vergrößerung des Transaktionsprotokolls Datenbanken als fehlerverdächtig markiert. Weitere Informationen zum Beheben des Problems finden Sie unter dem Thema "Nicht genügend Speicherplatz" in der SQL Server-Onlinedokumentation.

Darüber hinaus kann Vergrößerung des Transaktionsprotokolls aus folgenden Gründen oder in einem der folgenden Szenarien auftreten:
  • Eine große Transaktionsprotokolldatei.
  • Transaktionen können fehlschlagen und möglicherweise ein Rollback gestartet.
  • Transaktionen können viel Zeit in Anspruch nehmen.
  • Es können Leistungsprobleme auftreten.
  • Blockieren auftreten.
  • Die Datenbank ist eine AlwaysOn Availability Group beteiligt.


Sehr umfangreiche Transaktionen
Datensätze in den Transaktionsprotokolldateien werden auf Basis von Transaktion abgeschnitten. Wenn Transaktionsbereich groß ist, dass Transaktionen und Transaktionen gestartet, nachdem es werden nicht aus dem Transaktionsprotokoll entfernt, wenn es abgeschlossen ist. Dies kann sehr großen Protokolldateien führen. Wenn die Buchung ausreicht, die Protokolldatei möglicherweise Speicherplatz belegen und "Transaktionsprotokoll ist voll" Typ der Fehlermeldung Fehler 9002. Weitere Informationen, wenn Sie diese Fehlermeldung erhalten finden Sie im Abschnitt "Weitere Informationen" in diesem Artikel. Außerdem erfordert es viel Zeit und Aufwand SQL Server Rollback für umfangreiche Transaktionen.

Operationen: DBCC DBREINDEX und CREATE INDEX
Aufgrund der Wiederherstellungsmodell in SQL Server 2000 kann bei Verwendung des vollständigen Wiederherstellungsmodus und Ausführen von DBCC DBREINDEX das Transaktionsprotokoll erweitern erheblich mehr im Vergleich zu SQL Server 7.0 in eine entsprechende Wiederherstellungsmodus mit SELECT INTO oder MASSENKOPIEREN und "Trunc. Log on Chkpt."aus.

Zwar die Größe des Transaktionsprotokolls nach der Operation DBREINDEX Problem bietet dieser Ansatz besser Protokoll Wiederherstellung.


Beim Wiederherstellen von Sicherungskopien des Transaktionsprotokolls
Dies wird im folgenden Artikel der Microsoft Knowledge Base beschrieben:
232196 Wiederherstellung zu Speicherplatz verwendet wird


Wenn Sie SQL Server 2000 massenprotokollierten Modus und geben Sie eine BULK COPY oder SELECT INTO-Anweisung, wird jeder geänderte Block markiert und dann gesichert, wenn Sie das Transaktionsprotokoll sichern. Obwohl dadurch wieder Transaktionslogs und Wiederherstellung nach einem Ausfall auch nach Massenvorgängen durchführen, addiert die Größe der Transaktionsprotokolle. SQL Server 7.0 umfasst dieses Feature nicht. SQL Server 7.0 protokolliert nur, welche Blöcke geändert werden, aber keine eigentlichen Blöcke aufgezeichnet. Die Protokollierung wird daher wesentlich mehr Speicherplatz in SQL Server 2000 als in SQL Server 7.0 in Bulk-Modus, aber weniger als im vollständigen Modus wird verwendet.

Clientanwendungen verarbeiten nicht alle Ergebnisse
Wenn SQL Server eine Abfrage erteilen die Ergebnisse wird nicht sofort verarbeitet, möglicherweise sperren und reduzieren die Parallelität auf dem Server.

Angenommen Sie, dass Sie eine Abfrage aus, die Zeilen aus zwei Seiten zum Auffüllen der Ergebnismenge benötigt. SQL Server analysiert, kompiliert und führt die Abfrage aus. Dies bedeutet, dass gemeinsame Sperren auf zwei Seiten, die Zeilen, die Sie hinzugefügt werden enthalten für Ihre Abfrage benötigen. Nehmen wir außerdem an, dass nicht alle Zeilen in einer SQL Server-TDS-Paket passen (die Methode, durch die der Server mit dem Client kommuniziert). TDS-Pakete werden gefüllt und an den Client gesendet. Wenn alle Zeilen aus der ersten Seite in das TDS-Paket passen, SQL Server die gemeinsame Sperre auf der Seite; eine gemeinsame Sperre auf der zweiten Seite verlässt SQL Server wartet dann auf dem Client mehr Daten (Sie hierzu beispielsweise mithilfe von DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults oder FetchLast/FetchFirst).

Dies bedeutet, dass die gemeinsam verwendete Sperre gehalten wird, bis der Client die restlichen Daten anfordert. Andere Prozesse, die Daten aus dieser zweiten Seite anfordern blockiert.


Timeout abfragt, bevor ein Transaktionsprotokoll die Erweiterung beendet und false Fehlermeldungen 'Werden'
In diesem Fall zwar genügend Speicherplatz vorhanden ist Fehlermeldung Sie weiterhin eine "Speicherplatz".

Dies ist für SQL Server 7.0 und SQL Server 2000.

Eine Abfrage kann das Transaktionsprotokoll automatisch erweitert, wenn das Transaktionsprotokoll nahezu voll ist. Zusätzliche Zeit dauern und eine Abfrage kann oder darf deshalb das Timeoutintervall. SQL Server 7.0 gibt in diesem Fall Fehler 9002 zurück. Dieses Problem gilt nicht für SQL Server 2000.

In SQL Server 2000 haben Sie die Option Automatische Verkleinerung auf eine Datenbank gibt sehr Zeit während, die ein Transaktionsprotokoll automatisch erweitern möchte. Jedoch kann nicht erweitert die Funktion Automatische Verkleinerung gleichzeitig ausgeführt wird. Dies kann auch false Instanzen von Fehler 9002 führen.

Normalerweise tritt schnell die automatische Erweiterung der Transaktionsprotokolldateien. In den folgenden Situationen kann es länger dauern:
  • Schrittweite sind zu klein.
  • Der Server ist aus verschiedenen Gründen langsam.
  • Festplattenlaufwerke sind nicht schnell genug.


Replizierte Transaktionen
Das Transaktionslog der Publikationseigentümer -Datenbank erweitern, wenn Sie Replikation verwenden. Transaktionen, die Einfluss auf die Objekte, die repliziert werden Zeichnen als "Für die Replikation". Diese Transaktionen, wie nicht festgeschriebene Transaktionen werden erst gelöscht, nachdem Checkpoint oder Sie sichern Sie das Transaktionsprotokoll Task "Protokollleser" die Transaktionen an die Verteilungsdatenbank kopiert und die Kennzeichnung beseitigt. Wenn ein Problem mit der Protokollleser diese Transaktionen auf dem Verleger lesen verhindert, kann die Größe des Transaktionsprotokolls weiter die Anzahl der nicht replizierten Transaktionen zunimmt. Die DBCC OPENTRAN Transact-SQL-Referenz können Sie um die älteste nicht replizierte Transaktion zu identifizieren.

Weitere Informationen zur Problembehandlung bei nicht replizierten Transaktionen finden Sie unter den Themen "Sp_replcounters" und "Sp_repldone" in der SQL Server-Onlinedokumentation.

Klicken Sie für weitere Informationen auf die folgenden Artikelnummern, um die betreffenden Artikel in der Microsoft Knowledge Base anzuzeigen:

306769 beheben: Transaktionsprotokoll der publizierten Snapshotdatenbank kann nicht abgeschnitten werden

240039 beheben: DBCC OPENTRAN Replikationsinformationen nicht gemeldet

198514 beheben: Wiederherstellung auf neuen Server bewirkt, dass Transaktionen im Protokoll bleibt



AlwaysOn Transaktionsprotokoll-Datensätze in eine sekundäre Datenbank anwenden ' AVAILABILITY_REPLICA'

In SQL Server 2012 mit AlwaysOn Availability aktiviert sehen Sie folgende Meldung im Fehlerprotokoll von SQL:

Fehler 9002, Schweregrad: 17, Status: 9.
Die Protokolldatei für Datenbank ' %. * ls ist auf 'AVAILABILITY_REPLICA'

AVAILABILITY_REPLICA Log_reuse_wait gibt an, dass eine sekundäre Kopie AlwaysOn Availability Gruppen Transaktionsprotokolleinträge dieser Datenbank in einer entsprechenden sekundären Datenbank anwenden.

Es gibt zwei Szenarien, die zu einer verfügbarkeitsdatenbank und das AVAILABILITY_REPLICA Wachstum anmelden ' Log_reuse_wait:

Szenario 1: Wartezeit liefern angemeldet ändert sekundäre

Bei eine Transaktion auf dem Primärserver ausgeführt protokollierte Blöcke übermittelt und die Protokolldatei auf der sekundären abgesichert. Verzögerung verhindert die protokollierte Änderung in der Datenbank primäres Replikat abgeschnitten.

Szenario 2: Wartezeit wiederholen

Wenn die sekundäre Datenbank-Protokolldatei abgesichert gilt dedizierten Redo-Thread Protokolldatensätze.

Wenn der Wiederherstellungsvorgang nicht generierten Transaktionsprotokoll mithalten kann, kann es führen Wachstum anmelden. Die primäre können das Transaktionsprotokoll abschneiden, wenn der Wiederherstellungsvorgang sekundären Replikat hinter übernehmen diese Änderungen in einer entsprechenden sekundären Datenbank. Gibt es mehrere sekundäre, identifiziert die sekundäre Datenbank Protokoll abschneiden verzögern Truncation_lsn Spalte sys.dm_hadr_database_replica_states dynamische Ansicht über mehrere sekundäre vergleichen.

AlwaysOn-Dashboard verwenden und sys.dm_hadr_database_replica_states dynamische Verwaltungsansichten Protokoll überwachen Sendewarteschlange und Warteschlange wiederholen. Einige Felder sind:

FeldBeschreibung
log_send_queue_sizeBetrag der Protokolldatensätze, die sekundäre Replikat nicht angekommen
log_send_rateRate der Benutzer Datensätze sekundäre Datenbanken gesendet werden
redo_queue_sizeDer Betrag der Protokolldatensätze in den Protokolldateien der sekundären Kopie nicht noch, in Kilobyte (KB) erneuert wurde
redo_rateMit die Datensätze in einer bestimmten sekundären Datenbank in Kilobyte (KB) wiederholt wird werden Rate / Sekunde
last_redone_lsnTatsächliche Protokollsequenznummer des letzten Protokolldatensatzes auf die sekundäre Datenbank wiederhergestellt wurde. Last_redone_lsn ist immer kleiner als last_hardened_lsn
last_received_lsnProtokollieren Sie Block-ID identifizieren die alle Protokolldateien Blöcke sekundären Replikat eingegangen, die sekundäre Datenbank hostet Zeigt eine Protokoll Block-ID mit Nullen aufgefüllt. Es ist keine tatsächliche Sequenznummer.

Hinweis Weitere Informationen über die sys.dm_hadr_database_replica_states-Ansicht finden Sie auf der folgenden TechNet-Website:

http://technet.microsoft.com/en-us/library/ff877972.aspx



Erweiterte Informationen

Das Transaktionsprotokoll einer Datenbank wird als Satz virtueller Protokolldateien (VLFs) verwaltet. SQL Server bestimmt VLF Dateigrößen intern basierend auf der Gesamtgröße der Protokolldatei und die Schrittweite verwendet wird, wird. Ein Protokoll wird immer um ganze VLFs vergrößert und kann nur zu einer VLF komprimiert. Eine VLF kann in einem von drei Zuständen: aktiv, BEHEBBARE und wieder VERWENDBARE.
  • Aktiv: der aktive Teil des Protokolls beginnt bei der minimalen Protokollsequenznummer (LSN), die eine aktive (nicht übergebene) Transaktion darstellt. Der aktive Teil des Protokolls endet bei der zuletzt geschriebenen LSN. Alle VLFs, die einen Teil des aktiven Protokolls enthalten aktive VLFs. gelten (Speicherplatz im physischen Protokoll ist kein Bestandteil einer VLF.)
  • BEHEBBARER: der Teil des Protokolls vor die älteste aktive Transaktion nur eine Folge von Sicherungskopien für Recovery beibehalten werden muss.
  • Wieder VERWENDBARE: ob Transaktionsprotokollen nicht pflegen, wenn bereits das Protokoll gesichert, verwendet SQL Server die VLFs vor der ältesten aktiven Transaktion.
Wenn SQL Server das Ende der physischen Protokolldatei erreicht, wird dieser Speicherplatz in der physischen Datei durch einen Vorgang wieder KREISEN an Anfang der Dateien wiederverwenden. SQL Server verwendet, den Platz in der Protokolldatei für Recovery oder Sicherung entfällt. Wenn eine Folge von Sicherungskopien beibehalten wird, darf nicht Teil des Protokolls vor der kleinsten LSN erst dann überschrieben werden sichern oder abzuschneiden dieser Datensätze. Nach Abschluss die Sicherung können SQL Server wieder an den Anfang der Datei Kreisen. Nachdem SQL Server Kreise zurück, um Datensätze bereits in die Protokolldatei schreiben, ist wieder verwendbaren Teil des Protokolls dann zwischen dem Ende des logischen Protokolls und der aktive Teil des Protokolls.

Weitere Informationen finden Sie im Thema "Physische Architektur des Transaktionsprotokolls" in der SQL Server-Onlinedokumentation. Darüber hinaus können Sie ein Diagramm und eine genauere Beschreibung auf Seite 190 von "Inside SQL Server 7.0" (Soukup, Ron. sehen Inside Microsoft SQL Server 7.0, Microsoft Press, 1999), und auch auf den Seiten 182 bis 186 von "Inside SQL Server 2000" (Delaney, Kalen. In Microsoft SQL Server 2000, Microsoft Press, 2000).
SQL Server 2000 und SQL Server 7.0-Datenbanken können automatisch vergrößert oder verkleinert. Diese Optionen können Sie komprimieren oder Erweitern Sie das Transaktionsprotokoll.

Weitere Informationen zu dieser Optionen können Server beeinflussen, finden Sie im folgenden Artikel der Microsoft Knowledge Base:

315512 Faktoren für Vergrößerung und Verkleinerung in SQL Server

Verkürzung der Transaktionsprotokolldatei unterscheidet sich von der Komprimierung der Transaktionsprotokolldatei. Wenn SQL Server eine Transaktionsprotokoll-Datei abschneidet, bedeutet dies, dass der Inhalt dieser Datei (zum Beispiel die übergebenen Transaktionen) gelöscht werden. Wenn Sie die Größe der Datei aus Sicht der Disk Space (z. B. in Windows-Explorer oder den Befehl ) anzeigen, bleibt die Größe unverändert. Der Speicherplatz in der LDF-Datei kann jedoch jetzt neue Transaktionen wiederverwendet werden. Nur wenn SQL Server die Größe der Transaktionsprotokolldatei verkleinert tatsächlich ändert die physische Größe der Protokolldatei angezeigt.

Weitere Informationen dazu, wie Sie Transaktionsprotokolle verkleinern klicken Sie auf die folgenden Artikelnummern klicken, um die Artikel der Microsoft Knowledge Base:

256650 wie Verkleinern des SQL Server 7.0-Transaktionsprotokolls

272318 Verkleinern des Transaktionsprotokolls in SQL Server 2000 mit DBCC SHRINKFILE

Weitere Informationen zur Verwendung des Transaktionsprotokolls in SQL Server 6.5 finden Sie im folgenden Artikel der Microsoft Knowledge Base:

110139 bewirkt, dass der SQL-Transaktionsprotokolls

Wie zu Abfragen, die viel Speicherplatz in SQL Server 2005 und höher verwenden

In SQL Server 2005 und höheren Versionen können Sie sys.dm_tran_database_transactions dynamic Management View (DMV) zu Abfragen, die sehr viel Speicherplatz belegen. Die folgenden Spalten in der sys.dm_tran_database_transactions DMV hilfreich:
  • database_transaction_log_bytes_used
  • database_transaction_log_bytes_used_system
  • database_transaction_log_bytes_reserved
  • database_transaction_log_bytes_reserved_system
  • database_transaction_log_record_count
Sie können Abfragen die Sql_handle Spalte dm_exec_requests DMV Text der aktuellen Anweisung abrufen, die viel Speicherplatz verbraucht. Hierzu verknüpfen sys.dm_tran_database_transactions DMV und sys.dm_tran_session_transactions DMV Transaction_id Spalte und anschließend in der Spalte Nummer eine zusätzliche Verknüpfung mit dm_exec_requests hinzufügen.


Weitere Informationen zu sys.dm_tran_database_transactions DMV finden Sie auf der Microsoft Developer Network (MSDN)-Website sys.dm_tran_database_transactions (Transact-SQL) .


Weitere Informationen zu sys.dm_tran_session_transactions DMV finden Sie auf der MSDN-Website sys.dm_tran_session_transactions (Transact-SQL) .


Weitere Informationen zu dm_exec_requests DMV finden Sie auf der MSDN-Website dm_exec_requests (Transact-SQL) .
Eigenschaften

Artikelnummer: 317375 – Letzte Überarbeitung: 16.01.2017 – Revision: 2

Feedback