Das Transaktionsprotokoll [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. Wenn ein Systemfehler auftritt, benötigen Sie dieses Protokoll, um Ihre Datenbank wieder in einen konsistenten Zustand zu versetzen. Dieses Protokoll sollten Sie nicht löschen oder verschieben, wenn Sie sich über die Auswirkungen dieses Vorgangs nicht vollständig im Klaren sind.

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

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

Wiederherstellen einzelner Transaktionen

Wenn eine Anwendung eine ROLLBACK-Anweisung ausgibt oder wenn das Datenbankmodul 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, auf dem SQL Server ausgeführt wird, 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.

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 das Datenbankmodul erneut sämtliche im Protokoll aufgezeichneten Änderungen, um für alle Transaktionen einen Rollforward auszuführen. Sobald die letzte Protokollsicherung wiederhergestellt ist, verwendet das Datenbankmodul die Protokollinformationen, um für sämtliche Transaktionen einen Rollback auszuführen, die zu diesem Zeitpunkt noch nicht abgeschlossen waren.

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 AlwaysOn-Verfügbarkeitsgruppe wird jede Aktualisierung einer Datenbank (das primäre Replikat) 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 Protokollversendungsszenario 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.

SQL Server-Datenbankmodul-Transaktionsprotokolle weisen die folgenden Merkmale auf:

  • 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 Datenbankmoduls ergibt.
  • 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 automatische Vergrößerung 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).
  • Der Mechanismus zum erneuten Verwenden des freien Speicherplatzes in den Protokolldateien ist schnell und wirkt sich nur minimal auf den Transaktionsdurchsatz aus.

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 kürzen, damit es nicht den zugewiesenen Speicherplatz füllt. 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 aus dem logischen Transaktionsprotokoll einer SQL Server -Datenbank gelöscht. Zudem wird Speicherplatz im logischen Protokoll zur Wiederverwendung durch das physische Transaktionsprotokoll freigegeben. Wird ein Transaktionsprotokoll nicht gekürzt, füllt sich dadurch möglicherweise der gesamte Speicherplatz des Datenträgers auf, der den zugehörigen physischen Protokolldateien zugeordnet ist.

Um dieses Problem zu vermeiden, 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önnenweiter 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 Manage the Size of the Transaction Log File.

Bleiben Protokolldatensätze lange aktiv, verzögert sich die Transaktionsprotokollkürzung. Dabei kann sich das Transaktionsprotokoll potenziell auffüllen.

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 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 valuelog_reuse_wait_desc valueBeschreibung
0NOTHINGDerzeit ist mindestens eine wiederverwendbare virtuelle Protokolldatei vorhanden.
1CHECKPOINTSeit der letzten Protokollkürzung ist kein Prüfpunkt aufgetreten, oder der Kopf des Protokolls wurde noch nicht über eine virtuelle Protokolldatei hinaus verschoben. (Alle Wiederherstellungsmodelle)

Dies ist ein häufiger Grund für das verzögerte Kürzen von Protokollen. Weitere Informationen finden Sie unter Database Checkpoints (SQL Server).
2LOG_BACKUPEine 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.
3ACTIVE_BACKUP_OR_RESTOREEs 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.
4ACTIVE_TRANSACTIONEine 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).
5DATABASE_MIRRORINGDie 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).
6REPLICATIONWä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.
7DATABASE_SNAPSHOT_CREATIONEine 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.
8LOG_SCANEin 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.
9AVAILABILITY_REPLICAEin 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).
10Nur interne Verwendung
11Nur interne Verwendung
12Nur interne Verwendung
13OLDEST_PAGEIst eine Datenbank zur Verwendung von indirekten Prüfpunkten konfiguriert, ist die älteste Seite in der Datenbank u. U. älter als die Prüfpunkt-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).
14OTHER_TRANSIENTDieser Wert wird derzeit nicht verwendet.

Bei derminimalen 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.

WEITERER 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.

  • SELECT INTO -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. 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, ntextund image . Beachten Sie, dass die minimale Protokollierung nicht verwendet wird, wenn vorhandene Werte aktualisiert werden.

    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.

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

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

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
Datenbankprüfpunkte (SQL Server)
Anzeigen oder Ändern der Eigenschaften einer Datenbank
Wiederherstellungsmodelle (SQL Server)

Community-Beiträge

HINZUFÜGEN
Anzeigen: