Transaktionslog-Management in SQL Server

Überblick

Das Transaktionslog (kurz “TLog”) einer Datenbank ist eine wichtige Komponente zur Datenbankwiederherstellung im Fall logischer, physikalischer oder menschlich verursachter Fehler. Wie man dem Namen entnehmen kann, handelt es sich, ähnlich wie bei einem Logbuch, um ein Objekt (eine Datei), in dem alle Transaktionen einer Datenbank seit der letzten Datensicherung (Voll- und TLog) protokolliert werden. Dies ermöglicht es, ausgehend vom letzten Backup, abgeschlossene Transaktionen nachzufahren und so bis zum Zeitpunkt des Ausfalls (“Point in Time Recovery”) wiederherzustellen.

Unter gewissen Umständen kann die TLog-Datei extrem groß werden, so dass sie die Größe der Datenbank erreicht oder sogar überschreitet. Ein solches Wachstum hat nicht nur negative Auswirkungen auf den Storage, der irgendwann sein Limit erreicht, sondern auch auf die Performance der Datenbank.

Ausgangspunkt für diesen Beitrag ist ein Ereignis bei einem Kunden, das genau zu dieser Situation geführt hat, dass der Kauf weiterer Storage-Kapazität in Betracht gezogen wurde. Nach Kontakt mit der Mainzer Datenfabrik konnten wir diese unnötige Investition verhindern.

Alle Aussagen beziehen sich auf eine Datenbank im Full-Recovery-Modus, der Simple-Recovery-Modus sollte i.d.R. nicht verwendet werden, höchstens aber in Umgebungen in denen größere Datenverluste akzeptiert werden bzw. die Möglichkeit besteht, die Daten aus anderen Systemen mit akzeptablem Zeit- und Personalaufwand wiederherzustellen.

• Überblick
• Aufbau der TLog-Datei, VLFs
• Verkleinerung von Datenbank-Dateien
• Verkleinerung von TLog-Dateien
• Bereinigen belegter VLFs
• Monitoring der VLFs
• Anzahl der VLFs und Performance
• Zusammenhang zwischen Dateiwachstum und VLF-Anzahl
• Verkleinerung der TLog-Datei
  • Beispiel für die Reduktion einer TLog-Datei auf ein Minimum
• Strategie bei der Verkleinerung von TLog-Dateien
• Hindernis bei der TLog-Verkleinerung
• Fazit

Aufbau der TLog-Datei, VLFs

Die TLog-Datei besteht logisch aus vielen Abschnitten die als virtuelle Logfiles oder kurz VLF bezeichnet werden. Diese VLFs dienen zur Speicherung der transaktionsspezifischen Informationen. Eine der datenbankinternen Information über VLFs ist ihr aktueller Zustand, das heißt eine Information darüber, ob sie beschrieben werden dürfen, weil sie bereits gesichert wurden, oder ob sie eventuell weiterhin für ein Recovery benötigt werden. Dieser Zustand kann in aktuellen Datenbanken nur die Werte 0 (frei) und 2 (Inhalt wird im Fall eines erforderlichen Recovery noch benötigt), den Wert 1 verwenden neuere Installationen nicht mehr.

Der Inhalt wird benötigt, solange

1. eine Transaktion noch offen ist
2. keine Sicherung der TLog-Datei erfolgt ist
3. die jeweiligen Transaktionen nicht über die verschiedenen Datenaustauschmöglichkeiten (klassische Replikation, AlwaysOn Verfügbarkeitsgruppen, Datenaustausch per Change Data Capture) übertragen wurden
4. Backup- und Restore-Operationen aktiv sind
5. Aktive Index-Wartungsjobs laufen

Die Belegung der VLFs erfolgt zyklisch, wobei natürlich nur diejenigen VLFs verwendet werden, deren Status 0 ist. Sind alle VLFs belegt und werden weitere benötigt, so wird die TLog-Datei entsprechend ihrer Wachstumseinstellungen automatisch vergrößert.

Verkleinerung von Datenbank-Dateien

Datenbankdateien können grundsätzlich verkleinert werden, dies ist aber im Allgemeinen nicht empfehlenswert: Einerseits wird hierdurch die Fragmentierung der jeweiligen Datei gefördert, was zu Performanceeinbußen führen kann. Das Problem ist im Umfeld  elektronischer Speichersysteme nicht mehr so groß wie bei den mechanischen Platten früherer Server, sollte aber trotzdem beachtet werden. Weiterer Nachteil der Verkleinerung der DB-Dateien: Erfahrungsgemäß wachsen die jeweiligen Dateien später durch neu eingefügte Daten wieder an, so dass die Dateien mit einem entsprechenden Performanceverlust wieder vergrößert werden müssen.

Bei der Verkleinerung von Datenbank-Dateien muss berücksichtigt werden, dass diese nur am Ende der Datei stattfinden kann. Befinden sich also am Ende der Datei noch Datenblöcke, die weiterhin benötigt werden, so lässt sich die jeweilige Datei nicht verkleinern.

Verkleinerung von TLog-Dateien

Auch TLog-Dateien können verkleinert werden. Hier gilt grundsätzlich das Gleiche, was auch schon zu den “normalen” Datenbankdateien im vorhergehenden Kapitel geschrieben wurde. Hier gibt es jedoch zwei Ausnahmen:

TLog-Dateien sollten verkleinert werden, wenn sie

1. extrem groß geworden sind (vgl. einleitendes Kapitel "Überblick")
2. sehr viele VLFs enthalten, was  insbesondere bei Recovery-Operationen und DB-Neustart zu Performanceeinbußen führen kann. Idealerweise sollte die TLog-Datei ca. 50 VLFs enthalten, aber das ist nur eine pauschale Aussage für eine Datenbank deren Transaktionsaufkommen nicht bekannt ist. Eine Anzahl von 200 - 300 ist bei Datenbanken mit hoher Transaktionslast und vielen Sessions auch akzeptabel. Ab 500 sollte man über eine Verringerung nachdenken und ab 1000 raten wir dringend dazu. Wie man hierbei vorgeht, wird weiter unten im Kapitel Strategie bei der Verkleinerung von TLog Dateien beschrieben.

Bereinigen belegter VLFs

Wie aus der Funktionalität hervorgeht, werden VLFs erst dann als leer (0) markiert, wenn sie nicht mehr für die in Aufbau der TLog Datei, VLFs genannten Zwecke benötigt werden. Die Replikationsaktivitäten erfolgen i.d.R. permanent, sofern der Empfänger die zu übertragenden Daten empfangen und verarbeiten kann. Hier können jedoch Verbindungsprobleme auf Netzwerkebene zum Hindernis werden. Im Rahmen des TLog-Backups wird der Status der VLFs, sofern sie nicht für die übrigen Zwecke benötigt werden, auf 0 gesetzt. Anschließend lässt sich die TLog Datei im günstigsten Fall physisch verkleinern. Voraussetzung dazu ist, dass nicht noch am Ende der Datei durch aktuell aktive Transaktionen VLFs belegt sind. Hieraus ergibt sich, dass man für das Verkleinern der TLog-Datei möglichst transaktionsarme oder -freie Zeiten wählen sollte, idealerweise ein Wartungsfenster.

Monitoring der VLFs

Zur Ermittlung des Status und der Anzahl der VLFs in einer TLog-Datei kann eine der folgenden drei Anweisungen verwendet werden:

Ermittlung von Anzahl und Status der VLFs einer TLog-Datei

dbcc loginfo -- sofern man sich in der zu analysierenden Datenbank befindet


dbcc loginfo ('<Datenbankname'>)


SELECT * FROM sys.dm_db_log_info(DB_ID('<Datenbankname>'))

-- Ermittlung der Gründe die gegen eine Freigabe von TLog-Bereichen sprechen
SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Das letzte Statement des vorhergehenden Codeblocks kann pro Datenbank jeweils eines der Ergebnisse der folgenden Tabelle liefern:

Anzahl der VLFs und Performance

