SQL Server

Erläuterungen zu Protokollierung und Wiederherstellung in SQL Server

Paul S. Randal

Auf einen Blick:

• Funktionsweise der Protokollierung und Wiederherstellung in SQL Server
• Funktionsweise des Transaktionsprotokolls und Hinweise zu seiner Verwaltung
• Wiederherstellungsmodelle und ihre Auswirkungen auf die Protokollierung

Inhalt

Was ist Protokollierung?
Was ist Wiederherstellung?
Das Transaktionsprotokoll
Wiederherstellungsmodelle
Zusammenfassung

Häufig missverstandene Aspekte von SQL Server sind seine Methoden zur Protokollierung und Wiederherstellung. Die Tatsache, dass das Transaktionsprotokoll existiert und bei fehlerhafter Verwaltung Probleme verursachen kann, scheint viele „unfreiwillige DBAs“ zu verwirren. Warum kann das Transaktionsprotokoll uneingeschränkt wachsen? Warum dauert es manchmal nach einem Systemabsturz so lange, bis die Datenbank wird online ist? Warum kann die Protokollierung nicht vollständig ausgeschaltet werden? Warum lassen sich Datenbanken nicht richtig wiederherstellen? Was genau ist das Transaktionsprotokoll, und wozu dient es?

Alle diese Fragen tauchen immer wieder in SQL Server-Foren und -Newsgroups auf. Daher gibt dieser Artikel einen Überblick über das Protokollierungs- und Wiederherstellungssystem und erklärt, warum es ein wesentlicher Bestandteil des SQL Server-Speichermoduls ist. Neben der Architektur des Transaktionsprotokolls wird erläutert, wie die drei für eine Datenbank verfügbaren Wiederherstellungsmodelle das Verhalten des Transaktionsprotokolls und des Protokollierungsprozesses selbst ändern können. Darüber hinaus finden Sie einige Links zu Ressourcen, die Informationen über bewährte Methoden der Transaktionsprotokollverwaltung bieten.

Was ist Protokollierung?

Die Konzepte der Protokollierung und Wiederherstellung gelten nicht nur für SQL Server – sie sind für alle kommerziellen Managementsysteme für relationale Datenbanken (relational database management system, RDBMS) erforderlich, um die Unterstützung der verschiedenen ACID-Eigenschaften von Transaktionen zu ermöglichen. ACID steht für Atomicity, Consistency, Isolation und Durability (Atomarität, Konsistenz, Isolation und Dauerhaftigkeit). Dies sind die grundlegenden Eigenschaften eines Transaktionsverarbeitungssystems (z. B. eines RDBMS). Weitere Informationen hierzu finden Sie im Abschnitt ACID-Eigenschaften in der MSDN-Bibliothek.

Vorgänge in einem RDBMS werden auf physischer und logischer Ebene im Hinblick darauf protokolliert (oder aufgezeichnet), was in den Speicherstrukturen der Datenbank geschieht. Für jede Änderung der Speicherstrukturen gibt es einen eigenen Protokolldatensatz, der die geänderte Struktur und die Änderung selbst beschreibt. Dies geschieht so, dass die Änderung falls notwendig wiedergegeben oder rückgängig gemacht werden kann. Die Protokolldatensätze werden in einer besonderen Datei, dem Transaktionsprotokoll, gespeichert. Diese wird an späterer Stelle ausführlicher beschrieben. Vorläufig können Sie sich die Datei als Datei mit sequenziellem Zugriff vorstellen.

Ein Satz aus einer oder mehreren solcher Änderungen kann (und dies geschieht tatsächlich immer) in einer Transaktion zusammengefasst werden. So entsteht die Basiseinheit für die Ausführung von Änderungen (Atomarität) an einer Datenbank, was Benutzer, Anwendungsentwickler und DBAs betrifft. Entweder gelingt eine Transaktion (Commit wird ausgeführt) oder misslingt/wird abgebrochen (Rollback wird ausgeführt). Im ersten Fall ist garantiert, dass die Vorgänge, die die Transaktion bilden, in der Datenbank widergespiegelt werden. Im zweiten Fall ist garantiert, dass die Vorgänge nicht in der Datenbank widergespiegelt werden.

Transaktionen in SQL Server sind entweder explizit oder implizit. Bei einer expliziten Transaktion gibt der Benutzer oder die Anwendung eine BEGIN TRANSACTION T-SQL-Anweisung aus und signalisiert damit den Start einer Gruppe verwandter Änderungen durch diese Sitzung. Eine explizite Transaktion gelingt, wenn eine COMMIT TRANSACTION-Anweisung ausgegeben wird, was den erfolgreichen Abschluss der Gruppe der Änderungen signalisiert. Wenn stattdessen eine ROLLBACK TRANSACTION-Anweisung ausgegeben wird, werden alle Änderungen, die seit Ausgabe der BEGIN TRANSACTION-Anweisung von dieser Sitzung vorgenommen wurden, zurückgesetzt (Rollback wird ausgeführt), und die Transaktion wird abgebrochen. Ein Transaktionsrollback könnte auch von einem externen Ereignis erzwungen werden, z. B. wenn die Datenbank nicht mehr über genügend Speicherplatz verfügt oder es zu einem Serverabsturz kommt. Darauf wird später näher eingegangen.

Bei einer impliziten Transaktion gibt der Benutzer oder die Anwendung nicht explizit eine BEGIN TRANSACTION-Anweisung aus, bevor eine T-SQL-Anweisung ausgegeben wird. Da jedoch alle Änderungen an der Datenbank transaktional sein müssen, startet das Speichermodul automatisch eine Transaktion im Hintergrund. Bei Abschluss der T-SQL-Anweisung führt das Speichermodul für die gestartete Transaktion automatisch ein Commit aus, um die Anweisung des Benutzers zu umschließen.

Vielleicht denken Sie, dass dies nicht notwendig ist, da eine einzige T-SQL-Anweisung keine große Zahl von Änderungen an den Speicherstrukturen der Datenbank generiert. Betrachten Sie jedoch den Fall einer ALTER INDEX REBUILD-Anweisung. Obwohl diese Anweisung nicht in einer expliziten Transaktion enthalten sein kann, könnte sie eine enorme Zahl von Änderungen an der Datenbank generieren. Also muss es eine Methode geben, mit der sichergestellt werden kann, dass alle Änderungen korrekt zurückgesetzt werden, sollte etwas schief gehen (z. B. wenn die Anweisung abgebrochen wird).

Betrachten Sie beispielsweise, was geschieht, wenn eine einzige Tabellenzeile in einer impliziten Transaktion aktualisiert wird. Stellen Sie sich eine einfache Heaptabelle mit einer integer-Spalte c1 und einer char-Spalte c2 vor. Die Tabelle umfasst 10.000 Zeilen, und ein Benutzer führt eine Aktualisierungsabfrage wie die folgende aus:

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

Die folgenden Vorgänge finden statt:

• Die Datenseiten aus SimpleTable werden vom Datenträger in den Speicher (den Pufferpool) gelesen, damit sie nach übereinstimmenden Zeilen durchsucht werden können. Wie sich herausstellt, enthalten drei Datenseiten fünf Zeilen, die dem WHERE-Klausel-Prädikat entsprechen.
• Das Speichermodul startet automatisch eine implizite Transaktion.
• Die drei Datenseiten und fünf Datenzeilen werden gesperrt, um die Aktualisierungen zu ermöglichen.
• Die Änderungen werden an den fünf Datensätzen auf den drei Datenseiten im Speicher vorgenommen.
• Außerdem werden die Änderungen in Protokolldatensätzen im Transaktionsprotokoll auf dem Datenträger aufgezeichnet.
• Das Speichermodul führt automatisch ein Commit für die implizite Transaktion aus.

Beachten Sie, dass hier kein Schritt aufgelistet wurde, in dem die drei aktualisierten Datenseiten in den Datenträger zurückgeschrieben werden. Das liegt daran, dass dies noch nicht erforderlich ist. Solange die Protokolldatensätze, die die Änderungen beschreiben, sich auf dem Datenträger im Transaktionsprotokoll befinden, sind die Änderungen geschützt. Wenn die Seiten anschließend gelesen oder wieder geändert werden müssen, befindet sich die aktuelle Kopie der Seite bereits im Speicher, nur (noch) nicht auf dem Datenträger. Die Datenseiten werden wieder auf den Datenträger geschrieben, wenn der nächste Prüfpunktvorgang stattfindet oder wenn der Speicher, der von ihnen im Pufferpool verwendet wird, für ein anderes Seitenabbild erforderlich ist.

Prüfpunkte existieren aus zwei Gründen: um Schreib-E/A-Vorgänge zur Leistungsverbesserung im Stapel zusammenzufassen und um den Zeitaufwand zu verringern, der für die Wiederherstellung nach einem Systemabsturz erforderlich ist. Im Hinblick auf die Leistung kann Folgendes geschehen: Wenn eine Datenseite bei jeder Aktualisierung auf den Datenträger geschrieben werden müsste, könnte die Anzahl der Schreib-E/A-Vorgänge auf einem ausgelasteten System das E/A-Subsystem leicht überlasten. Besser wäre es, modifizierte Seiten (Seiten, die seit dem Lesen vom Datenträger geändert wurden) in regelmäßigen Abständen statt sofort bei jeder Änderung auf den Datenträger zu schreiben. Der Wiederherstellungsaspekt von Prüfpunkten soll in Kürze erläutert werden.

Ein häufiges Missverständnis in Bezug auf Prüfpunkte besteht darin, dass sie nur Seiten mit Änderungen aus Transaktionen, für die ein Commit durchgeführt wurde, auf den Datenträger schreiben. Dies trifft nicht zu. Ein Prüfpunkt schreibt immer alle modifizierten Seiten auf den Datenträger, unabhängig davon, ob für die Transaktion, von der eine Seite geändert wurde, ein Commit durchgeführt wurde oder nicht.

Write-Ahead-Protokollierung ist die Methode, bei der die Protokolldatensätze, die eine Änderung beschreiben, auf den Datenträger geschrieben werden, bevor die Änderungen selbst geschrieben werden. Durch diese Methode wird der Aspekt der Dauerhaftigkeit der ACID-Eigenschaften sichergestellt. Solange sich die Protokolldatensätze, die Änderungen beschreiben, auf dem Datenträger befinden, können die Protokolldatensätze (und folglich die Änderungen selbst) im Falle eines Absturzes wiederhergestellt werden, und die Auswirkungen der Transaktion gehen nicht verloren.

Was ist Wiederherstellung?

Protokollierung ermöglicht die Unterstützung verschiedener Vorgänge in SQL Server. Durch Protokollierung wird sichergestellt, dass bei einem Absturz eine Transaktion, für die ein Commit ausgeführt wurde, nach dem Absturz in der Datenbank korrekt widergespiegelt wird. Durch Protokollierung wird sichergestellt, dass eine Transaktion, für die kein Commit ausgeführt wurde, nach einem Absturz korrekt zurückgesetzt und nicht in der Datenbank widergespiegelt wird. Durch Protokollierung wird gewährleistet, dass es möglich ist, eine nicht abgeschlossene Transaktion abzubrechen und alle ihre Vorgänge zurückzusetzen. Außerdem ist es dank Protokollierung möglich, eine Sicherungskopie des Transaktionsprotokolls anzufertigen, sodass eine Datenbank wiederhergestellt und die Transaktionsprotokollsicherungen wiedergegeben werden können, um die Datenbank mit Transaktionskonsistenz in einen Zustand zu einem bestimmten Zeitpunkt zurückzuversetzen. Schließlich unterstützt die Protokollierung Features, die sich auf das Lesen des Transaktionsprotokolls stützen, z. B. Replikation, Datenbankspiegelung und Änderungsdatenerfassung.

Die meisten Verwendungszwecke der Protokollierung beinhalten eine Methode, die Wiederherstellung genannt wird. Wiederherstellung ist der Prozess des Wiedergebens oder Zurücksetzens der in den Protokolldatensätzen beschriebenen Änderungen in der Datenbank. Das Wiedergeben der Protokolldatensätze wird als REDO-Phase (oder Rollforwardphase) der Wiederherstellung bezeichnet. Das Zurücksetzen der Protokolldatensätze wird als UNDO-Phase (oder Rollbackphase) der Wiederherstellung bezeichnet. Mit anderen Worten: Die Wiederherstellung stellt sicher, dass eine Transaktion und alle einzelnen Protokolldatensätze entweder wiederholt oder rückgängig gemacht werden.

Die einfache Form der Wiederherstellung findet statt, wenn eine einzelne Transaktion abgebrochen wird. In diesem Fall wird sie rückgängig gemacht und hat keine Auswirkungen auf die Datenbank. Die komplexeste Form ist die Wiederherstellung nach einem Systemabsturz, wenn SQL Server abstürzt (aus welchem Grund auch immer) und das Transaktionsprotokoll wiederhergestellt werden muss, um die Datenbank an einen im Hinblick auf die Transaktion konsistenten Punkt zurückzuversetzen. Dies bedeutet, dass für alle Transaktionen, für die zum Zeitpunkt des Absturzes ein Commit ausgeführt worden war, ein Rollforward erforderlich ist, damit ihre Auswirkungen in der Datenbank persistent gespeichert werden. Für alle nicht abgeschlossenen Transaktionen, für die zum Zeitpunkt des Absturzes kein Commit ausgeführt worden war, ist hingegen ein Rollback erforderlich, damit ihre Auswirkungen in der Datenbank nicht persistent gespeichert werden.

