Das Transaktionsprotokoll [SQL Server]

Anwendungsbereich: JaSQL Server (alle unterstützten Versionen)

Jede SQL Server-Datenbank verfügt über ein Transaktionsprotokoll, in dem alle Transaktionen sowie die Datenbankänderungen erfasst werden, die von den einzelnen Transaktionen vorgenommen werden.

Das Transaktionsprotokoll ist eine wichtige Komponente der Datenbank. Wenn ein Systemfehler auftritt, benötigen Sie dieses Protokoll, um Ihre Datenbank wieder in einen konsistenten Zustand zu versetzen.

Informationen zur Transaktionsprotokollarchitektur und den internen Gegebenheiten finden Sie im Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server.

Warnung

Dieses Protokoll sollten Sie nicht löschen oder verschieben, wenn Sie sich über die Auswirkungen dieses Vorgangs nicht vollständig im Klaren sind.

Tipp

Einige bekannte gute Ausgangspunkte für das Anwenden von Transaktionsprotokollen während der Datenbankwiederherstellung werden durch Prüfpunkte vorgegeben. Weitere Informationen finden Sie unter Datenbankprüfpunkte (SQL Server).

Vom Transaktionsprotokoll unterstützte Operationen

Das Transaktionsprotokoll unterstützt die folgenden Vorgänge:

  • Wiederherstellen einzelner Transaktionen.
  • Wiederherstellen aller unvollständigen Transaktionen, wenn SQL Server gestartet wird.
  • Ausführen eines Rollforwards für eine wiederhergestellte Datenbank, Datei, Dateigruppe oder Seite bis zu dem Punkt, an dem der Fehler aufgetreten ist.
  • Unterstützen der Transaktionsreplikation.
  • Lösungen zur Unterstützung von Hochverfügbarkeit und Notfallwiederherstellung: Always On-Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand.

Wiederherstellen einzelner Transaktionen

Wenn eine Anwendung eine ROLLBACK-Anweisung ausgibt oder wenn Datenbank-Engine einen Fehler erkennt, z. B. die unterbrochene Verbindung mit einem Client, werden die Protokolldatensätze verwendet, um für die Änderungen, die von unvollständigen Transaktionen vorgenommen wurden, einen Rollback auszuführen.

Wiederherstellen aller unvollständigen Transaktionen, wenn SQL Server gestartet wird

Wenn ein Server ausfällt, bleiben die Datenbanken möglicherweise in einem Status, in dem einige Änderungen nicht vom Puffercache in die Datendateien geschrieben wurden, einige Änderungen von unvollständigen Transaktionen jedoch bereits in den Datendateien vorgenommen wurden. Beim Starten einer Instanz von SQL Server wird eine Wiederherstellung aller Datenbanken ausgeführt. Für jede Änderung, die im Protokoll aufgezeichnet wurde und die möglicherweise nicht in die Datendateien geschrieben wurde, wird ein Rollforward ausgeführt. Für jede unvollständige Transaktion, die im Transaktionsprotokoll erkannt wird, wird anschließend ein Rollback ausgeführt, um sicherzustellen, dass die Integrität der Datenbank aufrechterhalten wird. Weitere Informationen finden Sie unter Übersicht über Wiederherstellungsvorgänge (SQL Server).

Ausführen eines Rollforwards für eine wiederhergestellte Datenbank, Datei, Dateigruppe oder Seite bis zu dem Punkt, an dem der Fehler aufgetreten ist

Nach einem Hardwareverlust oder Datenträgerfehler, der sich auf die Datendateien auswirkt, können Sie die Datenbank so wiederherstellen, wie sie zum Zeitpunkt des Ausfalls vorlag. Sie stellen zuerst die letzte vollständige und die letzte differenzielle Datenbanksicherung und anschließend die nachfolgende Folge von Transaktionsprotokollsicherungen bis zu dem Punkt wieder her, an dem der Fehler aufgetreten ist.

Beim Wiederherstellen der einzelnen Protokollsicherungen übernimmt Datenbank-Engine erneut sämtliche im Protokoll aufgezeichneten Änderungen, um für alle Transaktionen einen Rollforward auszuführen. Sobald die letzte Protokollsicherung wiederhergestellt ist, verwendet Datenbank-Engine die Protokollinformationen, um für sämtliche Transaktionen einen Rollback auszuführen, die zu diesem Zeitpunkt noch nicht abgeschlossen waren. Weitere Informationen finden Sie unter Übersicht über Wiederherstellungsvorgänge (SQL Server).

Unterstützen der Transaktionsreplikation

