Das Transaktionsprotokoll

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. Wenn ein Systemfehler auftritt, benötigen Sie dieses Protokoll, um Die Datenbank wieder in einen konsistenten Zustand zu versetzen.

Informationen zur physischen und logischen Architektur des Transaktionsprotokolls finden Sie in der SQL Server-Transaktionsprotokollarchitektur und im Verwaltungshandbuch.

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ützung 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 ausführt oder die Datenbank-Engine einen Fehler erkennt, beispielsweise einen Kommunikationsausfall mit einem Client, werden die Protokolldatensätze verwendet, um die von einer unvollständigen Transaktion vorgenommenen Änderungen rückgängig zu machen.

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 das Wiederherstellen und die Wiederherstellung (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 Wiederherstellen und Wiederherstellung (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 Protokolleintrag sofort an die sekundären Replikate, die die eingehenden Protokolleinträge in die Verfügbarkeitsdatenbanken einspielen und dabei das Protokoll fortlaufend weiterrollen. 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 primäre Serverinstanz sendet jeden Protokolleintrag sofort an die Spiegelserverinstanz, die die eingehenden Protokolleinträge in der Spiegeldatenbank anwendet und sie dadurch fortlaufend aktualisiert. 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 separat vom Puffercache für Datenseiten verwaltet. Diese Trennung führt zu einfachem, schnellen und robusten Code innerhalb des SQL Server-Datenbankmoduls. 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. Sie können die Dateien so konfigurieren, dass sie automatisch erweitert werden, indem Sie den FILEGROWTH Wert für das Protokoll festlegen. Diese Konfiguration reduziert das Potenzial, im Transaktionsprotokoll nicht mehr Platz zu haben, wodurch gleichzeitig der Verwaltungsaufwand reduziert wird. Weitere Informationen finden Sie unter ALTER DATABASE (Transact-SQL) Datei- und Dateigruppenoptionen.

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

Informationen zur physischen und logischen Architektur des Transaktionsprotokolls finden Sie in der SQL Server-Transaktionsprotokollarchitektur und im Verwaltungshandbuch.

Trunkierung des Transaktionsprotokolls

Die Protokollkürzung gibt Speicherplatz in der Protokolldatei zur Wiederverwendung durch das Transaktionsprotokoll frei. 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 können minimal protokolliert werden, um deren Auswirkungen auf die Größe des Transaktionsprotokolls zu verringern.

Die Protokollkürzung löscht inaktive virtuelle Protokolldateien (VLFs) aus dem logischen Transaktionsprotokoll einer SQL Server-Datenbank, wobei Speicherplatz im logischen Protokoll für die 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 zum ersten Mal eine Datenbank erstellen, die das vollständige Wiederherstellungsmodell verwendet, wird das Transaktionsprotokoll nach Bedarf wiederverwendet (ähnlich einer Datenbank mit dem einfachen Wiederherstellungsmodell), bis zum Zeitpunkt der Erstellung einer vollständigen Datenbanksicherung.

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

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. Beachten Sie jedoch Faktoren, die die Protokollkürzung verzögern können. Wenn der Speicherplatz nach dem Verkleinern des Protokolls erneut benötigt wird, wird das Transaktionsprotokoll erneut vergrößert und führt dazu zu leistungseinbußen bei Protokollvergrößerungsvorgängen.

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

Wenn Protokolldatensätze lange aktiv bleiben, wird der Transaktionsprotokollabzug verzögert, und das Transaktionsprotokoll kann ausgefüllt werden, wie weiter oben in diesem Artikel beschrieben.

Das Abschneiden von Protokollen kann aus verschiedenen Gründen verzögert werden. Um zu erfahren, was das Abschneiden des Protokolls verhindert, fragen Sie die log_reuse_wait- und log_reuse_wait_desc-Spalten der sys.databases-Katalogsicht ab. In der folgenden Tabelle werden die Werte dieser Spalten beschrieben.

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

Die minimale Protokollierung umfasst nur die Protokollierung der Informationen, die zum Wiederherstellen der Transaktion erforderlich sind, ohne die Point-in-Time-Wiederherstellung zu unterstützen. In diesem Artikel werden die Vorgänge identifiziert, die unter dem Wiederherstellungsmodell mit massenprotokollierten Vorgängen nur minimal protokolliert werden (sowie auch unter dem einfachen Wiederherstellungsmodell, es sei denn, es wird gerade 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 für diese Vorgänge vorübergehend auf das Wiederherstellungsmodell mit massenhafter Protokollierung umstellen. 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 INSERTund INSERT). Weitere Informationen darüber, wann der Massenimport in eine Tabelle nur minimal protokolliert wird, finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport.

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

• SELECT – INTO-Klausel-Vorgänge.

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

• Teilweise Aktualisierungen von Datentypen für große Werte mithilfe der .WRITE-Klausel in der UPDATE-Anweisung beim Einfügen oder Anhängen 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 UPDATETEXT-Anweisungen sind veraltet. Vermeiden Sie die Verwendung in neuen Anwendungen.

• Wenn die Datenbank auf das einfache oder massenprotokolliert 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:

  • CREATE INDEX Vorgänge (einschließlich indizierten Ansichten).

  • ALTER INDEX NEU ERSTELLEN oder DBCC DBREINDEX-Vorgang.

    Indexbuildvorgänge verwenden minimale Protokollierung, können jedoch verzögert werden, wenn gleichzeitig eine Sicherung ausgeführt wird. Diese Verzögerung wird durch die Synchronisierungsanforderungen minimal protokollierter Pufferpoolseiten verursacht, wenn Sie das Wiederherstellungsmodell mit einfacher oder Massenprotokollierung verwenden.

    Warnung

    Die DBCC DBREINDEX Anweisung ist veraltet. Vermeiden Sie die Verwendung in neuen Anwendungen.

  • DROP INDEX neuer Heap-Neuaufbau (falls zutreffend). Indexseiten-Deallocation während eines DROP INDEX Vorgangs wird immer vollständig protokolliert.

Zugehörige Aufgaben

Zugehöriger Inhalt

• Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server
• Steuern der Lebensdauer von Transaktionen
• Voraussetzungen für die minimale Protokollierung beim Massenimport
• Sichern und Wiederherstellen von SQL Server-Datenbanken
• Übersicht über Wiederherstellung und Recovery (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)