Handbuch zur Architektur und Verwaltung von Transaktionsprotokollen in SQL Server

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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:

• Logische Operation protokolliert

  • Um ein Rollforward der logischen Operation durchzuführen, wird die Operation erneut ausgeführt.
  • Um die logische Operation rückgängig zu machen, wird die umgekehrte logische Operation durchgeführt.

• Vorher-/Nachher-Bild protokolliert

  • Um den Vorgang vorwärts auszuführen, wird das After-Image angewendet.
  • Um den Vorgang rückgängig zu machen, wird das Vorabbild angewendet.

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.

Der Abschnitt der Protokolldatei aus dem ersten Protokolldatensatz, der für ein erfolgreiches datenbankweites Rollback für den zuletzt geschriebenen Protokolldatensatz vorhanden sein muss, wird als aktiver Teil des Protokolls, des aktiven Protokolls oder des Hinterteils des Protokolls bezeichnet. Dies ist der Teil des Protokolls, der für eine vollständige Wiederherstellung der Datenbank erforderlich ist. Kein Teil des aktiven Protokolls kann jemals gekürzt 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.

Differenzsicherungen und Protokollsicherungen stellen die Datenbank bis zu einem späteren Zeitpunkt wieder her, der 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. Dies kann den Start der Datenbank verlangsamen, Transaktionsprotokollsicherungen sowie Wiederherstellungsvorgänge beeinträchtigen und zu Latenz bei der Transaktionsreplikation/CDC sowie bei Always On-Redo 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.

Wir empfehlen, Ihre Protokolldateien nahe an der letztlich erforderlichen Größe zu erstellen, dabei die Inkremente zu verwenden, die zum Erreichen einer optimalen VLF-Verteilung erforderlich sind, und einen relativ großen growth_increment-Wert 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 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 FILEGROWTH und SIZE Argumenten von ALTER DATABASE, siehe ALTER DATABASE (Transact-SQL) Datei- und Dateigruppenoptionen.

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 an VLFs aufweisen, wenn das Transaktionsprotokoll häufig automatisch oder manuell in kleinen Schritten vergrößert wird. 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.
• Bei der Transaktionsreplikation oder beim Change Data Capture können erhebliche Verzögerungen auftreten.

Wenn Sie das SQL Server-Fehlerprotokoll untersuchen, stellen Sie möglicherweise fest, dass vor der Analysephase des Prozesses zur Datenbankwiederherstellung erheblicher Zeitaufwand bestand. 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>);

   Note

   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 hat eine variable Größe und ist immer ein ganzzahliges Vielfaches von 512 Byte (der von SQL Server unterstützten Mindestsektorgröße), bei 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 Blockoffset, der auf einen Bereich hinter den ersten 8 KB der VLF zeigt.

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 Ringpufferdatei. 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. Die Protokollkürzung gibt alle virtuellen Protokolle frei, deren Datensätze alle vor der minimalen Wiederherstellungs-Protokollsequenznummer (MinLSN) liegen. 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, springen die neuen Protokolldatensätze an den Anfang der physischen Protokolldatei zurück.

Dieser Zyklus wiederholt sich endlos, solange das Ende des logischen Protokolls nie den Anfang des logischen Protokolls erreicht. Wenn die alten Protokolldatensätze häufig genug gekürzt werden, sodass immer genügend Platz für alle neuen Protokolldatensätze verbleibt, die bis zum nächsten Checkpoint erstellt werden, wird das Protokoll nie voll. 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 (Transact-SQL) Datei- und Dateigruppenoptionen.

• 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 zum Anfang der ersten physischen Protokolldatei zurückkehrt.

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. Ein Prüfpunkt schreibt die aktuell im Arbeitsspeicher geänderten Seiten (sogenannte Dirty Pages) und Informationen aus dem Transaktionsprotokoll aus dem Arbeitsspeicher auf den Datenträger. Beim Ausführen des Prüfpunkts wird der inaktive Teil des Transaktionsprotokolls als wiederverwendbar markiert. Danach kann eine Protokollkürzung den inaktiven Teil freigeben. Weitere Informationen zu Prüfpunkten finden Sie unter Datenbankprüfpunkte (SQL Server).

Die folgenden Diagramme zeigen ein Transaktionsprotokoll vor und nach der Trunkierung. 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 gekürzt werden. Das virtuelle Protokoll 5 wurde noch nicht verwendet und ist nicht Teil des aktuellen logischen Protokolls.

Das zweite Diagramm zeigt, wie das Protokoll nach dem Kürzen aussieht. 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 nach den folgenden Ereignissen automatisch, es sei denn, sie wird aus irgendeinem Grund verzögert:

• 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-Logging-Algorithmus (WAL), der sicherstellt, dass keine Datenänderungen auf den Datenträger geschrieben werden, bevor der zugehörige Protokolldatensatz auf den Datenträger geschrieben wird. 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. Wenn eine Seite im Puffercache geändert wird, wird sie nicht sofort auf den Datenträger zurückgeschrieben; stattdessen wird die Seite als dirty 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. Der Prüfpunktprozess durchsucht den Puffercache regelmäßig nach Puffern mit Seiten aus einer angegebenen Datenbank und schreibt alle geänderten Seiten auf den Datenträger. Prüfpunkte sparen bei einer späteren Wiederherstellung Zeit, indem ein Zeitpunkt geschaffen wird, zu dem garantiert alle modifizierten Seiten auf den Datenträger geschrieben wurden.

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, solange jede vollständige Sicherung läuft. 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 herkömmlichen Protokollsicherung gekürzt.

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.

Berücksichtigen Sie bei der Implementierung Ihrer Wiederherstellungsstrategie die erforderliche RTO und RPO, insbesondere die Häufigkeit vollständiger und differenzieller Datenbanksicherungen.

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

Sicherungshäufigkeit und Geschäftliche Anforderungen

Sie sollten häufig genug Protokollsicherungen verwenden, um Ihre geschäftlichen Anforderungen zu unterstützen, insbesondere Ihre Toleranz für Arbeitsverluste, z. B. durch einen beschädigten Protokollspeicher.

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. Berücksichtigen Sie bei der Implementierung Ihrer Wiederherstellungsstrategie die erforderliche Recovery Time Objective (RTO) und Recovery Point Objective (RPO) sowie insbesondere die Häufigkeit der Protokollsicherungen.

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.

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. Für einen Satz von Dateisicherungen muss sich die Folge der Protokollsicherungen vom Beginn eines vollständigen Satzes von Dateisicherungen an erstrecken. 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:

• Die Protokolleinträge der Änderungen, die vor dem Stopp des Systems nicht auf den Datenträger geschrieben wurden, werden per Rollforward wiederhergestellt.
• Alle Änderungen, die mit unvollständigen Transaktionen verbunden sind, z. B. Transaktionen, für die es keinen Protokolldatensatz COMMIT oder ROLLBACK gibt, werden rückgängig gemacht.

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.

• Wenn die Datenbank das einfache Wiederherstellungsmodell verwendet, wird der Bereich vor der MinLSN zur Wiederverwendung markiert.

• Schreibt alle modifizierten Protokoll- und Datenseiten auf den Datenträger.

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

• Schreibt die LSN des Beginns dieser Kette in die Bootseite der Datenbank.

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 mithilfe von ALTER DATABASE hinzugefügt oder entfernt.

• Eine Instanz von SQL Server wird durch eine SHUTDOWN Anweisung oder durch Beenden des SQL Server (MSSQLSERVER)-Diensts 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 Datenbanksicherung wird erstellt.

• Eine Aktivität wird ausgeführt, für die das Herunterfahren einer Datenbank erforderlich ist. Dies kann passieren, wenn die AUTO_CLOSE Option ist ON 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 Serverkonfiguration: Wiederherstellungsintervall (min).

Automatische Prüfpunkte schneiden den ungenutzten Teil des Transaktionsprotokolls ab, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet. Wenn die Datenbank jedoch das vollständige Wiederherstellungsmodell oder eines der massiv protokollierten Wiederherstellungsmodelle verwendet, wird das Protokoll durch automatische Prüfpunkte nicht gekürzt. Weitere Informationen finden Sie unter Das Transaktionsprotokoll.

Die CHECKPOINT Anweisung stellt jetzt ein optionales checkpoint_duration-Argument bereit, das den angeforderten Zeitraum in Sekunden angibt, damit Prüfpunkte abgeschlossen werden. 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. Kein Teil des aktiven Protokolls kann jemals gekürzt 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. Als der bei LSN 147 aufgezeichnete Checkpoint verarbeitet wurde, war Tran 1 bereits festgeschrieben, 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. Damit wird LSN 142, der Begin-Transaktionsdatensatz für Tran 2, 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 weder ein Commit noch ein Rollback dafür ausführt, hindert das Datenbankmodul daran, die MinLSN weiterzuschieben. Diese Situation kann zu zwei Arten von Problemen führen:

• Wenn das System heruntergefahren wird, nachdem viele nicht festgeschriebene Änderungen durchgeführt wurden, kann die Wiederherstellungsphase beim darauffolgenden Neustart erheblich länger dauern als in der Option Wiederherstellungsintervall angegeben.
• 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.

Verwandte Inhalte

• Das Transaktionsprotokoll
• Verwalten der Größe der Transaktionsprotokolldatei
• Transaktionsprotokollsicherungen (SQL Server)
• Datenbankprüfpunkte (SQL Server)
• Serverkonfiguration: Wiederherstellungsintervall (min)
• Verbesserte Wiederherstellung von Datenbanken
• sys.dm_db_log_info (Transact-SQL)
• sys.dm_db_log_space_usage (Transact-SQL)
• Grundlegendes zur Protokollierung und Wiederherstellung in SQL Server