Teilen über

Das Transaktionsprotokoll

  • Artikel

Gilt für: SQL Server

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

Das Transaktionsprotokoll ist eine wichtige Komponente der Datenbank. Im Falle eines Systemausfalls benötigen Sie dieses Protokoll, um Ihre Datenbank wieder in einen konsistenten Zustand zu versetzen.

Warnung

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

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

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: AlwaysOn-Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand.

Wiederherstellen einzelner Transaktionen

Wenn eine Anwendung eine ROLLBACK-Anweisung ausgibt oder die Datenbank-Engine einen Fehler erkennt (z.B. die unterbrochene Verbindung mit einem Client), wird anhand der Protokolldatensätze für die Änderungen, die von einer nicht abgeschlossenen Transaktion vorgenommen wurde, ein Rollback ausgeführt.

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 die Datenbank-Engine erneut sämtliche im Protokoll aufgezeichneten Änderungen, um für alle Transaktionen einen Rollforward auszuführen. Wenn die letzte Protokollsicherung wiederhergestellt wird, verwendet die Datenbank-Engine die Protokollinformationen, um einen Rollback aller Transaktionen durchzufü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, AlwaysOn-Verfügbarkeitsgruppen, Datenbankspiegelung und Protokollversand hängen in großem Umfang vom Transaktionsprotokoll ab.

In einem Szenario mit Always On-Verfügbarkeitsgruppen wird jede Aktualisierung einer Datenbank auf dem primären Replikat sofort in den separaten Kopien der Datenbank auf allen sekundären Replikaten reproduziert. Das primäre Replikat sendet jeden Protokolldatensatz sofort an die sekundären Replikate, die die eingehenden Protokolldatensätze auf die Verfügbarkeitsdatenbanken anwenden und kontinuierlich einen Rollforward auf das Protokoll anwenden. Weitere Informationen finden Sie unter Always On-Failoverclusterinstanzen (SQL Server).

In einem Protokollversandszenario sendet der primäre Server die Transaktionsprotokollsicherungen der primären Datenbank an ein oder mehrere Ziele. Jeder sekundäre Server stellt die Protokollsicherungen in seiner lokalen sekundären Datenbank wieder her. Weitere Informationen finden Sie unter Informationen zum Protokollversand (SQL Server).

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 (SQL Server).

Merkmale von Transaktionsprotokollen

Merkmale des SQL Server Datenbank-Engine-Transaktionsprotokolls:

  • 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 der SQL Server-Datenbank-Engine ergibt. Weitere Informationen finden Sie unter Physische Architektur des Transaktionsprotokolls.

  • Das Format der Protokolldatensätze und -seiten muss nicht zwingend dem Format der 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-(Transact-SQL-)Optionen für Dateien und Dateigruppen.

  • 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).

  • Wenn Sie zunächst eine Datenbank mit dem vollständigen Wiederherstellungsmodell erstellen, wird das Transaktionsprotokoll nach Bedarf wiederverwendet (ähnlich zu einer Datenbank, die das einfache Wiederherstellungmodell nutzt), bis eine vollständige Datenbanksicherung erfolgt.

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

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

Wenn Protokolldatensätze lange Zeit aktiv bleiben, verzögert sich die Kürzung des Transaktionsprotokolls, und das Transaktionsprotokoll kann sich füllen, wie in diesem Artikel bereits erwähnt.

Wichtig

Informationen zum Umgang mit einem vollen Transaktionsprotokoll finden Sie unter Problembehandlung bei einem vollen Transaktionsprotokoll (SQL Server-Fehler 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) (alle Wiederherstellungsmodelle) hinaus verschoben.

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. Lang laufende Transaktionen verhindern die Protokollkürzung bei allen Wiederherstellungsmodellen, einschließlich des einfachen Wiederherstellungsmodells, bei dem das Transaktionsprotokoll im allgemeinen bei 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 REPLICATION 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 ausgeführt (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. (Nur vollständiges Wiederherstellungsmodell).

Weitere Informationen finden Sie unter Was sind Always On-Verfügbarkeitsgruppen?.
10 - Nur zur internen Verwendung
11 - Nur zur internen Verwendung
12 - Nur zur internen 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).

Informationen zu indirekten Prüfpunkten finden Sie unter Datenbankprüfpunkte (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. Bei speicheroptimierten Tabellen wird ein automatischer Prüfpunkt gesetzt, wenn die Transaktionsprotokolldatei seit dem letzten Prüfpunkt größer als 1,5 GB geworden ist (gilt sowohl für datenträgerbasierte als auch für speicheroptimierte Tabellen).

Weitere Informationen finden Sie unter Prüfpunktvorgang für speicheroptimierte Tabellen und [Protokollierungs- und Prüfpunktprozess für In-Memory optimierte 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 derminimalen Protokollierung werden nur die Informationen protokolliert, die zum Wiederherstellen der Transaktion ohne Unterstützung der Zeitpunktwiederherstellung erforderlich sind. In diesem Artikel 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).

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

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:

  • Massenimportvorgänge (bcp, BULK INSERT und INSERT). Weitere Informationen zur minimalen Protokollierung eines Massenimports in eine Tabelle finden Sie unter Prerequisites for Minimal Logging in Bulk Import.

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

  • SELECT – INTO-Klausel-Vorgänge.

    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. Die minimale Protokollierung wird nicht verwendet, wenn vorhandene Werte aktualisiert werden. Weitere Informationen zu Datentypen für hohe Werte finden Sie unter Datentypen.

  • WRITETEXT -Anweisung und UPDATETEXT -Anweisung beim Einfügen oder Anfügen neuer Daten an die Datentypspalten text, ntextund image . Die minimale Protokollierung wird nicht verwendet, 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-Vorgang.

      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.

      Warnung

      Die DBCC DBREINDEX-Anweisung ist veraltet, sollte also in neuen Anwendungen nicht mehr verwendet werden.

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

Aufgabe Artikel
Verwalten des Transaktionsprotokolls - Verwalten der Größe der Transaktionsprotokolldatei

- Problembehandlung bei vollen Transaktionsprotokollen (SQL Server-Fehler 9002)
Sichern des Transaktionsprotokolls (nur vollständiges Wiederherstellungsmodell) - Sichern eines Transaktionsprotokolls

- Sichern des Transaktionsprotokolls bei beschädigter Datenbank (SQL Server)
Wiederherstellen von Transaktionsprotokollen (nur vollständiges Wiederherstellungsmodell) - Wiederherstellen einer Transaktionsprotokollsicherung (SQL Server)

Zugehöriger Inhalt