Das ein Microsoft SQL-Datenbank-Server mitunter einiges an Ressourcen benötigt ist soweit bekannt. Durch Fehlkonfiguration kann es allerdings auch sehr leicht dazu kommen, das Datenträger bis zum Anschlag voll geschrieben werden. So geschehen bei einem Kunden, natürlich an Weihnachten und trotz mehrmaliger jahrelanger Hinweise, das etwas nicht stimmt.

Eines Vorweg: Ich bin kein profunder MS SQL-Admin, daher korrigiert mich, falls an den nachfolgenden Ausführungen etwas nicht stimmt. Konstruktives Feedback ist wie immer gern gesehen.

Die Ausgangslage

Besagter Kunde kümmert sich selbst um seinen MS SQL-Server, wir betreuen lediglich den Windows Server und greifen nur bei Bedarf (wie diesem Notfall) oder nach Aufforderung ein.

Bereits am 22. und 23.12. gab es vom Monitoring Alarme dass das Datenlaufwerk nahezu voll ist. Wir haben dann immer etwas Platz geschafft (alte Daten verschoben) und die VHDX (das Ganze läuft auf einem Hyper-V Cluster) vergrößert. Als am 24.12. es dann erneut zu einem Alarm kam und das Datenlaufwerk komplett voll war, war mehr als klar, das irgendwas sämtlichen Platz in Anspruch nimmt. Bei der anschließenden Analyse zeigte sich folgendes:

• Die Datenbank ist ca. 250 GB groß.
• Das Transaktionsprotokoll war zu diesem Zeitpunkt 850 GB groß.

Die Ursache für das viel zu große Transaktionsprotokoll ist einfach:

Bei der Kombination “Wiederherstellungsmodell: Vollständig” ohne Sicherung des Transaktionsprotokolls wächst dieses solange an, bis das kein Speicherplatz mehr frei ist. Genau das ist hier passiert. Interessant war allerdings, das die Datenbank nicht offline gegangen ist, obwohl 0 Byte mehr frei waren.

Das ärgerliche bei dieser Situation ist, das uns schon früher das viel zu große Transaktionsprotokoll aufgefallen ist, auch wenn es da noch kleiner war, und wir über Jahre hinweg immer wieder darauf hingewiesen haben, das da was nicht stimmen kann. Entweder wurde uns gesagt, das sei so da man viel im ERP macht oder wir erhielten des Öfteren überhaupt keine Antwort oder Reaktion.

Sofort-Maßnahmen

Um das Transaktionsprotokoll verkleinern zu können, muss man zunächst eine Sicherung eben dieses durchführen. Das war bei fehlenden Platz einfacher gesagt als getan. So wurde zunächst ein Netzlaufwerk (vom Backup-Server) direkt im SQL Server verbunden:

MS SQL Server: Datensicherung auf Netzlaufwerk

Nun konnte ein manuelles Backup des Logs erfolgen, bei besagter Größe nahm dies mehrere Stunden in Anspruch.

Als nächstes wurde eine Verkleinerung versucht, aber der Dialog schloss sofort, kein Status oder ähnliches. Hilfreicher war es, die Abfrage direkt im SQL Management Studio abzusetzen:

USE [ApDaten]
GO
DBCC SHRINKFILE (N'Data_Log' , 0, TRUNCATEONLY)
GO

Dort gab es auf der Registerkarte “Meldungen” folgendes zu Lesen:

Die Protokolldatei "2" (Data_Log) kann nicht verkleinert werden, da die logische Protokolldatei am Ende der Datei zurzeit verwendet wird.

Dazu findet man nicht wirklich viel bei einer Recherche, hilfreicher ist die englische Meldung:

Cannot shrink log file 2 (Data_Log) because the logical log file located at the end of the file is in use.

Da das Log noch in Verwendung ist, wird das wohl nichts mit einer Verkleinerung. Daher, und weil man vmtl. keine andere Wahl mehr hatte, das Wiederherstellungsmodell auf “Einfach” geändert und siehe da, die Verkleinerung war erfolgreich und schlagartig wurden 895 GB frei.

Aktuell ist das Transaktionsprotokoll ca. 36 GB groß, ob das so bleibt wird die Zeit zeigen.

Wie kam es zu der Fehlkonfiguration?

Vermutlich kam das Ganze so zu Stande:

• Jemand (Kunde, ERP-Consultant) hat die neue Datenbank angelegt, die Voreinstellung für das Wiederherstellungsmodell lautet dann “Vollständig”.
• Derjenige (Kunde, ERP-Consultant) der die Konfiguration des Wartungsplan, der die Datenbanksicherung enthält, vorgenommen hat, hat schlicht keine Sicherung für das Transaktionsprotokoll eingerichtet oder wahlweise vergessen (je nach Anforderung) das Wiederherstellungsmodell auf “Einfach” zu ändern.

Gewissermaßen kleine Ursache, große Wirkung.

Allerdings wirkt sich das auf weitere Punkte aus: Da wäre die Performance des SQL-Server, der Ressourcenbedarf des Servers, der lokale als auch der Cloud-Speicherplatz für das Backup und mehr. Gerade letzteres erzeugt bei ständig steigenden Platzbedarf monatliche Mehrkosten.

Wie geht es weiter?

Nach den Sofort-Maßnahmen gibt es Klärungsbedarf, welche Anforderungen oder Ansprüche der Kunde und/oder das ERP hinsichtlich des Wiederherstellungsmodells hat. Dementsprechend muss man weitere Schritte einleiten. Entweder es bleibt bei “Einfach” oder wenn es doch “Vollständig” sein soll, dann muss das SQL-Backup angepasst werden.

Wir ziehen aus der Situation und einem vermiesten Weihnachtsfest ebenfalls Konsequenzen um sowas in Zukunft vermeiden zu können. Die genauen Maßnahmen werden wir nach dem Betriebsurlaub intern erörtern. Es wird wohl darauf hinauslaufen (müssen), das wir Konfigurationen prüfen werden und dass das Monitoring hinsichtlich MS SQL-Server besser werden muss.

Was auch immer dabei heraus kommt, ich werde berichten.

Quellen

Armann Systems – Wiki – SQL Transaktionsprotokoll verkleinern <- Sehr gute Erklärung zu den Schritten im Management Studio zum Log-Backup und der Verkleinerung

MS SQL Tips – How to shrink the transaction log

Tibor Karaszi’s SQL Server pages – Large transaction log file

SQL Server Log Explorer – Shrink MS SQL Transaction Log File in the Database Easily

Grutzeck-Software – Grundlagen für die Datensicherung von AG-VIP SQL über den SQL Server <- Sehr gute Erklärung zu den Wiederherstellungs-Modi und Backup-Strategie

stackoverflow – DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Stack Exchange – How can I reduce the size of a huge LDF file? [duplicate]

Andy

Verheiratet, Vater von zwei Kindern, eines an der Hand, eines im Herzen. Schon immer Technik-Freund, seit 2001 in der IT tätig und seit über 10 Jahren begeisterter Blogger. Mit meiner Firma IT-Service Weber kümmern wir uns um alle IT-Belange von gewerblichen Kunden und unterstützen zusätzlich sowohl Partner als auch Kollegen.