TechNet Team Blog Austria

Informationen und News abseits der offiziellen TechNet vom TechNet Team Blog Austria

SQL Server: The transaction log for database is full

SQL Server: The transaction log for database is full

  • Comments 2
  • Likes

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 12 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.

db_files

Ü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”):

sql_logical_names

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_virtual_log

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”.

db_recovery_model

Diese SQL Abfrage liefert eine Übersicht über das Recovery Model aller Datenbanken:

SELECT name, recovery_model_desc FROM sys.databases

db_recovery_model_sql

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:

logfile_properties_autogrowth

Und so wird die Restricted File Growth auf einen eigenen maximalen Wert gesetzt:

logfile_properties_fixed_size

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:

logfile_usage

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:

sql_backup_trn

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:

sql_backup_trn_result

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).

sql_loginfo

Status 2 zeigt offene Transaktionen an:

sql_loginfo2

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.:

db_shrinkfile

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:

db_autoshrink 

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. Zwinkerndes Smiley



Comments
  • <p>Hallo,</p> <p>Anstatt das TxLog mit einer fixen maximalen Größe zu versehen, macht es mehr Sinn hier als &quot;letzten Ausweg&quot; ein AutoGrowth des Logs zuzulassen. Besser das TxLog macht ein AutoGrowth, als dass die gesamte Datenbank steht.</p> <p>Und dann sollte man die initiale Größe des TxLog so setzen, dass der SQL Server nicht in Versuchung kommt, ein AutoGrowth durchzuführen. Wenn die Größe richtig gesetzt ist, und meine TxLog-Backups oft genug durchgeführt werden, kann der SQL Server beim TxLog ein Rollover durchführen, und somit wieder am Anfang des TxLog mit dem Schreiben von TxLog-Records beginnen. Wenn das TxLog richtig für meine Workload dimensioniert ist, sollte der SQL Server *niemals* beim TxLog ein AutoGrowth durchführen. Wenn es trotzdem der Fall ist, unbedingt die initiale Größe des TxLogs nochmals überdenken! AutoGrowth führt auch zu Fragmentierung und dadurch zu Performance-Einbußen. Zusätzlich muss der SQL Server auch die neu angelegten VLFs (Anzahl hängt von der Größe des angehängten neuen Chunks ab) mit Nullen überschreiben. Das ist eine synchrone Operations, dh. dass meine Datenbank während des AutoGrowths im TxLog STEHT und WARTEN muss!!!</p> <p>Wenn ich Database Mirroring nicht verwende, macht es Sinn, für INDEX REBUILD etc. *temporär* in das Recovery Model BULK LOGGED zu wechseln, weil hier nicht die kompletten Operations im TxLog gelogged werden, sondern nur die Extents, die durch die Bulk Logged Operations geändert wurden. Anschließend aber immer wieder in das Recovery-Model FULL zurückwechseln.</p> <p>HTH</p> <p>Klaus Aschenbrenner - SQL Server Consulting</p> <p><a rel="nofollow" target="_new" href="http://www.csharp.at">http://www.csharp.at</a></p> <p><a rel="nofollow" target="_new" href="http://twitter.com/Aschenbrenner">twitter.com/Aschenbrenner</a></p>

  • <p>Hi Klaus,</p> <p>super, danke für Deine Infos und Tipps dazu!</p> <p>Ja, AutoGrowth ist meistens die bessere Wahl. In meinem Szenario war es so, dass auf der Maschine mehrere DBs laufen und die Festplatte vom TxLog einer DB voll war. Damit stehen dann auch die anderen DBs, daher macht die Beschränkung in diesem Fall Sinn - solange nicht eine optimale TxLog Größe verwendet wird. Jeder DB-Admin muss ja in seiner Umgebung und nach Anforderungen entscheiden, wie die Ressourcen optimal verwendet werden.</p> <p>Aber ich bin bei Dir, AutoGrowth ist sicher die bessere Alternative - wenn die TxLog Größe richtig gesetzt ist. Dadurch, dass AutoGrowth synchron läuft kann das natürlich eine Performance-Bremse sein.</p> <p>Die Kunst dabei ist also, die _richtige_ initiale Größe fürs TxLog zu ermitteln, damit das Log nicht über die _gewünschte_ Größe wächst. Ich habe das bislang mit Analyse und try &amp; error zu ermitteln versucht. Hast du einen besseren Weg dazu (Profiler, Performance Monitor, ...)? </p> <p>Thx, Toni</p>

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment