Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL-Datenbank in Microsoft Fabric

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 und wird im Falle eines Systemfehlers ggf. benötigt, um einen konsistenten Status der Datenbank wiederherzustellen. Dieses Handbuch enthält Informationen zur physischen und logischen Architektur des Transaktionsprotokolls. Eine gute Kenntnis der Architektur kann Ihnen dabei helfen, Transaktionsprotokolle effizienter zu verwalten.

Logische Architektur des Transaktionsprotokolls

Das SQL Server-Transaktionsprotokoll wird logisch so verwendet, als handele es sich um eine Folge von Protokolleinträgen. Jeder Protokolleintrag wird durch eine Protokollsequenznummer (LSN, Log Sequence Number) gekennzeichnet. Jeder neue Protokolleintrag wird an das logische Ende des Protokolls geschrieben und erhält eine LSN, die höher ist als die LSN des vorherigen Eintrags. Protokolldatensätze werden beim Erstellen in einer seriellen Reihenfolge gespeichert. Wenn LSN2 größer als LSN1 ist, erfolgte die durch den Protokolldatensatz von LSN2 beschriebene Änderung nach der durch den Protokolldatensatz von LSN1 beschriebenen Änderung. Jeder Protokolleintrag enthält die ID der Transaktion, zu der er gehört. Für jede Transaktion werden alle Protokolleinträge, die mit dieser Transaktion verbunden sind, individuell zu einer Kette verknüpft. Dies erfolgt mithilfe von Rückwärtszeigern, durch die der Rollback der Transaktion beschleunigt wird.

Die grundlegende Struktur einer LSN ist [VLF ID:Log Block ID:Log Record ID]. Weitere Informationen finden Sie in den Abschnitten VLF und Protokollblock.

Hier ist ein Beispiel für eine LSN: 00000031:00000da0:0001, wobei 0x31 die ID der VLF, 0xda0 die Protokollblock-ID und 0x1 der erste Protokolldatensatz in diesem Protokollblock ist. Beispiele für LSNs: Sehen Sie sich die Ausgabe von sys.dm_db_log_info DMV an, und untersuchen Sie die vlf_create_lsn-Spalte.

Protokolleinträge für Datenänderungen zeichnen entweder die durchgeführte logische Operation oder die Anfangs- und Endimages der geänderten Daten auf. Ein Anfangsimage ist eine Kopie der Daten vor der Durchführung der Operation. Ein Endimage ist eine Kopie der Daten, nachdem die Operation durchgeführt wurde.

Die Schritte zum Wiederherstellen einer Operation hängen von der Art des Protokolleintrags ab:

• Protokollierung der logischen Operation

  • Um einen Rollforward für die logische Operation auszuführen, wird sie erneut durchgeführt.
  • Um einen Rollback für die logische Operation auszuführen, wird der logische Umkehrvorgang durchgeführt.

• Protokollierung der Anfangs- und Endimages

  • Um einen Rollforward für die Operation auszuführen, wird das Endimage übernommen.
  • Um einen Rollback für die Operation auszuführen, wird das Anfangsimage übernommen.

Im Transaktionsprotokoll werden viele Operationsarten aufgezeichnet. Dazu zählen die Operationen:

• Der Beginn und das Ende jeder Transaktion.

• Jede Datenänderung (Einfügung, Update oder Löschung). Dazu zählen auch Änderungen, die von gespeicherten Systemprozeduren oder DDL-Anweisungen (Data Definition Language, Datendefinitionssprache) an beliebigen Tabellen, einschließlich den Systemtabellen, vorgenommen werden.

• Jede Zuordnung oder Zuordnungsaufhebung von Blöcken und Seiten

• Erstellen oder Löschen einer Tabelle oder eines Indexes.

Rollback-Operationen werden ebenfalls protokolliert. Jede Transaktion reserviert Speicherplatz im Transaktionsprotokoll, um sicherzustellen, dass ausreichend Speicherplatz vorhanden ist, um einen Rollback infolge einer expliziten Rollback-Anweisung oder im Falle eines Fehlers zu unterstützen. Die Menge des reservierten Speicherplatzes hängt von den in der Transaktion durchgeführten Vorgängen ab, entspricht jedoch im Allgemeinen dem Speicherplatz, der zum Protokollieren der einzelnen Vorgänge verwendet wird. Dieser reservierte Speicherplatz wird freigegeben, sobald die Transaktion abgeschlossen ist.

Als aktiver Teil des Protokolls aktives Protokoll oder Protokollfragment wird der Abschnitt der Protokolldatei aus dem ersten Protokolldatensatz bezeichnet, der für einen erfolgreichen Rollback der gesamten Datenbank auf den zuletzt geschriebenen Protokolldatensatz benötigt wird. Dies ist der Teil des Protokolls, der für eine vollständige Wiederherstellung der Datenbank erforderlich ist. Vom aktiven Teil des Protokolls kann niemals ein Teil abgeschnitten werden. Die Protokollfolgenummer (Log Sequence Number, LSN) des ersten Protokolldatensatzes wird als Mindestwiederherstellungs-LSN (MinLSN) bezeichnet. Weitere Informationen zu Vorgängen, die vom Transaktionsprotokoll unterstützt werden, finden Sie unter Das Transaktionsprotokoll.

Durch differenzielle Sicherungen und Protokollsicherungen wird ein späterer Status der Datenbank wiederhergestellt, was wiederum einer höheren LSN entspricht.

Physische Architektur des Transaktionsprotokolls

Das Datenbank-Transaktionsprotokoll erstreckt sich über eine oder mehrere physische Dateien. Konzeptionell ist die Protokolldatei eine Folge von Protokolldatensätzen. Physisch wird die Folge von Protokolldatensätzen effizient in dem Satz physischer Dateien gespeichert, die das Transaktionsprotokoll implementieren. Für jede Datenbank muss mindestens eine Protokolldatei vorhanden sein.

Virtuelle Protokolldateien (Virtual Log Files, VLFs)

Die SQL Server-Datenbank-Engine teilt jede physische Protokolldatei intern in verschiedene virtuelle Protokolldateien (VLFs) auf. Virtuelle Protokolldateien haben keine feste Größe, und es gibt keine feststehende Anzahl virtueller Protokolldateien für eine physische Protokolldatei. Die Datenbank-Engine wählt die Größe der virtuellen Protokolldateien dynamisch beim Erstellen oder Erweitern von Protokolldateien aus. Die Datenbank-Engine versucht, einige virtuelle Dateien beizubehalten. Welche Größe die virtuellen Dateien haben, nachdem eine Protokolldatei erweitert wurde, hängt von der zusammengenommenen Größe des vorhandenen Protokolls und dem Umfang der Dateierweiterung ab. Die Größe oder Anzahl der virtuellen Protokolldateien kann nicht von Administratoren konfiguriert oder festgelegt werden.

Erstellen virtueller Protokolldateien

Die VLF (Virtual Log File) wird gemäß der folgenden Methode erstellt:

• Wenn in SQL Server 2014 (12.x) und späteren Versionen die nächste Dateivergrößerung weniger als 1/8 der aktuellen physischen Protokollgröße beträgt, wird 1 VLF erstellt, die den Umfang der Dateivergrößerung abdeckt.
• Verwenden Sie die Methode, die vor 2014 eingeführt wurde, wenn die nächste Dateivergrößerung mehr als 1/8 der aktuellen Protokollgröße beträgt, nämlich:
  • Wenn die Dateivergrößerung unter 64 MB liegt, werden 4 VLFs erstellt, die den Umfang der Dateivergrößerung abdecken. (Zum Beispiel werden für eine Vergrößerung von 1 MB 4 VLFs mit der Größe von 256 KB erstellt.)
    • In der Azure SQL-Datenbank und ab SQL Server 2022 (16.x) (alle Editionen) unterscheidet sich die Logik geringfügig. Wenn die Dateivergrößerung kleiner oder gleich 64 MB ist, erstellt die Datenbank-Engine nur eine VLF, um die Dateivergrößerung abzudecken.
  • Wenn die Dateivergrößerung von 64 MB bis 1 GB beträgt, werden 8 VLFs erstellt, die den Umfang der Dateivergrößerung abdecken. (Zum Beispiel werden für eine Vergrößerung von 512 MB 8 VLFs mit der Größe von 64 MB erstellt).
  • Bei einer Dateivergrößerung von mehr als 1 GB werden 16 VLFs erstellt, die den Umfang der Dateivergrößerung abdecken, beispielsweise werden für eine Vergrößerung von 8 GB 16 VLFs mit der Größe von 512 MB erstellt).

Wenn die Protokolldateien durch viele kleine Inkremente auf eine beträchtliche Größe anwachsen, beinhalten sie zahlreiche virtuelle Protokolldateien. Das kann den Start der Datenbank verlangsamen, Sicherungs- und Wiederherstellungsvorgänge protokollieren und zu Transaktionsreplikation/CDC und Always-On-Wiederholungswartezeit führen. Umgekehrt enthalten diese wenige große virtuelle Protokolldateien, wenn die Protokolldateien durch wenige oder nur ein Inkrement auf eine beträchtliche Größe anwachsen. Weitere Informationen zum Schätzen der erforderlichen Größe und der Einstellung für die automatische Vergrößerung eines Transaktionsprotokolls finden Sie unter Verwalten der Größe der Transaktionsprotokolldatei im Abschnitt Empfehlungen.

Es wird empfohlen, die Protokolldateien für eine optimale VLF-Verteilung mithilfe der erforderlichen Inkremente zu erstellen, damit ungefähr der endgültigen erforderlichen Größe entsprochen wird, und darüber hinaus einen relativ hohen Wert für growth_increment festzulegen.

Lesen Sie den folgenden Tipp, um die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls zu ermitteln:

• Der mit dem Argument SIZE festgelegte size-Wert von ALTER DATABASE ist die Anfangsgröße der Protokolldatei.
• Der growth_increment-Wert (auch als Wert für die automatische Vergrößerung bekannt), den das Argument FILEGROWTH von ALTER DATABASE festgelegt, entspricht der Menge von Speicherplatz, die der Datei immer dann hinzugefügt wird, wenn neuer Speicherplatz erforderlich wird.

Weitere Informationen zu den Argumenten FILEGROWTH und SIZE von ALTER DATABASE finden Sie unter ALTER DATABASE-Optionen FILE und FILEGROUP (Transact-SQL).

Tipp

Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie im Skript zur Korrektur von VLFs auf GitHub.

Was geschieht, wenn Sie zu viele VLFs haben?

In den ersten Phasen eines Prozesses zur Datenbankwiederherstellung ermittelt SQL Server alle VLFs in allen Transaktionsprotokolldateien und erstellt eine Liste dieser VLFs. Dieser Prozess kann je nach Anzahl der in der jeweiligen Datenbank vorhandenen VLFs sehr lange dauern. Je mehr VLFs, desto länger dauert der Prozess. Eine Datenbank kann eine große Anzahl von VLFs haben, wenn häufig eine automatische Vergrößerung des Transaktionsprotokolls oder manuelle Vergrößerung in kleinen Schritten auftritt. Wenn die Anzahl der VLFs den Bereich von mehreren Hunderttausend erreicht, können Sie einige oder die meisten der folgenden Symptome feststellen:

• Für mindestens eine Datenbank dauert es sehr lange, bis die Wiederherstellung während des SQL Server-Starts abgeschlossen ist.
• Das Abschließen der Wiederherstellung einer Datenbank dauert sehr lange.
• Der Versuch, eine Datenbank anzufügen, dauert sehr lange.
• Beim Versuch, die Datenbankspiegelung einzurichten, treten Fehlermeldungen 1413, 1443 und 1479 auf, die auf eine Zeitüberschreitung hinweisen.
• Beim Versuch, eine Datenbank wiederherzustellen, treten speicherbezogene Fehler wie 701 auf.
• Die Transaktionsreplikation oder die Change Data Capture kann erhebliche Wartezeit haben.

Wenn Sie das SQL Server-Fehlerprotokoll untersuchen, stellen Sie möglicherweise fest, dass vor der Analysephase des Prozesses zur Datenbankwiederherstellung erheblicher Zeitaufwand bestand. Zum Beispiel:

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Darüber hinaus kann SQL Server einen Fehler MSSQLSERVER_9017 protokollieren, wenn Sie eine Datenbank mit einer großen Anzahl an VLFs wiederherstellen:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Weitere Informationen finden Sie unter MSSQLSERVER_9017.

Beheben von Datenbanken mit einer großen Anzahl an VLFs

Damit die Gesamtanzahl der VLFs angemessen bleibt, z. B. maximal mehrere Tausend, können Sie die Transaktionsprotokolldatei zurücksetzen, um eine kleinere Anzahl von VLFs beizubehalten, indem Sie die folgenden Schritte ausführen:

1. Verkleinern Sie die Transaktionsprotokolldateien manuell.

2. Vergrößern Sie die Dateien manuell wie erforderlich in einem Schritt mithilfe des folgenden T-SQL-Skripts:

   ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

   Hinweis

   Dieser Schritt ist auch in SQL Server Management Studio mithilfe der Eigenschaftsseite für Datenbanken möglich.

Nachdem Sie das neue Layout der Transaktionsprotokolldatei mit weniger VLFs festgelegt haben, überprüfen Sie es und nehmen Sie die erforderlichen Änderungen an den Einstellungen für die automatische Vergrößerung des Transaktionsprotokolls vor. Diese Validierung der Einstellung stellt sicher, dass die Protokolldatei in Zukunft dasselbe Problem verhindert.

Bevor Sie eine dieser Operationen ausführen, sorgen Sie dafür, dass Sie über eine gültige wiederherstellbare Sicherung verfügen, falls später Probleme auftreten.

Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie im Skript zur Korrektur von VLFs.

Protokollblöcke

Jede VLF enthält mindestens einen Protokollblock. Jeder Protokollblock besteht aus den Protokolldatensätzen (ausgerichtet an einer 4-Byte-Grenze). Ein Protokollblock ist eine variable Größe und immer ein Integer-Wert von 512 Byte (die von SQL Server unterstützte Mindestsektorgröße), mit einer maximalen Größe von 60 KB. Ein Protokollblock ist die Grundeinheit von E/A für die Protokollierung von Transaktionen.

Zusammenfassend ist ein Protokollblock ein Container mit Protokolldatensätzen, der beim Schreiben von Protokolldatensätzen auf den Datenträger als Basiseinheit für die Transaktionsprotokollierung verwendet wird.

Jeder Protokollblock innerhalb einer VLF wird durch einen Blockversatz eindeutig adressiert. Der erste Block hat immer einen Blockversatz, der über die ersten 8 KB in der VLF hinaus verweist.

Im Allgemeinen wird eine VLF immer mit Protokollblöcken aufgefüllt. Der letzte Protokollblock in einer VLF kann leer sein (z. B. er enthält keine Protokolldatensätze). Das passiert, wenn ein zu schreibender Protokolldatensatz nicht in den aktuellen Protokollblock passt und auch, wenn der für die VLF übrige Speicherplatz nicht ausreicht, um diesen Protokolldatensatz zu speichern. In diesem Fall wird ein leerer Protokollblock erstellt, der die VLF ausfüllt. Der Protokolldatensatz wird in den ersten Block der nächsten VLF eingefügt.

Zyklischer Charakter des Transaktionsprotokolls

Das Transaktionsprotokoll ist eine umbrechende Protokolldatei. Nehmen Sie beispielsweise an, eine Datenbank verfügt über eine physische Protokolldatei, die in vier VLFs unterteilt ist. Wenn die Datenbank erstellt wird, beginnt die logische Protokolldatei am Anfang der ersten physischen Protokolldatei. Neue Protokolldatensätze werden am Ende des logischen Protokolls hinzugefügt, das in Richtung des Endes des physischen Protokolls erweitert wird. Beim Abschneiden eines Protokolls werden alle virtuellen Protokolle freigegeben, deren Datensätze sich ohne Ausnahme vor der Mindestwiederherstellungs-Protokollfolgenummer (Minimum Recovery Log Sequence Number, MinLSN) befinden. MinLSN ist die Protokollfolgenummer des ältesten Protokolldatensatzes, der für einen erfolgreichen Rollback der gesamten Datenbank benötigt wird. Das Transaktionsprotokoll in der Beispieldatenbank würde in etwa so aussehen wie das Protokoll im folgenden Diagramm.

Wenn das Ende des logischen Protokolls das Ende der physischen Protokolldatei erreicht, erfolgt ein Umbruch, und neue Protokolldatensätze werden nun wieder am Anfang der physischen Protokolldatei eingefügt.

Solange das Ende des logischen Protokolls nicht den Anfang des logischen Protokolls erreicht, wird dieser Kreislauf endlos wiederholt. Wenn die alten Protokolldatensätze häufig genug abgeschnitten werden, um ausreichend Platz für alle neuen Protokolldatensätze freizugeben, die bis zum nächsten Prüfpunkt erstellt werden, wird das Protokoll nie vollständig aufgefüllt. Wenn das Ende des logischen Protokolls jedoch den Anfang des logischen Protokolls erreicht, wird eine der beiden folgenden Aktionen eingeleitet:

• Wenn die FILEGROWTH-Einstellung für das Protokoll aktiviert und auf dem Datenträger Speicherplatz verfügbar ist, wird die Datei um die Menge vergrößert, die im growth_increment-Parameter angegeben ist, und der Erweiterung werden neue Protokolldatensätze hinzugefügt. Weitere Informationen zur FILEGROWTH-Einstellung finden Sie unter ALTER DATABASE-Optionen FILE und FILEGROUP (Transact-SQL).

• Wenn die FILEGROWTH-Einstellung nicht aktiviert ist oder der Datenträger mit der Protokolldatei über weniger freien Speicherplatz verfügt als in growth_increment angegeben, wird der Fehler 9002 generiert. Weitere Informationen finden Sie unter Problembehandlung bei vollen Transaktionsprotokollen (SQL Serverfehler 9002).

Wenn das Protokoll mehrere physische Protokolldateien enthält, durchläuft das logische Protokoll alle physischen Protokolldateien, bevor es umbricht und neue Einträge am Anfang der ersten physischen Protokolldatei einfügt.

Wichtig

Weitere Informationen zur Verwaltung von Transaktionsprotokolldateien finden Sie unter Verwalten der Größe der Transaktionsprotokolldatei.

Protokollkürzung

Die Protokollkürzung ist wichtig, um ein Auffüllen des Protokolls verhindern zu können. Durch die Protokollkürzung werden inaktive virtuelle Protokolldateien 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. Bevor das Protokoll jedoch gekürzt werden kann, ist ein Prüfpunktvorgang erforderlich. Durch einen Prüfpunktvorgang werden die aktuellen, im Arbeitsspeicher geänderten Seiten (auch als modifizierte Seiten bezeichnet) sowie Transaktionsprotokollinformationen vom Arbeitsspeicher auf den Datenträger geschrieben. Beim Ausführen des Prüfpunkts wird der inaktive Teil des Transaktionsprotokolls als wiederverwendbar markiert. Anschließend kann ein Abschneiden des Protokolls den inaktiven Teil des Protokolls freigeben. Weitere Informationen zu Prüfpunkten finden Sie unter Datenbankprüfpunkte (SQL Server).

Die folgenden Diagramme zeigen ein Transaktionsprotokoll vor und nach dem Abschneiden. Im ersten Diagramm wird ein Transaktionsprotokoll gezeigt, das noch nie abgeschnitten wurde. Aktuell verwendet das logische Protokoll vier virtuelle Protokolldateien. Das logische Protokoll beginnt am Anfang der ersten virtuellen Protokolldatei und endet beim virtuellen Protokoll 4. Der MinLSN-Datensatz befindet sich im virtuellen Protokoll 3. Das virtuelle Protokoll 1 und das virtuelle Protokoll 2 enthalten nur inaktive Protokolldatensätze. Diese Datensätze können abgeschnitten werden. Das virtuelle Protokoll 5 wurde noch nicht verwendet und ist nicht Teil des aktuellen logischen Protokolls.

Das zweite Diagramm zeigt das Protokoll, nachdem es abgeschnitten wurde. Die virtuellen Protokolle 1 und 2 wurden für die Wiederverwendung freigegeben. Das logische Protokoll beginnt nun am Anfang des virtuellen Protokolls 3. Das virtuelle Protokoll 5 wurde noch nicht verwendet und es ist nicht Teil des aktuellen logischen Protokolls.

Die Protokollkürzung erfolgt automatisch wie folgt, außer es tritt aus irgendeinem Grund eine Verzögerung auf:

• Unter dem einfachen Wiederherstellungsmodell, nach einem Prüfpunkt.
• Unter dem vollständigen oder massenprotokollierten Wiederherstellungsmodell, nach einer Protokollsicherung, wenn seit der vorherigen Sicherung ein Prüfpunkt aufgetreten ist.

Die Protokollkürzung kann durch verschiedene Faktoren verzögert werden. Im Falle einer langen Verzögerung der Protokollkürzung kann sich das Transaktionsprotokoll füllen. Weitere Informationen finden Sie unter Faktoren, die die Protokollkürzung verzögern können und Problembehandlung bei vollen Transaktionsprotokollen (SQL Server-Fehler 9002).

Write-Ahead-Transaktionsprotokoll

In diesem Abschnitt wird die Aufgabe des Write-Ahead-Transaktionsprotokolls beim Aufzeichnen von Datenänderungen auf dem Datenträger beschrieben. SQL Server verwendet einen Write-Ahead-Protokollalgorithmus (WAL), durch das sichergestellt wird, dass Datenänderungen erst dann auf den Datenträger geschrieben werden, nachdem der entsprechende Protokolldatensatz auf den Datenträger geschrieben wurde. Dies schützt die ACID-Eigenschaften einer Transaktion.

Weitere Informationen zu WAL finden Sie unter Grundlagen zu SQL Server-E/A.

Um die Funktionsweise des Write-Ahead-Protokolls in Bezug auf das Transaktionsprotokoll zu verstehen, müssen Sie zunächst wissen, wie geänderte Daten auf den Datenträger geschrieben werden. SQL Server verwaltet einen Puffercache (auch als Pufferpool bezeichnet), in den Datenseiten gelesen werden, wenn Daten abgerufen werden müssen. Eine Seite, die im Puffercache geändert wurde, wird nicht sofort auf den Datenträger geschrieben, sondern als geändert markiert. Auf einer Datenseite können mehrere logische Schreibvorgänge ausgeführt werden, bevor sie physisch auf den Datenträger geschrieben wird. Für jeden logischen Schreibvorgang wird ein Transaktionsprotokoll-Datensatz in den Protokollcache geschrieben, der die Änderung aufzeichnet. Die Protokolldatensätze müssen auf den Datenträger geschrieben werden, bevor die zugehörige modifizierte Seite aus dem Puffercache entfernt und auf den Datenträger geschrieben wird. Mit dem Prüfpunktprozess (checkpoint) wird der Puffercache regelmäßig auf Puffer mit Seiten aus einer angegebenen Datenbank überprüft, und alle modifizierten Seiten werden auf den Datenträger geschrieben. Durch Prüfpunkte kann bei einer späteren Wiederherstellung Zeit eingespart werden, da ein Punkt erstellt wird, an dem auf jeden Fall alle modifizierten Seiten auf den Datenträger geschrieben worden sind.

Wird eine geänderte Datenseite aus dem Puffercache auf den Datenträger geschrieben, wird dies als Leeren der Seite bezeichnet. Durch die Logik von SQL Server wird verhindert, dass eine geänderte Seite geleert wird, bevor der zugehörige Protokolldatensatz geschrieben wurde. Protokolldatensätze werden auf den Datenträger geschrieben, wenn die Protokollpuffer geleert werden. Dies geschieht immer dann, wenn eine Transaktion committet wird oder der Protokollpuffer voll wird.

Transaktionsprotokollsicherungen

In diesem Abschnitt werden Konzepte zum Sichern und Wiederherstellen (Anwenden) von Transaktionsprotokollen vorgestellt. Beim vollständigen und beim massenprotokollierten Wiederherstellungsmodell müssen zur Wiederherstellung von Daten routinemäßige Sicherungen der Transaktionsprotokolle (Protokollsicherungen) ausgeführt werden. Sie können das Protokoll sichern, während eine vollständige Sicherung ausgeführt wird. Weitere Informationen zu Wiederherstellungsmodellen finden Sie unter Sichern und Wiederherstellen von SQL Server-Datenbanken.

Bevor Sie die erste Protokollsicherung erstellen können, müssen Sie eine vollständige Sicherung erstellen, z. B. eine Datenbanksicherung oder die erste von mehreren Dateisicherungen. Die Wiederherstellung einer Datenbank, für die nur Dateisicherungen verwendet werden, kann komplex werden. Deshalb wird empfohlen, wenn möglich mit einer vollständigen Datenbanksicherung zu beginnen. Anschließend ist das regelmäßige Sichern des Transaktionsprotokolls erforderlich. Dadurch wird nicht nur die Gefahr von Datenverlusten minimiert, sondern es wird auch die Kürzung des Transaktionsprotokolls ermöglicht. Üblicherweise wird das Transaktionsprotokoll nach jeder konventionellen Protokollsicherung abgeschnitten.

Wichtig

Es wird empfohlen, entsprechend Ihren Geschäftsanforderungen ausreichend häufige Protokollsicherungen auszuführen. Die Häufigkeit sollte sich danach richten, inwiefern Sie Datenverlust (beispielsweise durch einen beschädigten Protokollspeicher) tolerieren können.

Beim Festlegen einer geeigneten Häufigkeit gilt es, einen Kompromiss aus Ihrer Toleranz gegenüber der Gefahr von Datenverlust und Ihrer Fähigkeit zum Speichern, Verwalten und zum möglichen Wiederherstellen von Protokollsicherungen zu finden. Denken Sie bei der Implementierung Ihrer Wiederherstellungsstrategie an die erforderliche Recovery Time Objective (RTO) und Recovery Point Objective (RPO) und insbesondere an den Zeitplan für die Protokollsicherung. Es kann ausreichen, alle 15 bis 30 Minuten eine Protokollsicherung auszuführen. Wenn es für Ihr Geschäft erforderlich ist, die Gefahr des Datenverlusts zu minimieren, können Sie Protokollsicherungen häufiger ausführen. Häufigere Protokollsicherungen bieten zusätzlich den Vorteil, dass das Protokoll häufiger abgeschnitten wird, wodurch kleinere Protokolldateien entstehen.

Um die Anzahl der zum Wiederherstellen benötigten Protokollsicherungen zu begrenzen, ist es wichtig, Daten regelmäßig zu sichern. Beispielsweise können Sie eine wöchentliche vollständige Datenbanksicherung und tägliche differenzielle Datenbanksicherungen planen.

Denken Sie bei der Implementierung Ihrer Wiederherstellungsstrategie an die erforderliche RTO und RPO und insbesondere an den Zeitplan für die vollständige differenzielle Datenbanksicherung.

Informationen zu Transaktionsprotokollsicherungen finden Sie unter Transaktionsprotokollsicherungen (SQL Server).

Die Protokollkette

Eine fortlaufende Abfolge von Protokollsicherungen wird als Protokollkettebezeichnet. Eine Protokollkette beginnt mit einer vollständigen Sicherung der Datenbank. Gewöhnlich wird eine neue Protokollkette nur gestartet, wenn die Datenbank zum ersten Mal gesichert wird oder wenn vom einfachen zum vollständigen oder massenprotokollierten Wiederherstellungsmodell gewechselt wird. Die bestehende Protokollkette bleibt intakt, es sei denn, Sie überschreiben beim Erstellen einer vollständigen Datenbanksicherung bestehende Sicherungssätze. Mit einer intakten Protokollkette können Sie Ihre Datenbank aus einer beliebigen vollständigen Datenbanksicherung im Mediensatz wiederherstellen, gefolgt von allen weiteren Protokollsicherungen bis zum Wiederherstellungspunkt. Der Wiederherstellungspunkt kann das Ende der letzten Protokollsicherung oder ein bestimmter Wiederherstellungspunkt in einer beliebigen Protokollsicherung sein. Weitere Informationen finden Sie unter Transaktionsprotokollsicherungen (SQL Server).

Um eine Datenbank bis zu dem Punkt, an dem ein Fehler aufgetreten ist, wiederherzustellen, muss die Protokollkette intakt sein. Das heißt, eine ununterbrochene Sequenz von Transaktionsprotokollsicherungen muss sich bis zum Zeitpunkt des Fehlers erstrecken. Wo diese Protokollsequenz anfangen muss, richtet sich nach dem Typ der Datensicherungen, die Sie wiederherstellen: Datenbank-, Teil- oder Dateisicherung. Bei einer Datenbank- oder Teilsicherung muss die Sequenz der Protokollsicherungen am Ende einer Datenbank- oder Teilsicherung beginnen. Bei einer Gruppe von Dateisicherungen muss die Sequenz der Protokollsicherungen mit dem Anfang einer vollständigen Gruppe von Dateisicherungen beginnen. Weitere Informationen finden Sie unter Anwenden von Transaktionsprotokollsicherungen (SQL Server).

Wiederherstellen von Protokollsicherungen

Beim Wiederherstellen einer Protokollsicherung wird ein Rollforward für die im Transaktionsprotokoll aufgezeichneten Änderungen ausgeführt, um den genauen Zustand der Datenbank zu dem Zeitpunkt, als der Protokollsicherungsvorgang gestartet wurde, wiederherzustellen. Wenn Sie eine Datenbank wiederherstellen, müssen Sie die Protokollsicherungen wiederherstellen, die nach der vollständigen Datenbanksicherung erstellt wurden, die Sie wiederherstellen, oder die Protokollsicherungen ab dem Start der ersten Dateisicherung, die Sie wiederherstellen. Nach dem Wiederherstellen der aktuellsten Daten oder der aktuellsten differenziellen Sicherung müssen Sie normalerweise eine Reihe von Protokollsicherungen wiederherstellen, bis Sie den Wiederherstellungspunkt erreichen. Dann stellen Sie die Datenbank wieder her. Dabei wird ein Rollback aller Transaktionen ausgeführt, die beim Start der Wiederherstellung unvollständig waren, und die Datenbank wird online geschaltet. Nach der Wiederherstellung der Datenbank können keine weiteren Sicherungen wiederhergestellt werden. Weitere Informationen finden Sie unter Anwenden von Transaktionsprotokollsicherungen (SQL Server).

Prüfpunkte und der aktive Teil des Protokolls

Prüfpunkte leeren modifizierte Datenseiten aus dem Puffercache der aktuellen Datenbank auf dem Datenträger. Auf diese Weise wird der aktive Teil des Protokolls minimiert, der im Rahmen einer vollständigen Wiederherstellung einer Datenbank verarbeitet werden muss. Während einer vollständigen Wiederherstellung werden die folgenden Arten von Aktionen ausgeführt:

• Für die Protokolldatensätze zu den Änderungen, die vor dem Systemausfall nicht auf den Datenträger geleert wurden, wird ein Rollforward durchgeführt.
• Für alle Änderungen, die mit unvollständigen Transaktionen verbunden sind, z. B. Transaktionen, für die kein COMMIT- oder ROLLBACK-Protokolldatensatz vorliegt, wird ein Rollback ausgeführt.

Prüfpunktvorgang

Ein Prüfpunkt führt die folgenden Vorgänge in der Datenbank aus:

• Schreiben eines Datensatzes in die Protokolldatei, mit dem der Beginn des Prüfpunktes markiert wird.

• Speichern der aufgezeichneten Informationen für den Prüfpunkt in einer Kette von Prüfpunkt-Protokolldatensätzen.

  Ein Teil der im Prüfpunkt aufgezeichneten Informationen besteht aus der LSN (Log Sequence Number oder Protokollfolgenummer) des ersten Protokolldatensatzes, der für eine erfolgreiche Durchführung eines datenbankweiten Rollbacks vorhanden sein muss. Diese LSN wird als Mindestwiederherstellungs-LSN (MinLSN) bezeichnet. Die MinLSN gibt den Mindestwert für Folgendes an:

  • LSN des Beginns des Prüfpunktes.
  • LSN des Beginns der ältesten aktiven Transaktion.
  • LSN des Beginns der ältesten Replikationstransaktion, die noch nicht an die Verteilungsdatenbank übermittelt wurde.

  Die Prüfpunktdatensätze enthalten auch eine Liste aller aktiven Transaktionen, die die Datenbank geändert haben.

• Markieren des Speicherplatzes vor der MinLSN für die Wiederverwendung, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet.

• Schreiben aller modifizierten Protokoll- und Datenseiten auf den Datenträger.

• Schreiben eines Datensatzes in die Protokolldatei, mit dem das Ende des Prüfpunktes markiert wird.

• Schreiben der LSN des Anfangs dieser Kette auf die Datenbank-Startseite.

Aktivitäten, die einen Prüfpunkt auslösen

Prüfpunkte treten in den folgenden Situationen auf:

• Eine CHECKPOINT-Anweisung wird explizit ausgeführt. Ein Prüfpunkt tritt in der aktuellen Datenbank für die Verbindung auf.
• Ein minimal protokollierter Vorgang wird in der Datenbank ausgeführt, z. B. wird ein Massenkopiervorgang mit einer Datenbank ausgeführt, die das massenprotokollierte Wiederherstellungsmodell verwendet.
• Datenbankdateien wurden mit ALTER DATABASE hinzugefügt oder entfernt.
• Eine SQL Server-Instanz wurde durch eine SHUTDOWN-Anweisung oder Beenden des SQL Server-Dienstes (MSSQLSERVER) beendet. Durch jede der Aktionen wird ein Prüfpunkt in jeder Datenbank der SQL Server-Instanz ausgelöst.
• Eine SQL Server-Instanz erzeugt regelmäßig automatische Prüfpunkte in jeder Datenbank, um die Zeitspanne zu verkürzen, die die Instanz zum Wiederherstellen der Datenbank benötigen würde.
• Eine vollständige Datenbanksicherung wird ausgeführt.
• Eine Aktivität wird ausgeführt, für die das Herunterfahren einer Datenbank erforderlich ist. Da kann passieren, wenn die Option AUTO_CLOSE aktiviert ist und die letzte Benutzerverbindung mit der Datenbank geschlossen wird. Ein weiteres Beispiel ist, wenn eine Datenbankoption geändert wird, für die ein Neustart der Datenbank erforderlich ist.

Automatische Prüfpunkte

Die SQL Server-Datenbank-Engine generiert automatische Prüfpunkte. Das Intervall zwischen automatischen Prüfpunkten wird anhand des belegten Speicherplatzes des Protokolls und der seit dem letzten Prüfpunkt verstrichenen Zeitspanne festgelegt. Werden nur wenige Änderungen in der Datenbank vorgenommen, kann das Zeitintervall zwischen den automatischen Prüfpunkten sehr unterschiedlich bzw. lang sein. Wenn eine Vielzahl von Daten geändert werden, können automatische Prüfpunkte ebenfalls häufig auftreten.

Verwenden Sie die Serverkonfigurationsoption Wiederherstellungsintervall , um das Intervall zwischen den automatischen Prüfpunkten aller Datenbanken in einer Serverinstanz zu berechnen. Durch diese Option wird angegeben, wie viel Zeit die Datenbank-Engine höchstens benötigen sollte, um eine Datenbank während des Systemstarts wiederherzustellen. Die Datenbank-Engine schätzt, wie viele Protokolldatensätze während einer Datenbankwiederherstellung in dem Wiederherstellungsintervall verarbeitet werden können.

Das Intervall zwischen automatischen Prüfpunkten hängt außerdem vom Wiederherstellungsmodell ab:

• Wenn die Datenbank entweder das vollständige oder das massenprotokollierte Wiederherstellungsmodell verwendet, wird ein automatischer Prüfpunkt generiert, sobald die Anzahl der Protokolldatensätze die Anzahl an Einträgen erreicht, die laut der Datenbank-Engine in dem Zeitraum verarbeitet werden können, der in der Option „Wiederherstellungsintervall“ angegeben ist.

• Wenn die Datenbank das einfache Wiederherstellungsmodell verwendet, wird ein automatischer Prüfpunkt erzeugt, sobald die Anzahl der Protokolldatensätze dem jeweils kleineren der beiden folgenden Werte entspricht:

  • Das Protokoll ist zu 70 % gefüllt.
  • Die Anzahl der tatsächlichen Protokolldatensätze erreicht die von der Datenbank-Engine geschätzte Anzahl an Einträgen, die in dem Zeitraum verarbeitet werden können, der in der Option „Wiederherstellungsintervall“ angegeben ist.

Informationen zum Festlegen des Wiederherstellungsintervalls finden Sie unter Konfigurieren von „Wiederherstellungsintervall (Min.)“ (Serverkonfigurationsoption).

Tipp

Die erweiterte Setupoption -k von SQL Server ermöglicht Datenbankadministrator*innen, das Prüfpunkt-E/A-Verhalten auf Basis des Durchsatzes des E/A-Subsystems für einige Prüfpunkttypen zu drosseln. Die Setupoption „-k“ gilt für automatische Prüfpunkte sowie für andere, nicht gedrosselte Prüfpunkte.

Automatische Prüfpunkte schneiden den ungenutzten Teil des Transaktionsprotokolls ab, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet. Das Protokoll wird jedoch nicht durch automatische Prüfpunkte abgeschnitten, wenn die Datenbank das Modell der vollständigen oder massenprotokollierten Wiederherstellung verwendet. Weitere Informationen finden Sie unter Das Transaktionsprotokoll.

Die CHECKPOINT-Anweisung stellt jetzt ein optionales checkpoint_duration-Argument bereit, das die gewünschte Zeitdauer (in Sekunden) für die zu beendenden Prüfpunkte angibt. Weitere Informationen finden Sie unter CHECKPOINT (Transact-SQL).

Aktives Protokoll

Der Abschnitt der Protokolldatei von der MinLSN bis zu dem zuletzt geschriebenen Protokolldatensatz wird aktiver Teil des Protokolls oder aktives Protokoll genannt. Dies ist der Teil des Protokolls, der für eine vollständige Wiederherstellung der Datenbank erforderlich ist. Vom aktiven Teil des Protokolls kann niemals ein Teil abgeschnitten werden. Alle Protokolldatensätze müssen aus den Teilen des Protokolls abgeschnitten werden, die vor der MinLSN liegen.

Beim folgenden Diagramm handelt es sich um die vereinfachte Version des Endes eines Transaktionsprotokolls mit zwei aktiven Transaktionen. Die Prüfpunkteinträge wurden zu einem einzigen Eintrag zusammengefasst.

LSN 148 ist der letzte Eintrag im Transaktionsprotokoll. Zum Zeitpunkt der Verarbeitung des Prüfpunktes, der bei LSN 147 aufgezeichnet wurde, wurde für Tran 1 ein Commit ausgeführt, und Tran 2 war die einzige aktive Transaktion. Hierdurch wird der erste Protokolldatensatz für Tran 2 zum ältesten Protokolleintrag für eine Transaktion, die zum Zeitpunkt des letzten Prüfpunktes aktiviert war. LSN 142, der Eintrag für den Transaktionsbeginn von Tran 2, wird somit zur MinLSN.

Lang andauernde Transaktionen

Das aktive Protokoll muss jeden Teil aller Transaktionen umfassen, für die noch kein Commit ausgeführt wurde. Eine Anwendung, die eine Transaktion startet und für diese Transaktion keinen Commit oder Rollback ausführt, verhindert, dass die Datenbank-Engine die MinLSN heraufsetzt. Diese Situation kann zu zwei Arten von Problemen führen:

• Wenn das System heruntergefahren wird, nachdem die Transaktion zahlreiche Änderungen vorgenommen hat, für die kein Commit ausgeführt wurde, kann die Wiederherstellungsphase beim nachfolgenden Neustart erheblich länger dauern, als durch die Option Wiederherstellungsintervall festgelegt wurde.
• Das Protokoll kann sehr umfangreich werden, da das Protokoll nicht hinter der MinLSN abgeschnitten werden kann. Dies tritt auch dann auf, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet, bei dem das Transaktionsprotokoll bei jedem automatischen Prüfpunkt abgeschnitten wird.

Die Wiederherstellung langer Transaktionen und die in diesem Artikel beschriebenen Probleme können mithilfe der beschleunigten Datenbankwiederherstellung vermieden werden, einem Feature, das ab SQL Server 2019 (15.x) und in der Azure SQL-Datenbank verfügbar ist.

Replikationstransaktionen

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. Das aktive Protokoll muss alle Transaktionen enthalten, die für die Replikation markiert wurden, die jedoch noch nicht an die Verteilungsdatenbank übermittelt wurden. Wenn diese Transaktionen nicht rechtzeitig repliziert werden, können sie die Kürzung des Protokolls verhindern. Weitere Informationen finden Sie unter Transaktionsreplikation.

Zugehöriger Inhalt

• Das Transaktionsprotokoll
• Verwalten der Größe der Transaktionsprotokolldatei
• Transaktionsprotokollsicherungen (SQL Server)
• Datenbankprüfpunkte (SQL Server)
• Konfigurieren von „Wiederherstellungsintervall (Min.)“ (Serverkonfigurationsoption)
• Verbesserte Wiederherstellung von Datenbanken
• sys.dm_db_log_info (Transact-SQL)
• sys.dm_db_log_space_usage (Transact-SQL)
• Erläuterungen zu Protokollierung und Wiederherstellung in SQL Server von Paul Randal
• Verwaltung von SQL Server-Transaktionsprotokollen von Tony Davis und Gail Shaw