Der Protokolllese-Agent überwacht das Transaktionsprotokoll jeder für die Transaktionsreplikation konfigurierten Datenbank und kopiert die für die Replikation markierten Transaktionen aus dem Transaktionsprotokoll in die Verteilungsdatenbank. Weitere Informationen finden Sie unter Funktionsweise der Transaktionsreplikation.

Unterstützen von Hochverfügbarkeits- und Notfallwiederherstellungslösungen

Standbyserverlösungen, Always On-Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand hängen in großem Umfang vom Transaktionsprotokoll ab.

In einem Szenario mit Always On-Verfügbarkeitsgruppen wird jedes Update einer Datenbank (des primären Replikats) sofort in separaten vollständigen Kopien der Datenbank (die sekundären Replikate) reproduziert. Das primäre Replikat sendet jeden Protokolldatensatz sofort an die sekundären Replikate, in denen dieser eingehende Protokolldatensatz auf Verfügbarkeitsgruppen-Datenbanken angewendet wird, wobei der Protokolldatensatz kontinuierlich weitergegeben wird. Weitere Informationen finden Sie unter Always On-Failoverclusterinstanzen.

In einem Protokollversandszenario sendet der primäre Server das aktive Transaktionsprotokoll der primären Datenbank an ein oder mehrere Ziele. Jeder sekundäre Server stellt das Protokoll in seiner lokalen sekundären Datenbank wieder her. Weitere Informationen finden Sie unter Informationen zum Protokollversand.

In einem Datenbankspiegelungsszenario wird jedes Update einer Datenbank (der Prinzipaldatenbank) sofort in einer separaten vollständigen Kopie der Datenbank (der Spiegeldatenbank) reproduziert. Die Prinzipalserverinstanz sendet jeden Protokolldatensatz sofort an die Spiegelserverinstanz, die die eingehenden Protokolldatensätze auf die Spiegeldatenbank anwendet, um kontinuierlich ein Rollforward dafür auszuführen. Weitere Informationen finden Sie unter Datenbankspiegelung.

Merkmale von Transaktionsprotokollen

Merkmale der SQL Server-Datenbank-Engine-Transaktionsprotokolle:

  • Das Transaktionsprotokoll wird als eine separate oder mehrere Dateien in der Datenbank implementiert. Der Protokollcache wird getrennt vom Puffercache für Datenseiten verwaltet, woraus sich ein einfacher, schneller und zuverlässiger Code innerhalb des SQL Server-Datenbank-Engines ergibt. Weitere Informationen finden Sie unter Physische Architektur des Transaktionsprotokolls.

  • Das Format der Protokolldatensätze und -seiten muss nicht dem Format von Datenseiten entsprechen.

  • Das Transaktionsprotokoll kann in Form mehrerer Dateien implementiert werden. Für die Dateien kann eine automatische Erweiterung durch Festlegen des FILEGROWTH-Werts für das Protokoll definiert werden. Auf diese Weise nimmt die Wahrscheinlichkeit ab, dass im Transaktionsprotokoll kein Speicherplatz mehr verfügbar ist. Zudem wird der Verwaltungsaufwand verringert. Weitere Informationen finden Sie unter ALTER DATABASE-Optionen FILE und FILEGROUP (Transact-SQL).

  • Der Mechanismus zum erneuten Verwenden des freien Speicherplatzes in den Protokolldateien ist schnell und wirkt sich nur minimal auf den Transaktionsdurchsatz aus.

Informationen zur Transaktionsprotokollarchitektur und den internen Gegebenheiten finden Sie im Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server.

Kürzung des Transaktionsprotokolls

Durch das Kürzen des Protokolls wird in der Protokolldatei Speicherplatz freigegeben, der vom Transaktionsprotokoll erneut verwendet werden kann. Sie müssen regelmäßig das Transaktionsprotokoll abschneiden, damit es nicht den vorgesehenen Speicherplatz belegt. Verschiedene Faktoren können die Protokollkürzung verzögern, daher ist die Überwachung der Protokollgröße wichtig. Einige Vorgänge lassen sich minimal protokollieren, um deren Auswirkung auf die Größe des Transaktionsprotokolls zu reduzieren.

Durch die Protokollkürzung werden inaktive virtuelle Protokolldateien (Virtual Log Files, VLFs) aus dem logischen Transaktionsprotokoll einer SQL Server-Datenbank gelöscht, wodurch Speicherplatz im logischen Protokoll zur Wiederverwendung durch das physische Transaktionsprotokoll freigegeben wird. Wird ein Transaktionsprotokoll nicht gekürzt, füllt sich dadurch der gesamte Speicherplatz des Datenträgers auf, der den zugehörigen physischen Protokolldateien zugeordnet ist.

Um zu vermeiden, dass nur noch wenig Speicherplatz vorhanden ist, erfolgt die Kürzung automatisch nach den folgenden Ereignissen, sofern die Protokollkürzung nicht aus bestimmten Gründen verzögert wird:

  • Unter dem einfachen Wiederherstellungsmodell, nach einem Prüfpunkt.
  • Unter dem vollständigen oder massenprotokollierten Wiederherstellungsmodell, wenn ein Prüfpunkt seit der vorherigen Sicherung ausgelöst wurde, erfolgt die Kürzung nach einer Protokollsicherung (sofern es sich nicht um eine Kopiesicherung handelt).

Weitere Informationen finden Sie unter Faktoren, die die Protokollkürzung verzögern können weiter unten in diesem Thema.

Hinweis

Die Protokollkürzung verringert nicht die Größe einer physischen Protokolldatei. Sie müssen zum Reduzieren der physischen Größe einer physischen Protokolldatei die Protokolldatei verkleinern. Informationen zum Verkleinern der Größe der physischen Protokolldatei finden Sie unter Verwalten der Größe der Transaktionsprotokolldatei.
Berücksichtigen Sie jedoch die Faktoren, die die Protokollkürzung verzögern können. Wenn der Speicherplatz nach einer Protokollverkleinerung wieder benötigt wird, vergrößert sich das Transaktionsprotokoll wieder und führt bei der Protokollvergrößerung infolgedessen zu einem Leistungsoverhead.

Faktoren, die die Protokollkürzung verzögern können

Bleiben Protokolldatensätze lange aktiv, verzögert sich die Transaktionsprotokollkürzung. Dabei kann sich das Transaktionsprotokoll auffüllen, wie bereits oben erwähnt wurde.

Wichtig

Informationen zum Umgang mit einem voll aufgefüllten Transaktionsprotokoll finden Sie unter Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Die Protokollkürzung kann tatsächlich aus verschiedenen Gründen verzögert werden. Sie können ermitteln, wodurch die Protokollkürzung verhindert wird, indem Sie die Spalten log_reuse_wait und log_reuse_wait_desc der Katalogsicht sys.databases abfragen. In der folgenden Tabelle werden die Werte dieser Spalten beschrieben.

log_reuse_wait value log_reuse_wait_desc value BESCHREIBUNG
0 NOTHING Derzeit ist mindestens eine wiederverwendbare virtuelle Protokolldatei (Virtual Log File, VLF) vorhanden.
1 CHECKPOINT Seit der letzten Protokollkürzung ist kein Prüfpunkt aufgetreten, oder der Kopf des Protokolls wurde noch nicht über eine virtuelle Protokolldatei (Virtual Log File, VLF) hinaus verschoben. (Alle Wiederherstellungsmodelle)

Dies ist ein häufiger Grund für das verzögerte Kürzen von Protokollen. Weitere Informationen finden Sie unter Datenbankprüfpunkte (SQL Server).
2 LOG_BACKUP Eine Protokollsicherung ist erforderlich, bevor das Transaktionsprotokoll gekürzt werden kann. (nur vollständiges bzw. massenprotokolliertes Wiederherstellungsmodell)

Bei Abschluss der nächsten Protokollsicherung wird möglicherweise ein Teil des Protokollspeicherplatzes zur Wiederverwendung freigegeben.
3 ACTIVE_BACKUP_OR_RESTORE Es findet gerade eine Datensicherung oder eine Wiederherstellung statt (alle Wiederherstellungsmodelle).

Verhindert eine Datensicherung die Protokollkürzung, kann das unmittelbare Problem u. U. durch Abbrechen des Sicherungsvorgangs behoben werden.
4 ACTIVE_TRANSACTION Eine Transaktion ist aktiv (alle Wiederherstellungsmodelle):

Möglicherweise ist beim Starten der Protokollsicherung eine Transaktion mit langer Ausführungszeit vorhanden. In diesem Fall ist zum Freigeben von Speicherplatz möglicherweise eine weitere Protokollsicherung erforderlich. Hinweis: Transaktionen mit langer Laufzeit verhindern die Protokollkürzung unter allen Wiederherstellungsmodellen, einschließlich des einfachen Wiederherstellungsmodells, unter dem im Allgemeinen das Transaktionsprotokoll an jedem automatischen Prüfpunkt gekürzt wird.

