Schön ist, wenn man als Datenbank-Administrator ein ruhiges Leben führen kann: Dann, wenn alle Datenbank-Wartungen gescriptet sind und automatisch laufen. So müssen nur zeitweise die Logs durchgesehen werden und die administrativen Tätigkeiten sind sehr gering (Tipp: Siehe übrigens auch Microsoft Codename Atlanta – SQL Monitoring Teil 1, 2 und 3). Allerdings kann es dennoch manchmal Situationen geben, wo eine Datenbank “steht”.
Die Datenbank “steht”
Das kann beispielsweise eintreten, wenn das Transaction Logfile einer Datenbank so groß wird, dass kein Platz mehr auf der Festplatte vorhanden ist – und die Datenbank nicht mehr funktioniert – zu mindestens alle Datenoperationen nicht mehr. SQL Server markiert dann die Datenbank oft als fehlerverdächtig (suspect).
“Warum wächst ein Datenbank Logfile so stark?”
Diese Frage haben sich schon viele Administratoren gestellt. Die Datenbank ist vielleicht winzig, aber das Logfile dazu wächst und wächst und wächst… die Analogie zur Duracell-Batterien Werbung drängt sich auf…
Dazu möchte ich hier ein wenig Einblick geben und auch Lösungen liefern, vor allem, wenn Sie eine solche Fehlermeldung erhalten oder im Log finden:
…failed with the following error: "The transaction log for database <dbname>' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Aber schön der Reihe nach.
Die Beteiligten
Eine SQL Server Datenbank besteht immer aus (mindestens ) zwei Dateien:
Einem Datenbank-File z.B. <dbname>.mdf und einem Transaction-Logfile <dbname>.ldf.
Übrigens: Zusätzlich besitzt jedes File einen logischen Namen. Diese können mit
select * from sys.database_files
ausgelesen werden (hier z.B. “awCMS_Data” und “awCMS_Log”):
Zurück zu unseren physischen Files: Das erste .mdf ist das primäre Datenbankfile. Bei großen Datenbanken kann es Sinn machten, die Daten auf mehrere Files (Filegroups) aufzuteilen – das bringt bei mehreren Laufwerken oft bessere Performance. Understanding Files and Filegroups und Files and Filegroups Architecture liefern übrigens die wichtigsten Informationen hierzu.
Das Transaction Log
Eine Funktion des SQL Servers ist, dass alle Datenbank-Aktionen zuerst in das Transaction-Logfile geschrieben werden und erst danach in das Datenbank-File. Die Daten aus dem Transaction Log werden erst durch eine COMMIT-Funktion in die Datenbank persistiert (dann, wenn die Datenbank-Transaktion vollständig ist). Daher stehen im Transactionlog also eigentlich nur “temporäre” Daten – eine Art Zwischenspeicher.
Das Transaction Log wird in kleinere Segmente unterteilt, diese heißen “Virtual Logs”.
SQL Server kümmert sich selbst um die Verwaltung des Transaction Logs. Nach dem Commit wird ein Checkpoint gesetzt und der verwendete Platz wird wieder freigegeben. Bereits gespeicherte Daten werden abgeschnitten (Truncated).
Je nach Recovery Model und Größe und Auslastung der Datenbank kann die Größe des Transaction Logs allerdings wachsen (und zwar immer um ein Vielfaches eines Virtual Log Blocks).
Transaktionen werden sequentiell geschrieben (siehe unten). Wenn der Platz begrenzt ist/das Ende erreicht ist, wird wieder am Anfang des Files fortgesetzt (Loop).
Das Datenmodell
Wichtig für den Betrieb und das Backup ist das gewählte Daten-Modell.
Für jede Datenbank kann (in den Eigenschaften der Datenbank) das Recovery Model eingestellt werden, die Vorgabe ist “Full”.
Diese SQL Abfrage liefert eine Übersicht über das Recovery Model aller Datenbanken:
SELECT name, recovery_model_desc FROM sys.databases
Ganz rasch: SIMPLE erfordert keine Log File Backups, FULL bietet höheren Schutz (Wiederherstellung) und erfordert Log File Backups.
Das bedeutet, dass das Datenbank Recovery Model je nach Anforderung sorgsam gewählt werden sollte. msdn liefert in Recovery Model Overview alle Informationen hierzu.
Bei Verwendung von FULL kann es also dazu kommen, dass das Transaction Log voll wird…
Tipp: Damit das Transaction Log nicht die Festplatte vollschreibt, empfiehlt es sich, das Transaction Log File zu beschränken. Das funktioniert in den Datenbank-Eigenschaften durch Setzen der File-Größe.
So sehen die Standard-Einstellungen aus:
Und so wird die Restricted File Growth auf einen eigenen maximalen Wert gesetzt:
Somit kann das Transaction Log für diese Datenbank nur bis 25MB wachsen. Beachten Sie, dass im Logfile genügend Platz für den laufenden Betrieb vorhanden ist!
Hinweis: Wenn Sie Database Mirroring (mehr dazu in einem folgenden Blog-Artikel…) einsetzen, können Sie das Recovery Model SIMPLE NICHT einsetzen (sondern FULL).
Tipp: Beachten Sie den Platzbedarf auch, wenn umfangreiche Datenbank-Operationen durchgeführt werden, beispielsweise wenn ein Maintenance Task alle Indizes in einer Datenbank erneuert…! Das kann leicht zu einem “Transaction Log is full” Fehler führen (so ist es auch bei einem Kunden passiert, als die Datenbank in der Nacht reorganisiert wurde - und am nächsten Tag in der Früh stand…)
Transaction Log is full
Wenn das Transaction Log voll ist verweigert SQL Server Abfragen und sogar Backups:
Executing the query "BACKUP DATABASE..." failed with the following error: "The transaction log for database 'cms' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
BACKUP DATABASE is terminating abnormally…
Was tun?
Die Antwort ist zum Glück einfach:
DBCC SQLPERF(logspace)
zeigt, wie viel Platz IM Logfile verbraucht wird:
In diesem Beispiel verwendet die Datenbank “cms” bereits 91% des verfügbaren Platzes. Ein Zeichen für einen möglichen Engpass.
Wie reduziert man also die Transactions? Per BACKUP des Transaction Logs!
BACKUP LOG [cms]
TO DISK = N'D:\BACKUP\cms.trn' WITH NOFORMAT, INIT,
NAME = N'cms-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
So wird das Transaction Log in das Backupfile cms.trn geschrieben:
Durch das Backup werden inaktive Transaktionen entfernt und das Transaction Log File somit wieder für Virtual Logs entleert.
Visualisierung - Log Kontrolle
Sehen wir nach dem Backup nochmals mit DBCC SQLPERF(logspace) nach:
Fein, die CMS Datenbank benötigt jetzt nur mehr 21% Platz im Transaction Log File. Hat funktioniert!
Weiters sehr hilfreich ist der TSQL-Befehl
DBCC LOGINFO
LogInfo zeigt an, welche Transactions in einer Datenbank (in einem Log File) vorhanden sind, welche committed sind (Status 0) und welche noch nicht committed sind (Status 2).
Status 2 zeigt offene Transaktionen an:
Die Spalte FSeqNo ist die laufende Transaktionsnummer – immer aufsteigend (sequentielles Schreiben), hier sind die Transaktionen 21990 und 21991 offen.
Durch Ausführen von BACKUP LOG werden alle offenen Transaktionen geschlossen! Bei neuerlichem Ausführen danach dürfen eigentlich nur mehr neue (die letzten) Transaktionen mit Status 2 markiert sein.
Wichtige Hinweise zum BACKUP
Mit Full oder Bulk-Logged Recovery Mode bleiben inaktive Transaktionen im Logfile! Und zwar so lange, bis ein Checkpoint erstellt wurde und ein Backup des Transaction Logs durchgeführt wurde!
Ein “Full Backup” entfernt also KEINE Transactions aus dem Transaction Log File, wie in diesem Beispiel:
BACKUP DATABASE [cms]
TO DISK = N'D:\Backup\cms.bak' WITH NOFORMAT, INIT,
NAME = N'Vollstaendig Datenbank Sicherung', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Ein Transaction Log Backup gibt zuvor benutzen Platz wieder frei, verkleinert das File aber NICHT, wie in diesem Beispiel:
BACKUP LOG [cms]
TO DISK = N'D:\BACKUP\cms.trn' WITH NOFORMAT, INIT,
NAME = N'cms-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Shrink File
Und es geht doch: Um ein Transaction Log File zu verkleinern, verwenden Sie in der aktuellen Datenbank:
SELECT name FROM sys.database_files
WHERE type_desc = 'LOG'
Und nun den logischen Dateinamen (in meinem Beispiel “awcms_log”) in ShrinkFile einsetzen:
DBCC SHRINKFILE ('awcms_log', 1000)
Setzt bzw. reduziert die physische Größe des Log Files auf 1GB.
Zur Vollständigkeit: Um unbenutzten Platz in Daten-Files freizugeben kann ShrinkDatabase verwendet werden:
DBCC SHRINKDATABASE ('cms', TRUNCATEONLY)
Hiermit wird der freie Platz am File-Ende freigegeben, aber das File nicht reorganisiert - siehe auch DBCC SHRINKDATABASE (Transact-SQL). z.B.:
Achtung: Die Datenbank sollte NICHT auf Auto-Shrink gesetzt sein, da bei kontinuierlichem Verkleinern durch starke Defragmentierung Performance-Probleme auftreten können! AutoShrink sollte also FALSE sein, so wie hier:
Wie vorbeugen?
Nachdem wir nun reagieren können, stellt sich die Frage, wie man vorbeugen kann, damit dieser Fall nicht eintritt.
Gut ist, bei Verwendung von Recovery Model FULL die Datenbank komplett zu sichern und danach das Transaction Log extra zu sichern. Damit wird das Transaction Log File verkleinert und kann wieder neu befüllt werden.
Tipp: Bei Bedarf (bei großen Datenbanken mit vielen Transaktionen) empfehle ich, stündliche oder minütliche Backups des Transaction Logs – das bringt auch eine gute Sicherheit, da die Datenbank so leicht zu einem beliebigen Zeitpunkt (zu jedem Zeitpunkt, wo ein Transaction Log gesichert wurde) wiederherstellbar ist.
Ein Backup-Verzeichnis eines Tages könnte also sinngemäß so aussehen:
cms.bak
cms_1.trn
cms_2.trn
cms_3.trn
cms_4.trn
Der Vorteil: Restore zu jedem der fünf Zeitpunkte und ein kleines operatives Log File.
Tipp: Unbedingt beachten, dass Transaction Log auch VOR und NACH Reorganisations-Tasks (wie Index Rebuild) durchgeführt werden sollten – vor allem, wenn die File Size mit einem eigenen Wert auf restricted gesetzt ist (oder der Festplattenplatz eng ist).
Damit sollte der “Transaction Log is full” Fehler der Vergangenheit angehören und Sie das Handwerkszeug zum Beheben und Warten von Transaction Log Files besitzen.
Über SQL Server Wartung gibt es noch viel zu sagen und zu berichten
– stay tuned. 