SQL Server Transaktionsprotokoll – Was ist das?

Was ist das “Transaktionsprotokoll” (TP)?

Das TP ist eine LOG-Datei in der alle Aktionen gespeichert werden, die auf eine Datenbank ausgeführt werden, also z.B. einen neuen Datensatz anlegen, löschen oder ändern. Die zugehörigen Datei auf der Festplatte hat die Dateienendung “ldf”.

Welchen Zweck hat das TP?

Eine Datenbank wird normalerweise einmal täglich gesichert, sagen wir abends um 20.00 Uhr. Was ist mit allen den Aktionen, die zwischen einer Sicherung ausgeführt werden? Diese wären im Fall eines Festplattenausfalls verloren, wenn nur die letzte Sicherung zur Verfügung stehen würde. Deswegen kann zusätzlich das Transaktionsprotokoll, das alle Änderungen an der DB enthält, in kurzen Zeitintervallen (15-30 Min! ja, Minuten) gesichert werden.

Warum nicht einfach die Datenbank anstelle des TP öfters sichern?

Nehmen wir an, es handelt sich um eine Datenbank mit der Größe von 30 GB die alle 10 Minuten gesichert wird, was zur Folge hätte, dass max. 10 Minuten Arbeit verloren gingen. Selbst bei einer Differenzialsicherung (es wird nur die Änderung seit der letzten Vollsicherung gesichert) würde die Sicherung möglicherweise zu lange dauern, den (SQL-) Server zu stark ausbremsen und sehr große Datenmenge produzieren. Und was ist, wenn ich alle 10 oder gar 1 Sekunde sichern will, weil ich es mir nicht leisten kann, 10 Min. zu verlieren? Ich denke, das Problem ist klar!

Größe des Transaktionsprotokoll

Das TP ist in verschiedene “virtuelle Seiten” aufgeteilt, die, wie oben beschrieben, jede einzelne Änderung an der DB enthalten. Theoretisch kann das TP “unendlich” anwachsen, was aber nicht der Fall sein sollte. In der SQL Server Management Konsole kann die Anzahl der Seiten innerhalb des TP abgerufen werden. Der Befehl dazu lautet:

DBCC LOGINFO (’professional_pro_DEMO’)

Wobei ‘professional_pro_DEMO’ durch den Namen Ihrer (PSP) Datenbank ersetzt werden muss. Bei “Ergebnisse” stellt jede Zeile eine virtuelle Seite dar. Alle mit dem Status 2 sind aktiv, alle mit dem Status 0 nicht mehr.

TP verkleinern

Ist das Transaktionsprotokoll zu groß, kann es verkleinert werden. Das TP lässt aber sich nur verkleinern, wenn sowohl die DB als auch das TP vorher gesichert wurde.

DB sichern (ohne aktuelle DB Sicherung kann das TP nicht gesichert werden)

Tasks > Sichern aufrufen

Sicherungstyp “Vollständig” und mit OK die Sicherung ausführen.

1. TP sichern
   Tasks > Sichern aufrufen
   Sicherungstyp “Transaktionsprotokoll” und mit OK bestätigen
2. TP verkleinern
   Tasks > Verkleinern > Dateien (nicht Datenbank!)Datentyp “Protokoll” und mit OK bestätigen. Das TP bzw. dessen Datei mit der Endung “ldf” wird verkleinert und alles was eben gesichert wurde, wird entfernt.

Alternativ kann die Wiederherstellungsmethode der DB auch auf “Einfach” eingestellt werden (s.u.), dann die Verkleinerung des TP vorgenommen werden und dann die DB Wiederherstellungsmethode auf “Vollständig” zurückgestellt werden.

Ist eine regelmäßige TP Sicherung notwendig?

Wie oben beschrieben dient das TP bzw. dessen Sicherung dazu, auch Stände nach der letzten DB Sicherung wiederherzustellen. Das Problem dabei: PSP besteht nicht nur aus Datenbankeinträgen sondern auch aus Dateien im DATA-Verzeichnis. Sollte also bei einem Plattencrash der aktuellste Stand wiederhergestellt werden, so muss nicht nur das TP alle x Minuten gesichert werden (was bedeutet als Sicherung aus dem SQL Server geschrieben und dann von der Festplatte wegkopiert werden) sondern auch das PSP DATA-Verzeichnis, weil eine wiederhergestellt DB ohne den dazugehörigen Dateien nichts bringt. Werden die TP nicht wirklich regelmäßig gesichert, kann auch eine vereinfachte Variante der Datensicherung genutzt werden, bei der nur die DB täglich gesichert wird.

Datensicherung

Das Wiederherstellungsmodell “Einfach”

Der SQL Server unterstützt 3 Wiederherstellungsmodelle: Vollständig, Massenprotokolliert und Einfach.

Vollständig bedeutet das als Sicherungsstrategie sowohl die tägliche DB Sicherung wie auch die 15-30 minütige TP Sicherung zum Einsatz kommt. Hierbei wächst das TP an und muss ab und an verkleinert werden. Vollständig macht wenig Sinn, wenn das TP nicht wirklich im ¼ Stundentakt gesichert wird. Wird das TP alle 15 Minuten gesichert, kann die DB mit max. 15 Minuten “Verlust” wiederhergestellt werden. Das Problem dabei im PSP Umfeld: Wer sichert analog alle 15 Minuten sein PSP-DATA Verzeichnis das von einem Festplattencrash ebenfalls betroffen wäre?

Massenprotokolliert ist für PSP nicht relevant!

Bleibt noch die Wiederherstellungsstrategie “Einfach“. Bei dieser Methode wird “nur” die Datenbank gesichert nicht aber das TP.

Das Wiederherstellungsmodell wählen

Es obliegt dem Admin, das für Ihn passende Wiederherstellungsmodell zu wählen!

Über RMT auf die entsprechende DB und Auswahl von “Eigenschaft” aus dem Kontextmenü werden die Eigenschaften der Datenbank angezeigt.

Bei den “Optionen” kann das Wiederherstellungsmodell gewählt werden.