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. Im Falle eines Systemausfalls 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.

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.

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.

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.

Zugehörige Aufgaben

Zugehöriger Inhalt

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