Dies liegt daran, dass es keine Funktion gibt, mit der eine Transaktion in SQL Server nach einem Absturz fortgesetzt werden kann. Wenn für die Auswirkungen einer teilweise abgeschlossenen Transaktion kein Rollback ausgeführt würde, verbliebe die Datenbank folglich in einem inkonsistenten Zustand (möglicherweise wäre sogar die Struktur beschädigt, je nachdem, welche Vorgänge gerade von der Transaktion ausgeführt wurden).

Woher weiß aber die Wiederherstellung, welche Schritte erforderlich sind? Alle Wiederherstellungsprozesse hängen von der Tatsache ab, dass jeder Protokolldatensatz mit einer Protokollfolgenummer (Log Sequence Number, LSN) gekennzeichnet wird. Eine Protokollfolgenummer ist eine ständig wachsende, dreiteilige Nummer, die die Position eines Protokolldatensatzes innerhalb des Transaktionsprotokolls eindeutig definiert. Jeder Protokolldatensatz in einer Transaktion wird in fortlaufender Reihenfolge innerhalb des Transaktionsprotokolls gespeichert und enthält die Transaktions-ID und die LSN des früheren Protokolldatensatzes für die Transaktion. Mit anderen Worten: Jeder Vorgang, der im Rahmen der Transaktion aufgezeichnet wird, verfügt über eine „Verknüpfung“ zurück zu dem Vorgang, der ihm unmittelbar vorausgegangen ist.

Für den einfachen Fall des Rollback einer einzelnen Transaktion kann der Wiederherstellungsmechanismus einfach und schnell die Kette protokollierter Vorgänge vom aktuellen Vorgang bis zurück zum ersten Vorgang nachvollziehen und die Auswirkungen der Vorgänge in umgekehrter Reihenfolge rückgängig machen. Die Datenbankseiten, die von der Transaktion betroffen waren, befinden sich entweder noch im Pufferpool oder auf dem Datenträger. In beiden Fällen ist garantiert, dass die Auswirkung der Transaktion im Abbild der verfügbaren Seite auf der Seite widergespiegelt wird und rückgängig gemacht werden muss.

Während der Wiederherstellung nach einem Systemabsturz ist die Methode komplizierter. Da Datenbankseiten nicht auf den Datenträger geschrieben werden, wenn für eine Transaktion ein Commit ausgeführt wird, bedeutet dies, dass es keine Garantie dafür gibt, dass der Satz von Datenbankseiten auf dem Datenträger den Satz der Änderungen, die im Transaktionsprotokoll beschrieben werden, korrekt widerspiegelt (für Transaktionen, für die ein Commit durchgeführt wurde, und für solche, bei denen das nicht der Fall ist). Ein letztes Teil des Puzzles wurde bis jetzt noch nicht erwähnt: Alle Datenbankseiten verfügen über ein Feld in ihrer Kopfzeile (einen 96-Byte-Abschnitt der 8192-Byte-Seite, der Metadaten über die Seite enthält), das die LSN des letzten Protokolldatensatzes mit Auswirkungen auf die Seite enthält. Dies ermöglicht dem Wiederherstellungssystem zu entscheiden, was in Bezug auf einen bestimmten Protokolldatensatz, der wiederhergestellt werden muss, geschehen soll:

• Bei einem Protokolldatensatz aus einer Transaktion, für die ein Commit ausgeführt wurde, wobei die LSN der Datenbankseite der LSN des Protokolldatensatzes entspricht oder größer ist, muss nichts unternommen werden. Die Auswirkungen des Protokolldatensatzes wurden bereits persistent auf der Seite auf dem Datenträger gespeichert.
• Bei einem Protokolldatensatz aus einer Transaktion, für die ein Commit ausgeführt wurde, wobei die LSN der Datenbankseite kleiner ist als die LSN des Protokolldatensatzes, muss der Protokolldatensatz wiederholt werden, um sicherzustellen, dass die Transaktionsauswirkungen persistent gespeichert werden.
• Bei einem Protokolldatensatz einer Transaktion, für die kein Commit ausgeführt wurde, wobei die LSN der Datenbankseite der LSN des Protokolldatensatzes entspricht oder größer ist, muss der Protokolldatensatz rückgängig gemacht werden, um sicherzustellen, dass die Transaktionsauswirkungen nicht persistent gespeichert werden.
• Bei einem Protokolldatensatz einer Transaktion, für die kein Commit ausgeführt wurde, wobei die LSN der Datenbankseite kleiner ist als die LSN des Protokolldatensatzes, muss nichts unternommen werden. Die Auswirkungen des Protokolldatensatzes wurden auf der Seite auf dem Datenträger nicht persistent gespeichert und müssen folglich nicht rückgängig gemacht werden.

Die Wiederherstellung nach einem Systemabsturz liest das Transaktionsprotokoll und stellt sicher, dass alle Auswirkungen sämtlicher Transaktionen, für die ein Commit ausgeführt wurde, in der Datenbank persistent gespeichert werden und alle Auswirkungen sämtlicher Transaktionen, für die kein Commit ausgeführt wurde, in der Datenbank nicht persistent gespeichert werden. Dies entspricht der REDO- bzw. der UNDO-Phase. Nach Abschluss der Wiederherstellung nach einem Systemabsturz ist die Datenbank im Hinblick auf die Transaktion konsistent und kann verwendet werden.

An früherer Stelle wurde bereits erwähnt, dass der Zweck eines Prüfpunktvorgangs u. a. darin besteht, den Zeitaufwand zu verringern, der für die Wiederherstellung nach einem Systemabsturz erforderlich ist. Durch regelmäßige Auslagerung aller modifizierten Seiten auf den Datenträger wird die Anzahl der Seiten verringert, die sich aufgrund von Transaktionen, für die ein Commit durchgeführt wurde, geändert haben, deren Abbilder sich aber nicht auf dem Datenträger befinden. Hierdurch wiederum verringert sich die Anzahl der Seiten, auf die während der Wiederherstellung nach einem Systemabsturz eine REDO-Wiederherstellung angewendet werden muss.

Das Transaktionsprotokoll

Die Wiederherstellung nach einem Systemabsturz ist nur möglich, wenn das Transaktionsprotokoll unversehrt ist. Das Transaktionsprotokoll ist der wichtigste Teil der Datenbank – es ist der einzige Ort, an dem alle Änderungen der Datenbank im Fall eines Absturzes mit Sicherheit beschrieben werden.

Wenn das Transaktionsprotokoll nach einem Absturz fehlt oder beschädigt ist, kann die Wiederherstellung nach einem Systemabsturz nicht abgeschlossen werden, was zu einer verdächtigen Datenbank führt. In diesem Fall muss die Datenbank aus Sicherungskopien oder unter Verwendung weniger wünschenswerter Optionen wiederhergestellt werden, z. B. mithilfe der Notfallmodusreparatur. (Eine Beschreibung dieser Verfahren würde den Rahmen dieses Artikels sprengen, folgt aber in aller Ausführlichkeit in später erscheinenden Artikeln in diesem Jahr.)

Das Transaktionsprotokoll ist eine besondere Datei, über die jede Datenbank verfügen muss, um richtig funktionieren zu können. Es enthält die Protokolldatensätze, die durch die Protokollierung erzeugt werden, und wird verwendet, um sie während der Wiederherstellung zu lesen (weitere Verwendungszwecke der Protokollierung wurden bereits erwähnt). Neben dem Speicherplatz, der von den Protokolldatensätzen selbst beansprucht wird, reserviert eine Transaktion im Transaktionsprotokoll auch Platz für eventuell erforderliche weitere Protokolldatensätze, wenn die Transaktion abgebrochen und ein Rollback ausgeführt werden müsste. Damit lässt sich das Verhalten erklären, das Sie beobachten können, wenn eine Transaktion, die beispielsweise 50 MB Daten in der Datenbank aktualisiert, in Wirklichkeit 100 MB Speicherplatz im Transaktionsprotokoll erfordert.

Beim Erstellen einer neuen Datenbank ist das Transaktionsprotokoll im Grunde leer. Wenn Transaktionen stattfinden, werden Protokolldatensätze der Reihenfolge nach in das Transaktionsprotokoll geschrieben, was bedeutet, dass die Erstellung mehrerer Transaktionsprotokolldateien keine Leistungssteigerung bringt – ein sehr häufiges Missverständnis. Das Transaktionsprotokoll verwendet die Protokolldateien nacheinander.

Protokolldatensätze für gleichzeitige Transaktionen können im Transaktionsprotokoll eingefügt werden. Denken Sie daran, dass Protokolldatensätze für eine einzige Transaktion durch ihre LSNs verknüpft werden. Daher besteht keine Notwendigkeit, alle Protokolldatensätze für eine Transaktion im Protokoll zusammenzufassen. LSNs können Sie sich fast wie einen Zeitstempel vorstellen.

Die physische Architektur des Transaktionsprotokolls wird in Abbildung 1 gezeigt. Intern ist es in kleinere Abschnitte aufgeteilt, die als virtuelle Protokolldateien (Virtual Log Files, VLFs) bezeichnet werden. Diese Dateien vereinfachen die interne Verwaltung des Transaktionsprotokolls. Wenn eine VLF voll ist, verwendet die Protokollierung automatisch die nächste VLF im Transaktionsprotokoll. Vielleicht vermuten Sie, dass dem Transaktionsprotokoll schließlich der Platz ausgeht. An diesem Punkt unterscheidet sich das Transaktionsprotokoll jedoch grundlegend von Datendateien.

Abbildung 1 Physische Architektur des Transaktionsprotokolls

Das Transaktionsprotokoll ist in Wahrheit eine zirkuläre Datei – solange die Protokolldatensätze am Anfang des Transaktionsprotokolls abgeschnitten (oder gelöscht) wurden. Wenn die Protokollierung dann das Ende des Transaktionsprotokolls erreicht, erfolgt ein Umbruch zum Start und das Protokoll beginnt, das Vorhergehende zu überschreiben.

Wie werden nun also die Protokolldatensätze abgeschnitten, damit der von ihnen beanspruchte Platz wiederverwendet werden kann? Ein Protokolldatensatz wird im Transaktionsprotokoll nicht länger benötigt, wenn alle folgenden Kriterien erfüllt sind:

• Für die Transaktion, zu der er gehört, wurde ein Commit ausgeführt.
• Die von ihm geänderten Datenbankseiten wurden alle durch einen Prüfpunkt auf einen Datenträger geschrieben.
• Der Protokolldatensatz wird nicht für eine Sicherung (vollständige, differenzielle oder Protokollsicherung) benötigt.
• Der Protokolldatensatz ist nicht für ein Feature erforderlich, von dem das Protokoll gelesen wird (z. B. Datenbankspiegelung oder Replikation).

Ein noch benötigter Protokolldatensatz wird als aktiv bezeichnet. Gleiches gilt für eine VLF, die über mindestens einen aktiven Protokolldatensatz verfügt. Gelegentlich wird das Transaktionsprotokoll daraufhin überprüft, ob alle Protokolldatensätze in einer vollen VLF aktiv sind oder nicht. Sind alle inaktiv, wird die VLF als abgeschnitten markiert (das bedeutet, dass die VLF überschrieben werden kann, sobald das Transaktionsprotokoll an den Anfang zurückkehrt). Ist eine VLF abgeschnitten, wird sie weder überschrieben noch mit Leerdaten gefüllt. Sie wird lediglich als abgeschnitten markiert und kann dann wiederverwendet werden.

Dieser Prozess wird als Protokollabschneidung bezeichnet, nicht zu verwechseln mit der tatsächlichen Verkleinerung der Größe des Transaktionsprotokolls. Durch Protokollabschneidung wird die physische Größe des Transaktionsprotokolls niemals verändert. Hierbei wird lediglich bestimmt, welche Abschnitte des Transaktionsprotokolls aktiv sind. Abbildung 2 zeigt das Transaktionsprotokoll aus Abbildung 1 nach dem Durchführen der Abschneidung.

Abbildung 2 Das Transaktionsprotokoll nach der Protokollabschneidung

Aktive VLFs bilden das logische Protokoll, den Abschnitt des Transaktionsprotokolls, der alle aktiven Protokolldatensätze enthält. Die Datenbank selbst erhält Informationen darüber, wo die Wiederherstellung nach einem Systemabsturz beginnen sollte, Protokolldatensätze innerhalb des aktiven Abschnitts des Protokolls zu lesen – beim Beginn der ältesten aktiven Transaktion im Protokoll, der MinLSN (gespeichert in der Datenbankstartseite).

Die Wiederherstellung nach einem Systemabsturz weiß nicht, wo mit dem Lesen der Protokolldatensätze aufgehört werden muss. Daher wird der Vorgang fortgesetzt, bis ein mit Leerdaten gefüllter Abschnitt des Transaktionsprotokolls erreicht wird (wenn das Transaktionsprotokoll noch nicht wieder am Anfang angelangt ist) oder ein Protokolldatensatz, dessen Paritätsbits nicht der Reihenfolge aus dem früheren Protokolldatensatz entsprechen.

Während VLFs abgeschnitten und neue aktiv werden, verschiebt sich das logische Protokoll innerhalb der physischen Transaktionsprotokolldatei und sollte schließlich wieder zum Anfang gelangen, wie in Abbildung 3 gezeigt.

Abbildung 3 Die Zirkularität des Transaktionsprotokolls

Protokollabschneidung kann unter einer der folgenden Bedingungen stattfinden:

• Wenn ein Prüfpunkt im einfachen Wiederherstellungsmodell oder in anderen Wiederherstellungsmodellen auftritt, wobei nie eine vollständige Sicherung vorgenommen wurde. (Dies impliziert, dass eine Datenbank nach dem Wechsel aus einem einfachen Wiederherstellungsmodell in einem pseudoeinfachen Wiederherstellungsmodell verbleibt, bis eine vollständige Datenbanksicherung stattfindet.)
• Bei Abschluss einer Protokollsicherung.

Denken Sie daran, dass Protokollabschneidung u. U. nicht möglich ist, denn es gibt viele Gründe dafür, dass ein Protokolldatensatz aktiv bleiben muss. Wenn keine Protokollabschneidung möglich ist, können die VLFs nicht abgeschnitten werden und das Transaktionsprotokoll muss schließlich wachsen (oder es muss eine weitere Transaktionsprotokolldatei hinzugefügt werden). Übermäßiges Wachstum des Transaktionsprotokolls kann Leistungsprobleme verursachen. Zurückzuführen ist dies auf das Phänomen der VLF-Fragmentierung. Durch Beseitigung der VLF-Fragmentierung lassen sich mitunter deutliche Verbesserungen der Leistung protokollbezogener Aktivitäten erzielen.

Weitere Informationen hierzu finden Sie im Blogbeitrag 8 Steps to Better Transaction Log Throughput von Kimberly Tripp. Thema dieses interessanten Beitrags sind bewährte Methoden in Bezug auf die Kapazitätsplanung für Transaktionsprotokolle, die Verwaltung und Leistungsverbesserungen.

Es gibt zwei häufige Probleme, die die Protokollabschneidung verhindern können:

• Eine aktive Transaktion mit langer Laufzeit. Das gesamte Transaktionsprotokoll ab dem ersten Protokolldatensatz von der ältesten aktiven Transaktion kann nicht abgeschnitten werden, solange diese Transaktion nicht abgebrochen wird oder ein Commit dafür ausgeführt wird.
• Wechseln zum vollständigen Wiederherstellungsmodell, Anfertigen einer vollständigen Sicherung und kein Durchführen von Protokollsicherungen. Das gesamte Transaktionsprotokoll bleibt aktiv und wartet darauf, durch eine Protokollsicherung gesichert zu werden.

Eine vollständige Liste der Faktoren und Hinweise, mit deren Hilfe sich bestimmen lässt, was eine Protokollabschneidung verhindert, finden Sie unter Faktoren, die das Abschneiden des Protokolls verzögern können in der SQL Server-Onlinedokumentation. In der von mir erstellten Videovorführung wird gezeigt, welche Auswirkungen das unkontrollierte Wachstum des Transaktionsprotokolls hat und wie die VLF-Fragmentierung beseitigt werden kann. Sie finden diesen Videoscreencast (sowie frühere Screencasts zu SQL-Themen) unter technetmagazine.com/videos.

Wenn das Transaktionsprotokoll seine volle Kapazität erreicht und nicht weiter wachsen kann, wird der Fehler 9002 ausgegeben. Dann müssen Sie Schritte unternehmen, um mehr Platz zur Verfügung zu stellen, z. B. durch Erweitern der Protokolldatei, Hinzufügen einer weiteren Protokolldatei oder Beseitigen von Hindernissen, die der Abschneidung des Protokolls im Wege stehen.

Unter keinen Umständen sollten Sie das Transaktionsprotokoll löschen, es mithilfe nicht dokumentierter Befehle neu erstellen oder es einfach mit den Optionen NO_LOG oder TRUNCATE_ONLY von BACKUP LOG abschneiden (diese wurden in SQL Server 2008 entfernt). Die Verwendung dieser Optionen führt entweder zu Transaktionsinkonsistenz (und wahrscheinlich zu Beschädigungen) oder nimmt Ihnen die Möglichkeit, die Datenbank ordnungsgemäß wiederherzustellen.

Weitere Informationen zur Problembehandlung bei vollen Transaktionsprotokollen finden Sie in der Onlinedokumentation unter Problembehandlung bei vollen Transaktionsprotokollen (Fehler 9002).

Wiederherstellungsmodelle

Wie Sie sehen, hängt das Verhalten des Transaktionsprotokolls zum Teil vom Wiederherstellungsmodell ab, das die Datenbank verwendet. Alle drei verfügbaren Wiederherstellungsmodelle haben Auswirkungen auf das Verhalten des Transaktionsprotokolls und/oder darauf, wie Vorgänge protokolliert werden.

Beim vollständigen Wiederherstellungsmodell wird jeder Teil jedes Vorgangs protokolliert. Hier spricht man von der vollständigen Protokollierung. Nach Anfertigung einer vollständigen Datenbanksicherung im vollständigen Wiederherstellungsmodell wird das Transaktionsprotokoll nicht automatisch abgeschnitten, solange keine Protokollsicherung vorgenommen wird. Wenn Sie von der Protokollsicherung und der Möglichkeit der Wiederherstellung einer Datenbank in einem Zustand zu einem bestimmten Zeitpunkt keinen Gebrauch machen möchten, sollten Sie das vollständige Wiederherstellungsmodell nicht verwenden. Wenn Sie jedoch Datenbankspiegelung verwenden möchten, bleibt Ihnen keine Wahl, da nur das vollständige Wiederherstellungsmodell unterstützt wird.

Das BULK_LOGGED-Wiederherstellungsmodell verfügt über die gleiche Semantik der Transaktionsprotokollabschneidung wie das vollständige Wiederherstellungsmodell, ermöglicht aber, einige Vorgänge teilweise zu protokollieren. Hierbei spricht man von minimaler Protokollierung. Beispiele sind eine Indexneuerstellung und einige Massenladevorgänge: Beim vollständigen Wiederherstellungsmodell wird der gesamte Vorgang protokolliert.

Beim BULK_LOGGED-Wiederherstellungsmodell werden jedoch nur die Zuordnungsänderungen protokolliert, wodurch die Anzahl der erzeugten Protokolldatensätze drastisch sinkt. Hierdurch wiederum verringert sich das Potenzial für das Transaktionsprotokollwachstum. Weitere Informationen zu Vorgängen mit minimaler Protokollierung finden Sie in der Onlinedokumentation im Abschnitt Vorgänge, für die eine minimale Protokollierung verfügbar ist.

Abschließend sei erwähnt, dass das einfache Wiederherstellungsmodell tatsächlich dasselbe Protokollierungsverhalten aufweist wie das BULK_LOGGED-Wiederherstellungsmodell. Die Semantik für die Transaktionsprotokollabschneidung unterscheidet sich jedoch deutlich. Protokollsicherungen sind im einfachen Wiederherstellungsmodell nicht möglich. Das bedeutet, dass das Protokoll abgeschnitten werden kann (sofern die Protokolldatensätze nicht anderweitig aktiv gehalten werden), wenn ein Prüfpunkt auftritt. Jedes dieser Wiederherstellungsmodelle hat Vor- und Nachteile in Bezug auf die möglichen (oder erforderlichen) Sicherungen und die Möglichkeit der Wiederherstellung bestimmter Zustände zu verschiedenen Zeitpunkten (darauf wird in einem späteren Artikel in diesem Jahr näher eingegangen).

Zusammenfassung

Dieser Artikel bietet eine relativ theoretische Erläuterung der Funktionsweise eines wichtigen Teils von SQL Server. Ich hoffe, es ist mir gelungen, eventuell vorhandene Missverständnisse aufzuklären. Wenn das Thema Protokollierung und Wiederherstellung noch ganz neu für Sie ist, sollten Sie die folgenden Kernpunkte als wichtigste Erkenntnisse aus diesem Artikel betrachten:

• Erstellen Sie nicht mehrere Protokolldateien, da dies nicht zu einer Leistungssteigerung führt.
• Informieren Sie sich über das von Ihrer Datenbank verwendete Wiederherstellungsmodell und die Auswirkungen dieses Modells auf das Transaktionsprotokoll, insbesondere im Hinblick darauf, ob es zur automatischen Abschneidung in der Lage ist, wenn ein Prüfpunkt auftritt.
• Berücksichtigen Sie die Möglichkeit des Wachstums des Transaktionsprotokolls, die Faktoren, die dazu führen können, sowie die Möglichkeiten, die Kontrolle wiederzugewinnen.
• Informieren Sie sich, wo Sie Hilfe bei der Problembehebung für ein volles Transaktionsprotokoll erhalten.

Mein Blog enthält viele weitere Informationen zum Transaktionsprotokoll und zu den Faktoren, die es beeinflussen. Einzelheiten finden Sie unter Shrinking the database before taking a backup. Ebenfalls zu empfehlen sind die verschiedenen Themen der Onlinedokumentation, die das Transaktionsprotokoll betreffen, z. B. zur Transaktionsprotokollverwaltung.

Senden Sie Ihr Feedback oder Ihre Fragen (in englischer Sprache) an Paul@SQLskills.com.

Mein Dank gilt Kimberly L. Tripp für die technische Prüfung dieses Artikels.

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Paul Randal war von 1999 bis 2007 im SQL Server-Speichermodulteam von Microsoft tätig. Er schrieb DBCC CHECKDB/repair für SQL Server 2005 und war bei der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig. Als Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung ist Paul Randal ein regelmäßiger Referent bei Konferenzen weltweit. Sein Blog befindet sich unter SQLskills.com/blogs/paul.