Eine pauschale Empfehlung zur Anzahl der VLFs hinsichtlich Performancebetrachtungen gibt die folgende Tabelle:

Zusammenhang zwischen Dateiwachstum und VLF-Anzahl

Bei jeder Vergrößerung der TLog-Datei wird der Datei eine bestimmte Anzahl von VLFs hinzugefügt. Diese Anzahl ist abhängig von dem Wachstum, das bei der Erweiterung konfiguriert ist und ist in der folgenden Tabelle wiedergegeben:

Verkleinerung der TLog-Datei

Sofern keine der in Monitoring der VLFs aufgeführten Werte gegen eine Verkleinerung der TLog-Datei sprechen und sich keine belegten VLFs am Ende der Datei befinden, kann diese mit folgendem Befehl verkleinert werden:

USE [<Datenbankname>]
GO
DBCC SHRINKFILE (N'Logischer Name der Log-Datei', <Gewünschte Dateigröße in MB>)
-- Der logische Name der Log-Datei kann mit der folgenden Abfrage ermittelt werden:
select name from sys.database_files where type_desc = 'LOG'
GO

Hierbei kann der zweite Parameter zur Angabe der gewünschten Größe auf 0 gesetzt werden, um die Datei auf ihre Minimalgröße unter Berücksichtigung der belegten VLFs am Ende der Datei zu bringen.

Beispiel für die Reduktion einer TLog-Datei auf ein Minimum

Die folgende Abbildung stellt schematisch die Struktur einer TLog-Datei mit N VLFs dar. Freie VLFs sind mit dem Statuswert 0 und grünem Hintergrung versehen, belegte mit dem Statuswert 2 bzw. rot. In der hier dargestellten Situation lässt sich die die TLog-Datei lediglich um die drei VLFs [VLF N], [VLF N-1] und [VLF N-2], weil diese, betrachtet vom Ende der Datei, den größten zusammenhängenden Speicherbereich am Ende der Datei darstellen.

Übrigens: Die Größen der VLFs innerhalb einer TLog-Datei sind nicht zwingend gleich, sondern abhängig von der Historie der Einstellungen für Dateierweiterungen. Das folgt bereits aus Kapitel Zusammenhang zwischen Dateiwachstum und VLF Anzahl: Wird die TLog-Datei intial mit 64 MB angelegt, so erhält man vier VLFs der Größe 16 MB. Ändert man anschließend das Wachstum auf beispielsweise 640 MB, so kommen 8 VLFs der Größe 80 MB hinzu.

Strategie bei der Verkleinerung von TLog-Dateien

Wie Sie dem bisher geschriebenen entnehmen können, ist - pauschal gesagt - eine Anzahl von 50 VLFs in einer nicht übermäßig strapazierten Datenbank optimal. Ziel ist nun, diese Anzahl zu erreichen. Aus dem Zusammenhang zwischen Wachstum und VLF-Anzahl geht hervor, dass man versuchen sollte, ausgehend von einer minimalen TLog-Datei lediglich drei Dateiverbrößerungsoperationen (3 * 16 ==> 48 VLFs) durchzuführen. Idealerweise ist Ihnen dazu bekannt, wie hoch der Platzbedarf der TLog-Datei ist. Entsprechend einer groben Einschätzung für eine Datenbank, deren Transaktionsumfang unbekannt ist, geht man von 25% der Datenbankgröße aus. Diese Größe, in MB, wird durch drei dividiert. Das Ergebnis dieser Division wird zur Konfiguration der automatischen TLog-Dateivergrößerung herangezogen, wobei der Wert max( 1025, <ermittelter Quotient>) [MB] verwendet wird. Hierdurch wird erreicht, dass die nächsten drei erforderlichen Dateierweiterungen jeweils zu 16 weiteren VLFs führen.

Hierzu können die folgenden Statements verwendet werden:

-- Prüfen der aktuellen Situation
dbcc loginfo('<Name der Datenbank>')

-- alternativ
SELECT * FROM sys.dm_db_log_info(DB_ID())

-- ggf. zuvor TLog-Backup, falls sich am Ende der Datei belegte VLFs befinden

-- Verkleinern der TLog-Datei auf Minimalgröße (= Parameter 0)
dbcc shrinkfile( '<Logischer Name der Log-Datei>', 0)

-- Der logische Name der Log-Datei kann mit der folgenden Abfrage ermittelt werden:
select name from sys.database_files where type_desc = 'LOG'

-- Erweiterungen jeweils um 1025 MB ==> 16 VLFs
ALTER DATABASE [<Name der Datenbank>] MODIFY FILE ( NAME = N'<log. TLog Name', FILEGROWTH = 1025MB )

Hindernis bei der TLog-Verkleinerung

In den vorherigen Ausführungen wurde aufgezeigt, wodurch VLFs trotz Backups weiterhin als belegt markiert sind, weil beispielsweise noch keine Synchronisation mit Fremddatenbanken (Replikation, AlwaysOn Verfügbarkeitsgruppen) stattgefunden hat. Ein weiteres Hindernis stellt die Verwendung speicheroptimierter Tabellen dar. Diese verwenden einen eigenen Synchronisationsmechanismus zwischen Hauptspeicher und Storage über einen asynchronen Garbage Collector. Wir haben verschiedene Möglichkeiten ausprobiert, diesen Garbage Collector mittels Aufruf von

EXEC sys.sp_xtp_checkpoint_force_garbage_collection;

zu aktivieren. Leider ohne Erfolg. Die belegten VLFs am Ende der Datei wurden nicht freigegeben,

Nach verschiedenen Maßnahmen haben sich die in dem folgenden Script wiedergegebenen Aktionen als Erfolg herausgestellt. Hier muss in Produktivumgebungen darauf beachtet werden, dass die Aktionen nur in einem Wartungsfenster durchgeführt werden und ein gültiges Backup verfügbar ist.

-- Werden VLFs am Ende einer TLog-Datei nicht freigegeben, obwohl die Datenbank keine
-- Replikationsmechanismen verwendet, können diese Statements helfen:

USE master;
GO

-- Achtung: Nur, wenn du danach wieder in FULL gehst und ein vollständiges Backup machst!
ALTER DATABASE [<Datenbankname>] SET RECOVERY SIMPLE;
GO

-- Checkpoint setzen
CHECKPOINT;
GO

-- TLog-Datei auf die minimale Größe reduzieren
DBCC SHRINKFILE (N'<Logischer Name der TLog-Datei>', 0);
GO

-- Datenbank in den Full-Recovery Modus zurücksetzen
ALTER DATABASE [<Datenbankname>] SET RECOVERY FULL;
GO

-- Vollsicherung der Datenbank
BACKUP DATABASE [<Datenbankname>]
TO DISK = '<Backup-Pfad>\<Datenbankname>_VLF_bereinigt.bak'
WITH INIT, COMPRESSION;
GO

Fazit

Transaktionslog-Dateien können in bestimmten Fällen extrem groß werden, die Größe der Datenbank um ein Mehrfaches übertreffen und dadurch den verfügbaren Plattenplatz bis zum Limit füllen. In den meisten Fällen lässt sich das Problem, vorausgesetzt man hat es erkannt (==> Monitoring!), leicht durch ein TLog-Backup und anschließende Verkleinerung der Datei beheben. Im Fall der Nutzung speicheroptimierter Tabellen kann es jedoch zu der Situation kommen, dass belegte VLFs am Ende der TLog-Datei trotz TLog-Backups nicht freigegeben werden. In einem solchen Fall hilft nur noch die kurzzeitige Umschaltung der DB in den Simple Recovery Modus und ein anschließender Checkpoint, bevor in den Full Recovery Modus zurückgeschaltet wird. Wichtig ist hierbei, dass man unmittelbar danach eine Vollsicherung durchführt.

Wenn Sie mehr zu diesem Thema erfahren möchten, stehen Ihnen unsere Experten gerne zur Verfügung. Vereinbaren Sie unverbindlich ein Beratungsgespräch über unser Kontaktformular. Wir unterstützen Sie gerne!

‍