Eine Transaktion wird verzögert. Eine verzögerte Transaktion ist tatsächlich eine aktive Transaktion, deren Rollback aufgrund einer nicht verfügbaren Ressource blockiert ist. Weitere Informationen zu den Ursachen für verzögerte Transaktionen und zum Auflösen ihres verzögerten Zustands finden Sie unter Verzögerte Transaktionen (SQL Server).

Lang andauernde Transaktionen können auch das Transaktionsprotokoll von „tempdb“ füllen. „tempdb“ wird implizit von Benutzertransaktionen für interne Objekte wie z.B. Arbeitstabellen zum Sortieren, Arbeitsdateien für Hashverfahren, Cursorarbeitstabellen und Zeilenversionsverwaltung verwendet. Selbst wenn die Benutzertransaktion nur das Lesen von Daten umfasst (SELECT-Abfragen), werden möglicherweise interne Objekte erstellt und unter Benutzertransaktionen verwendet. Anschließend kann das tempdb-Transaktionsprotokoll gefüllt werden.
5 DATABASE_MIRRORING Die Datenbankspiegelung wurde angehalten, oder im Modus für hohe Leistung befindet sich die Spiegeldatenbank deutlich hinter der Prinzipaldatenbank. (nur vollständiges Wiederherstellungsmodell)

Weitere Informationen finden Sie unter Datenbankspiegelung (SQL Server).
6 REPLIKATION Während der Transaktionsreplikationen wurden für die Veröffentlichungen relevante Transaktionen noch immer nicht für die Verteilungsdatenbank bereitgestellt. (nur vollständiges Wiederherstellungsmodell)

Weitere Informationen zur Transaktionsreplikation finden Sie unter SQL Server Replication.
7 DATABASE_SNAPSHOT_CREATION Eine Datenbank-Momentaufnahme wird erstellt. (Alle Wiederherstellungsmodelle)

Dies ist ein häufiger, im Allgemeinen jedoch nur kurz andauernder Grund für ein verzögertes Kürzen eines Protokolls.
8 LOG_SCAN Ein Protokollscan wird ausgelöst. (Alle Wiederherstellungsmodelle)

Dies ist ein häufiger, im Allgemeinen jedoch nur kurz andauernder Grund für ein verzögertes Kürzen eines Protokolls.
9 AVAILABILITY_REPLICA Ein sekundäres Replikat einer Verfügbarkeitsgruppe wendet Transaktionsprotokoll-Datensätze dieser Datenbank auf eine zugehörige sekundäre Datenbank an. (vollständiges Wiederherstellungsmodell)

Weitere Informationen finden Sie unter Übersicht über Always On-Verfügbarkeitsgruppen (SQL Server).
10 - Nur interne Verwendung
11 - Nur interne Verwendung
12 - Nur interne Verwendung
13 OLDEST_PAGE Ist eine Datenbank zur Verwendung von indirekten Prüfpunkten konfiguriert, ist die älteste Seite in der Datenbank u.U. älter als die Protokollfolgenummer (Log Sequence Number, LSN). In diesem Fall kann die älteste Seite die Protokollkürzung verzögern. (Alle Wiederherstellungsmodelle)

Weitere Informationen zu indirekten Prüfpunkten finden Sie unter Database Checkpoints (SQL Server).
14 OTHER_TRANSIENT Dieser Wert wird derzeit nicht verwendet.
16 XTP_CHECKPOINT Es muss ein In-Memory OLTP-Prüfpunkt ausgeführt werden. Für speicheroptimierte Tabellen wird ein automatischer Prüfpunkt erstellt, wenn die Transaktionsprotokolldatei seit dem letzten Prüfpunkt größer als 1,5 GB geworden ist (bezieht sich sowohl auf datenträgerbasierte als auch auf speicheroptimierte Tabellen).
Weitere Informationen finden Sie unter Prüfpunktvorgang für speicheroptimierte Tabellen und [Protokollierungs- und Prüfpunktprozess für speicheroptimierte Tabellen] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Vorgänge, für die eine minimale Protokollierung verfügbar ist

Bei der minimalen Protokollierung werden nur die Informationen protokolliert, die zum Wiederherstellen der Transaktion ohne Unterstützung der Zeitpunktwiederherstellung erforderlich sind. In diesem Thema werden die Vorgänge aufgeführt, die unter dem massenprotokollierten Wiederherstellungsmodell minimal protokolliert werden (sowie unter dem einfachen Wiederherstellungsmodell, es sei denn, es wird eine Sicherung ausgeführt).

Hinweis

