Initialgröße von Protokolldatei in Verbindung mit SHRINKFILE

von | Feb. 6, 2015 | 300, Administration, SQL Blog, Tipps und Tricks

Einen Tag nach der SQL Konferenz in Darmstadt habe ich Zeit gehabt, meine Emails zu bearbeiten. Unter den vielen Emails war eine interessante Frage eines Teilnehmers des SQLSaturday in Slowenien, die sich mit der Größe der Protokolldatei einer Datenbank beschäftigte.

Eine Datenbank (einfache Wiederherstellung) besitzt eine Protokolldatei mit einer Größe von 76 GB. Der DBA möchte das Protokollmedium auf eine moderate Größe von 100 MB verkleinern. Trotz mehrerer Versuche lies sich die Protokolldatei auf lediglich 1.250 MB verkleinern. Warum das Protokollmedium sich nicht weiter verkleinern lies, beschreibt dieser Artikel.

Hinweis

Dieser Artikel beschreibt nicht die generelle Funktionsweise einer Transaktionsprotokolldatei sondern setzt voraus, dass der Leser die Grundlagen bereits kennt. Ein sehr gutes Dokument für den Einstieg stellt TRIVADIS bereit: “Handling der Transaction Log Files im MS SQL Server”

Transaktionsprotokoll

Die Transaktionsprotokolldatei einer Datenbank ist immer wieder ein Problem im täglichen Umfeld eines DBA. BITTE DIE EINZAHL BEACHTEN – es sollte immer nur eine Protokolldatei vorhanden sein; Microsoft SQL Server verarbeitet Transaktionen immer seriell!
Wenn ein Workload zu groß wird oder aber eine Transaktion nicht in Zwischenschritten abgearbeitet werden kann (z. B. bei ETL-Prozessen), dann wird die Protokolldatei schnell sehr groß und konsumiert einen nicht erheblichen Anteil des Storages. Selbst bei einer Datenbank im Wiederherstellungsmodus SIMPLE kann das Protokoll sehr stark wachsen, wenn auch nur eine einzelne Transaktion ein hohes Transaktionsvolumen produziert.
Da viele erfahrene DBA in der Regel bereits im Vorfeld wissen, dass – insbesondere bei der Erstinstallation – für die Importe große Transaktionsvolumen generiert werden, legt man die Datenbanken mit sehr großen Transaktionsprotokolldateien an. Dieser Umstand kann aber bei “Normalbetrieb” zu einem Problem werden wie das folgende Beispiel verdeutlicht.
Zunächst wird eine Datenbank mit einer Initialgröße von 10 GB für das Protokoll erstellt. Der nachfolgende Code erstellt eine einfache Datenbank, dessen Transaktionsprotokoll initial sehr groß gewählt wird. Je nach Initialgröße/Wachstumsgröße werden zwischen 4 und 16 sogenannter VLF (Virtual Log Files) innerhalb einer Transaktionsprotokolldatei erstellt (siehe Kimberly Tripp):

  • Größe / Vergrößerung: < =64 MB: 4 VLF
  • Größe / Vergrößerung: 64 MB – 1024 MB: 8 VLF
  • Größe / Vergrößerung: 1.024 MB: 16 VLF

Hinweis: Dieser Algorithmus hat sich mit Microsoft SQL Server 2014 geändert (siehe Paul Randal)!

CREATE DATABASE [demo_db]
ON  PRIMARY 
(
    NAME = N'demo_db',
    FILENAME = N'F:\DATA\demo_db.mdf',
    SIZE = 100MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 100MB
)
LOG ON 
(
    NAME = N'demo_log',
    FILENAME = N'F:\DATA\demo_db.ldf',
    SIZE = 10240MB,
    MAXSIZE = 50000MB,
    FILEGROWTH = 100MB
);
GO

Sobald die Datenbank angelegt wurde, werden die Informationen über die erstellten VLF mit dem folgenden Befehl angezeigt:

USE demo_db;
GO

DBCC LOGINFO();
GO

Das Ergebnis präsentiert sich wie erwartet – es sind insgesamt 16 VLF, die sich über die Transaktionsprotokolldatei erstrecken.

ResultSet_01

Wie man gut erkennen kann, erstreckt sich die Verteilung der VLF sehr gleichmäßig auf insgesamt 16 Einheiten. Jede Einheit ist ~671.023.104 Bytes (0,625 GB) groß. Soll das Transaktionsprotokoll verkleinert werden, wird mit dem nachfolgenden Befehl, versucht, eine Initialgröße von 100 MB zu erreichen:

USE demo_db;
GO

DBCC SHRINKFILE ('demo_log', 100); 
GO

Das Ergebnis der Aktion sieht nicht sehr erfolgversprechend aus, da – augenscheinlich – die Größe nicht auf 1 MB reduziert wurde sondern auf 1,25 GB!

ResultSet_02

Die obigen Angaben stammen aus der Aktion DBCC SHRINKFILE. Die Größenangabe bezieht sich immer auf Datenseiten (1 Datenseite = 8.192 Bytes). 163.825 Datenseiten entsprechen 1,250 GB.
Die Ursache für dieses Verhalten versteckt sich in der Größe der VLF. Auf Grund der hohen Startgröße des Transaktionsprotokolls wurde der Initialwert gleichmäßig auf 16 VLF aufgeteilt. Beim Verkleinern verbleiben aber IMMER mindestens zwei VLF in der Protokolldatei. Schaut man sich den Inhalt des Transaktionsprotokolls nach der Verkleinerung wieder an, kann man das sehr deutlich erkennen:

DBCC LOGINFO();
GO

ResultSet_03

Zusammenfassung

Die Initialgröße des Transaktionsprotokolls sollte immer sehr gut überlegt sein. Abhängig vom Workload der Applikation kann es sicherlich sinnvoll sein, dass das Transaktionsprotokoll bereits vor der Verwendung ausreichend dimensioniert ist/wird. Wird jedoch ausschließlich für das Beladen einer neuen Datenbank das Transaktionsprotokoll initial mit großen Startwerten initialisiert, ist unter Umständen eine ausreichende Verkleinerung nicht mehr möglich. In diesem Fall bleibt nur die Möglichkeit, die Datenbank abzuhängen, die Transaktionsprotokolldatei zu löschen und die Datenbank erneut OHNE Transaktionsprotokoll wieder anzuhängen. Dabei wird dann eine neue – kleinere – Datei für das Transaktionsprotokoll erzeugt.

Links

Das Thema “Transaktionsprotokoll” ist immer wieder eine Herausforderung für DBA. Nachfolgend ein paar – aus meiner Sicht – sehr interessante Links zu diesem komplexen Thema:

Herzlichen Dank fürs Lesen!