SQL Server-Transaktionsprotokollarchitektur und Verwaltungshandbuch

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Jede SQL Server-Datenbank verfügt über ein Transaktionsprotokoll, in dem alle Transaktionen und die Datenbankänderungen aufgezeichnet werden, die von jeder Transaktion vorgenommen werden. Das Transaktionsprotokoll ist eine wichtige Komponente der Datenbank. Wenn ein Systemfehler auftritt, ist das Transaktionsprotokoll möglicherweise erforderlich, um Die Datenbank wieder in einen konsistenten Zustand zu versetzen. 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 funktioniert logisch so, als ob das Transaktionsprotokoll eine Zeichenfolge von Protokolldatensätzen ist. Jeder Protokolldatensatz wird durch eine Protokollsequenznummer (Log Sequence Number , LSN) identifiziert. 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 bei der Erstellung in einer seriellen Sequenz gespeichert, sodass die Änderung, die durch den von LSN2 bezeichneten Protokolldatensatz nach der änderung durch den Protokolldatensatz LSN1 beschrieben wird, nach der änderung durch den Protokolldatensatz LSN1 beschrieben wurde. 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 eines 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 einen LSN: 00000031:00000da0:0001, wobei 0x31 die ID des VLF die 0xda0 Protokollblock-ID ist 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.

Protokolldatensätze für Datenänderungen zeichnen entweder den logischen Vorgang auf, der ausgeführt wurde, oder sie zeichnen die Vor- und Nachher-Bilder der geänderten Daten auf. Das Vorbild ist eine Kopie der Daten, bevor der Vorgang ausgeführt wird. Das Nachbild ist eine Kopie der Daten, nachdem der Vorgang ausgeführt wurde.

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

• Protokollierung der logischen Operation

  • Zum Weiterleiten des logischen Vorgangs wird der Vorgang erneut ausgeführt.
  • Zum Zurücksetzen des logischen Vorgangs wird der umgekehrte logische Vorgang ausgeführt.

• Protokollierung der Anfangs- und Endimages

  • Um den Vorgang vorwärts zu leiten, wird das Nachbild angewendet.
  • Zum Zurücksetzen des Vorgangs wird das Vorbild 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). Änderungen umfassen Änderungen durch vom System gespeicherte Prozeduren oder DDL-Anweisungen (Data Definition Language) an einer beliebigen Tabelle, einschließlich Systemtabellen.

• 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 genügend Protokollspeicher vorhanden ist, um ein Rollback zu unterstützen, das entweder durch eine explizite Rollback-Anweisung verursacht wird oder wenn ein Fehler aufgetreten ist. Die reservierte Speicherplatzmenge hängt von den Vorgängen ab, die in der Transaktion ausgeführt werden, aber im Allgemeinen entspricht sie 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 (SQL Server).

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 Datenbanktransaktionsprotokoll ordnet eine oder mehrere physische Dateien zu. 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 unterteilt jede physische Protokolldatei intern in mehrere virtuelle Protokolldateien (VLFs). Virtuelle Protokolldateien haben keine feste Größe, und es gibt keine feste Anzahl virtueller Protokolldateien für eine physische Protokolldatei. Die Datenbank-Engine wählt die Größe der virtuellen Protokolldateien dynamisch aus, während protokolldateien erstellt oder erweitert werden. Die Datenbank-Engine versucht, einige virtuelle Dateien zu Standard. 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 von Administratoren nicht konfiguriert oder festgelegt werden.

Erstellung virtueller Protokolldateien

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

• Erstellen Sie in SQL Server 2014 (12.x) und höheren Versionen, wenn das nächste Wachstum kleiner als 1/8 der aktuellen physischen Protokollgröße ist, und erstellen Sie dann 1 VLF, das die Wachstumsgröße abdeckt.
• Wenn das nächste Wachstum mehr als 1/8 der aktuellen Protokollgröße ist, verwenden Sie die Methode pre-2014, nämlich:
  • Wenn das Wachstum kleiner als 64 MB ist, erstellen Sie 4 VLFs, die die Wachstumsgröße abdecken (z. B. für 1-MB-Wachstum, erstellen Sie 4 VLFs von Größe 256 KB).
    • In Azure SQL-Datenbank und ab SQL Server 2022 (16.x) (alle Editionen) unterscheidet sich die Logik geringfügig. Wenn das Wachstum kleiner oder gleich 64 MB ist, erstellt die Datenbank-Engine nur ein VLF, um die Wachstumsgröße zu decken.
  • Wenn das Wachstum von 64 MB bis zu 1 GB beträgt, erstellen Sie 8 VLFs, die die Wachstumsgröße abdecken (z. B. für 512-MB-Wachstum, erstellen Sie 8 VLFs mit einer Größe von 64 MB).
  • Wenn das Wachstum größer als 1 GB ist, erstellen Sie 16 VLFs, die die Wachstumsgröße abdecken, z. B. für 8-GB-Wachstum, erstellen Sie 16 VLFs mit einer Größe von 512 MB).

Wenn die Protokolldateien in vielen kleinen Schritten auf eine große Größe wachsen, haben sie viele virtuelle Protokolldateien. Dies kann den Start der Datenbank verlangsamen, Sicherungs- und Wiederherstellungsvorgänge protokollieren und zu Transaktionsreplikation/CDC und Always On-Wiederholungslatenz führen. Wenn die Protokolldateien dagegen mit wenigen oder nur einem Inkrement auf eine große Größe festgelegt sind, enthalten sie nur wenige sehr große virtuelle Protokolldateien. Weitere Informationen zum ordnungsgemäßen Abschätzen der erforderlichen Größe und automatischen Vergrößerung eines Transaktionsprotokolls finden Sie im Abschnitt Empfehlungen Abschnitt "Verwalten der Größe der Transaktionsprotokolldatei".

Es wird empfohlen, Ihre Protokolldateien in der Nähe der erforderlichen endgültigen Größe zu erstellen, indem Sie die erforderlichen Schritte verwenden, um eine optimale VLF-Verteilung zu erzielen und einen relativ großen growth_increment Wert aufweisen.

In den folgenden Tipps können Sie die optimale VLF-Verteilung für die aktuelle Transaktionsprotokollgröße ermitteln:

• Der Durch das SIZE Argument ALTER DATABASE festgelegte Größenwert ist die Anfangsgröße für die Protokolldatei.
• Der growth_increment Wert (auch bekannt als AutoGrow-Wert), der das FILEGROWTH Argument von ALTER DATABASE Sätzen ist, ist der Speicherplatz, der der Datei bei jeder Anforderung neuer Speicherplatz hinzugefügt wird.

Weitere Informationen und FILEGROWTHSIZE Argumente von ALTER DATABASE, finden Sie unter ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Was geschieht, wenn Sie zu viele VLFs haben?

In den ersten Phasen eines Datenbankwiederherstellungsprozesses ermittelt SQL Server alle VLFs in allen Transaktionsprotokolldateien und erstellt eine Liste dieser VLFs. Dieser Vorgang kann je nach Anzahl der in der jeweiligen Datenbank vorhandenen VLFs sehr lange dauern. Je mehr VLFs, desto länger der Prozess. Eine Datenbank kann mit einer großen Anzahl von VLFs enden, wenn häufiges automatisches Transaktionsprotokoll oder manuelles Wachstum in kleinen Schritten auftritt. Wenn die Anzahl der VLFs den Bereich von mehreren hunderttausend erreicht, können einige oder die meisten der folgenden Symptome auftreten:

• Mindestens eine Datenbank dauert sehr lange, bis die Wiederherstellung während des SQL Server-Starts abgeschlossen ist.
• Das Wiederherstellen einer Datenbank dauert sehr lange, bis sie abgeschlossen ist.
• Versuche, eine Datenbank anzufügen, dauern sehr lange.
• Wenn Sie versuchen, datenbank-Spiegel ing einzurichten, treten Fehlermeldungen 1413, 1443 und 1479 auf, die ein Timeout angeben.
• Beim Versuch, eine Datenbank wie 701 wiederherzustellen, treten speicherbezogene Fehler auf.
• Bei der Transaktionsreplikation oder der Datenerfassung kann es zu erheblicher Latenz führen.

Wenn Sie das SQL Server-Fehlerprotokoll untersuchen, stellen Sie möglicherweise fest, dass vor der Analysephase des Datenbankwiederherstellungsvorgangs eine erhebliche Zeit aufgewendet wird. 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 protokolliert SQL Server möglicherweise einen 9017-Fehler, wenn Sie eine Datenbank mit einer großen Anzahl von 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 von VLFs

Um die Gesamtanzahl der VLFs mit einem angemessenen Betrag zu halten, z. B. maximal mehrere Tausend, können Sie die Transaktionsprotokolldatei zurücksetzen, um eine kleinere Anzahl von VLFs zu enthalten, indem Sie die folgenden Schritte ausführen:

1. Verkleinern Sie die Transaktionsprotokolldateien manuell.

2. Vergrößern Sie die Dateien manuell auf die erforderliche Größe 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 Datenbankeigenschaftenseite möglich.

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

Bevor Sie eine dieser Vorgänge ausführen, stellen Sie sicher, dass Sie über eine gültige wiederherstellbare Sicherung verfügen, falls später Probleme auftreten.

Um die optimale VLF-Verteilung für die aktuelle Transaktionsprotokollgröße aller Datenbanken in einer bestimmten Instanz und die erforderlichen Wachstumsschritte zu ermitteln, um die erforderliche Größe zu erreichen, können Sie das folgende GitHub-Skript verwenden, um VLFs zu beheben.

Protokollblöcke

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

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

Jeder Protokollblock innerhalb eines VLF wird durch seinen Blockoffset eindeutig adressiert. Der erste Block hat immer einen Blockversatz, der über die ersten 8 KB im VLF verweist.

Im Allgemeinen wird ein VLF immer mit Protokollblöcken gefüllt. Es ist möglich, dass der letzte Protokollblock in einem VLF leer ist (z. B. keine Protokolldatensätze). Dies geschieht, wenn ein zu schreibener Protokolldatensatz nicht in den aktuellen Protokollblock passt und auch, wenn der auf dem VLF übrige Speicherplatz nicht ausreicht, um diesen Protokolldatensatz zu speichern. In diesem Fall wird ein leerer Protokollblock erstellt, der das VLF ausfüllt. Der Protokolldatensatz wird in den ersten Block des 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 in der folgenden Abbildung.

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 ist und Speicherplatz auf dem Datenträger verfügbar ist, wird die Datei um den im parameter growth_increment angegebenen Betrag erweitert, und die neuen Protokolldatensätze werden der Erweiterung hinzugefügt. Weitere Informationen zur FILEGROWTH Einstellung finden Sie unter ALTER DATABASE File and Filegroup Options (Transact-SQL).

• Wenn die FILEGROWTH Einstellung nicht aktiviert ist oder der Datenträger, der die Protokolldatei enthält, weniger freien Speicherplatz als der in growth_increment angegebene Betrag aufweist, wird ein Fehler von 9002 generiert. Weitere Informationen finden Sie unter Problembehandlung bei vollen Transaktionsprotokollen.

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.

Protokollabkürzung

Die Protokollkürzung ist wichtig, um ein Auffüllen des Protokolls verhindern zu können. Durch das Abschneiden von Protokollen werden inaktive virtuelle Protokolldateien aus dem logischen Transaktionsprotokoll einer SQL Server-Datenbank gelöscht, wobei Speicherplatz im logischen Protokoll für die Wiederverwendung durch das physische Transaktionsprotokoll freigegeben wird. Wenn ein Transaktionsprotokoll nie abgeschnitten wird, füllt es schließlich den gesamten Speicherplatz, der seinen physischen Protokolldateien zugeordnet ist. Bevor das Protokoll jedoch gekürzt werden kann, ist ein Prüfpunktvorgang erforderlich. Ein Prüfpunkt schreibt die aktuellen geänderten Seiten im Arbeitsspeicher (auch als modifiziert Seiten bezeichnet) und Transaktionsprotokollinformationen vom Speicher auf den Datenträger. Beim Ausführen des Prüfpunkts wird der inaktive Teil des Transaktionsprotokolls als wiederverwendbar markiert. Danach kann ein Protokollabkürzung den inaktiven Teil freigeben. Weitere Informationen zu Prüfpunkten finden Sie unter Datenbankprüfpunkte (SQL Server).

Die folgenden Abbildungen zeigen ein Transaktionsprotokoll vor und nach dem Abschneiden. In der ersten Abbildung 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 ist noch nicht verwendet und ist nicht Teil des aktuellen logischen Protokolls.

Die zweite Abbildung 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 ist 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.

Das Abschneiden von Protokollen 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 das Abschneiden von Protokollen verzögern und eine Problembehandlung für ein vollständiges Transaktionsprotokoll (SQL Server-Fehler 9002) beheben können.

Transaktionsprotokoll mit Schreibzugriff

In diesem Abschnitt wird die Aufgabe des Write-Ahead-Transaktionsprotokolls beim Aufzeichnen von Datenänderungen auf dem Datenträger beschrieben. SQL Server verwendet einen WAL-Algorithmus (Write-Ahead Logging), der garantiert, dass keine Datenänderungen auf den Datenträger geschrieben werden, bevor der zugeordnete Protokolldatensatz auf den Datenträger geschrieben wird. Dies schützt die ACID-Eigenschaften einer Transaktion.

Um zu verstehen, wie das Schreib-Ahead-Protokoll funktioniert, ist es wichtig, dass Sie wissen, wie geänderte Daten auf den Datenträger geschrieben werden. SQL Server Standard enthält einen Puffercache (auch als Pufferpool bezeichnet), in den Datenseiten beim Abrufen von Daten gelesen 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 modifiziert markiert. Eine Datenseite kann mehrere logische Schreibvorgänge aufweisen, 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. SQL Server verfügt über Eine Logik, die verhindert, dass eine modifiziert Seite geleert wird, bevor der zugeordnete Protokolldatensatz geschrieben wird. 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.

Um die Anzahl der Protokollsicherungen zu begrenzen, die Sie wiederherstellen müssen, ist es wichtig, Ihre Daten routinemäßig zu sichern. Beispielsweise können Sie eine wöchentliche vollständige Datenbanksicherung und tägliche differenzielle Datenbanksicherungen planen.

Überlegen Sie bei der Implementierung Ihrer Wiederherstellungsstrategie und insbesondere des vollständigen und differenziellen Datenbanksicherungsrhythmen die erforderliche RTO und RPO .

Weitere 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. In der Regel wird eine neue Protokollkette nur gestartet, wenn die Datenbank zum ersten Mal gesichert wird oder nachdem das Wiederherstellungsmodell von der einfachen Wiederherstellung auf die vollständige oder massenprotokollierte Wiederherstellung gewechselt wurde. 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 Abfolge des Protokolls gestartet werden muss, hängt vom Typ der Datensicherungen ab, die Sie wiederherstellen: Datenbank, Teil oder Datei. 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

Durch das Wiederherstellen einer Protokollsicherung werden die Im Transaktionsprotokoll aufgezeichneten Änderungen weitergeleitet, um den genauen Zustand der Datenbank zu dem Zeitpunkt, zu dem der Protokollsicherungsvorgang gestartet wurde, neu zu erstellen. Wenn Sie eine Datenbank wiederherstellen, müssen Sie die Protokollsicherungen wiederherstellen, die nach der vollständigen Datenbanksicherung erstellt wurden, die Sie wiederherstellen, oder von Beginn 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. Nachdem die Datenbank wiederhergestellt wurde, können Sie keine weiteren Sicherungen wiederherstellen. 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.
• Alle Änderungen, die unvollständigen Transaktionen zugeordnet sind, z. B. Transaktionen, für die kein COMMIT- oder ROLLBACK-Protokolldatensatz vorhanden ist, werden zurückgesetzt.

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 Starts 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 verursachen

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. Dies kann passieren, wenn die Option AUTO_CLOSE aktiviert ist und die letzte Benutzerverbindung mit der Datenbank geschlossen wird. Ein weiteres Beispiel ist, wenn eine Datenbankoptionsänderung vorgenommen 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 der Serverkonfigurationsoption „Wiederherstellungsintervall“.

Automatische Prüfpunkte schneiden den ungenutzten Teil des Transaktionsprotokolls ab, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet. Wenn die Datenbank jedoch die vollständigen oder massenprotokollierten Wiederherstellungsmodelle verwendet, wird das Protokoll nicht von automatischen Prüfpunkten abgeschnitten. Weitere Informationen finden Sie unter Das Transaktionsprotokoll (SQL Server).

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.

Bei der folgenden Abbildung 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 nicht committ oder zurückrollt, verhindert, dass der Datenbank-Engine den MinLSN weiterschreitet. Diese Situation kann zwei Arten von Problemen verursachen:

• 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 groß werden, da das Protokoll nicht über den MinLSN abgeschnitten werden kann. Dies tritt auch dann auf, wenn die Datenbank das einfache Wiederherstellungsmodell verwendet, in dem das Transaktionsprotokoll für jeden 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 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 gekennzeichnet sind, aber noch nicht an die Verteilungsdatenbank übermittelt wurden. Wenn diese Transaktionen nicht rechtzeitig repliziert werden, können sie das Abschneiden des Protokolls verhindern. Weitere Informationen finden Sie unter Transaktionsreplikation.

Nächste Schritte

In den folgenden empfohlenen Artikeln und Büchern finden Sie zusätzliche Informationen zu Transaktionsprotokollen und bewährten Methoden für die Protokollverwaltung.

• Das Transaktionsprotokoll [SQL Server]
• Verwalten der Größe der Transaktionsprotokolldatei
• Transaktionsprotokollsicherungen (SQL Server)
• Datenbankprüfpunkte (SQL Server)
• Konfigurieren des Wiederherstellungsintervalls (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