Die minimale Protokollierung wird für speicheroptimierte Tabellen nicht unterstützt.

Hinweis

Unter dem vollständigen Wiederherstellungsmodellwerden alle Massenvorgänge vollständig protokolliert. Sie können die Protokollierung für eine Reihe von Massenvorgängen jedoch verringern, indem Sie die Datenbank bei Massenvorgängen vorübergehend in das massenprotokollierte Wiederherstellungsmodell schalten. Die minimale Protokollierung ist effizienter als die vollständige Protokollierung und senkt die Wahrscheinlichkeit, dass ein umfangreicher Massenvorgang den verfügbaren Transaktionsprotokoll-Speicherplatz während einer Massentransaktion auffüllt. Wenn die Datenbank bei Aktivierung der minimalen Protokollierung jedoch beschädigt wird oder verloren geht, können Sie die Datenbank nicht bis zu dem Punkt wiederherstellen, an dem der Fehler aufgetreten ist.

Die folgenden Vorgänge, die unter dem vollständigen Wiederherstellungsmodell vollständig protokolliert werden, werden unter dem einfachen und massenprotokollierten Wiederherstellungsmodell minimal protokolliert:

Wenn die Transaktionsreplikation aktiviert ist, werden BULK INSERT-Vorgänge auch unter dem massenprotokollierten Wiederherstellungsmodell vollständig protokolliert.

Wenn die Transaktionsreplikation aktiviert ist, werden SELECT INTO-Vorgänge auch unter dem massenprotokollierten Wiederherstellungsmodell vollständig protokolliert.

  • Teilupdates von Datentypen für hohe Werte mithilfe der .WRITE-Klausel in der UPDATE-Anweisung beim Einfügen oder Anfügen neuer Daten. Beachten Sie, dass die minimale Protokollierung nicht verwendet wird, wenn vorhandene Werte aktualisiert werden. Weitere Informationen zu Datentypen für hohe Werte finden Sie unter Datentypen (Transact-SQL).

  • WRITETEXT -Anweisung und UPDATETEXT -Anweisung beim Einfügen oder Anfügen neuer Daten an die Datentypspalten text , ntext und image . Beachten Sie, dass die minimale Protokollierung nicht verwendet wird, wenn vorhandene Werte aktualisiert werden.

    Warnung

    Die WRITETEXT- und die UPDATETEXT-Anweisung sind veraltet , sollten also in neuen Anwendungen nicht mehr verwendet werden.

  • Wenn für die Datenbank das einfache oder massenprotokollierte Wiederherstellungsmodell festgelegt ist, werden einige Index-DDL-Vorgänge minimal protokolliert, unabhängig davon, ob der Vorgang offline oder online ausgeführt wird. Die minimal protokollierten Indexvorgänge sind nachfolgend aufgeführt:

    • CREATE INDEX -Vorgänge (einschließlich indizierter Sichten).

    • ALTER INDEX REBUILD- oder DBCC DBREINDEX-Vorgänge.

      Warnung

      Die DBCC DBREINDEX-Anweisung ist veraltet und sollte daher in neuen Anwendungen nicht verwendet werden.

      Hinweis

      Bei Indexerstellungsvorgängen ist die Protokollierung minimiert, aber möglicherweise verzögert sie sich, wenn eine gleichzeitig ausgeführte Sicherung vorhanden ist. Diese Verzögerung wird durch die Synchronisierungsanforderungen minimal protokollierter Pufferpoolseiten verursacht, wenn das einfache Modell oder das massenprotokollierte Wiederherstellungsmodell verwendet wird.

    • Neuerstellungen neuer Heaps mit DROP INDEX (falls zutreffend). Aufhebungen von Indexseitenzuordnungen während eines DROP INDEX-Vorgangs werden immer vollständig protokolliert.

Verwalten des Transaktionsprotokolls

Sichern des Transaktionsprotokolls (vollständiges Wiederherstellungsmodell)

Wiederherstellen des Transaktionsprotokolls (vollständiges Wiederherstellungsmodell)

Weitere Informationen

Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server
Steuern der Transaktionsdauerhaftigkeit
Voraussetzungen für die minimale Protokollierung beim Massenimport
Sichern und Wiederherstellen von SQL Server-Datenbanken
Übersicht über Wiederherstellungsvorgänge (SQL Server)
Datenbankprüfpunkte (SQL Server)
Anzeigen oder Ändern der Eigenschaften einer Datenbank
Wiederherstellungsmodelle (SQL Server)
Transaktionsprotokollsicherungen (SQL Server)
sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL)