Wie viel Arbeit darf verloren gehen?
SIMPLE spart keine Arbeit und kaum Platz, es tauscht nur die Protokollpflege gegen den Verzicht auf die Wiederherstellung zu einem Zeitpunkt deiner Wahl.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
SIMPLE wirkt sparsam und ist eine Entscheidung über den Ernstfall
Die Umstellung passiert fast immer aus demselben Anlass: Eine Protokolldatei ist gewachsen, die Platte wird knapp, jemand sucht nach der schnellsten Lösung und findet den Hinweis, dass SIMPLE das Problem beseitigt. Das stimmt sogar. Die Engine schneidet das Protokoll dann bei jedem Prüfpunkt selbst zurück, die Datei bleibt klein, das Ticket ist geschlossen. Was in diesem Moment niemand notiert: Ab sofort ist die Wiederherstellung zu einem beliebigen Zeitpunkt nicht mehr möglich, für keine Sicherung, die danach entsteht.
Der Preis wird erst im Schadensfall fällig. Um 14:40 Uhr löscht ein Auftrag ohne WHERE-Bedingung die Positionen aus einem Auftragskopf, um 15:10 fällt es auf. Mit FULL sicherst du das Protokollende, spielst die letzte Vollsicherung ein und stoppst mit STOPAT um 14:39 Uhr. Verloren ist eine Minute. Mit SIMPLE gibt es genau einen Stand, nämlich die Nacht davor, und damit ist der komplette Vormittag weg, für jeden, der an diesem Tag etwas eingegeben hat. Der Aufwand, diese Stunden zu rekonstruieren, übersteigt die Kosten für den Plattenplatz und die Protokollsicherung um ein Vielfaches.
Der Platzvorteil ist zudem kleiner, als er wirkt. Auch unter SIMPLE muss das Protokoll jede laufende Transaktion vollständig fassen, ein großes DELETE über Millionen Zeilen lässt die Datei genauso wachsen. Gespart wird nur der Anteil zwischen zwei Protokollsicherungen, und dieser Anteil ist eine Frage des Sicherungsintervalls, nicht des Modells. Wer alle fünfzehn Minuten sichert, hält die Protokolldatei klein und behält die Wiederherstellung zum Zeitpunkt seiner Wahl.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die drei Modelle an den Fragen, die im Ernstfall zählen
Verglichen wird nicht die Menge geschriebener Protokolldaten, sondern was in den Situationen passiert, in denen das Modell überhaupt eine Rolle spielt: nach einem versehentlichen Löschen, bei einem Plattenausfall und während eines großen Ladevorgangs.
SIMPLE
die Engine räumt das Protokoll beim Prüfpunkt selbst auf, Protokollsicherungen sind ausgeschlossen
FULL
jede Änderung bleibt im Protokoll, bis du es sicherst, dafür kommst du auf die Sekunde genau zurück
BULK_LOGGED
wie FULL, nur werden Massenvorgänge minimal protokolliert, gedacht als Schalter auf Zeit
| Entscheidungsfrage | SIMPLE | FULL | BULK_LOGGED |
|---|---|---|---|
| Wie weit kommst du nach einem versehentlichen DELETE um 14:40 Uhr zurück? | Schwäche Bis zur letzten Voll- oder differenziellen Sicherung, also in der Regel bis zur vergangenen Nacht. Alles danach ist nachzuarbeiten. | Stärke Auf 14:39 Uhr. Du sicherst das Protokollende, stellst die Vollsicherung mit NORECOVERY her und beendest die Kette mit STOPAT. | Kommt darauf an Genauso genau, solange in der betroffenen Protokollsicherung kein minimal protokollierter Vorgang steckt. Steckt einer darin, ist STOPAT innerhalb dieser Sicherung nicht möglich. |
| Was passiert mit der Protokolldatei im laufenden Betrieb? | Stärke Der Bereich hinter der ältesten offenen Transaktion wird beim Prüfpunkt zur Wiederverwendung freigegeben, die Datei bleibt weitgehend stabil. | Kommt darauf an Sie wächst, bis eine Protokollsicherung läuft. Ohne geplante Sicherung wächst sie so lange, bis die Platte voll ist und Fehler 9002 erscheint. | Kommt darauf an Dasselbe Verhalten wie FULL. Nur die Protokolldatei bleibt bei Massenvorgängen kleiner, die Protokollsicherung wird dafür deutlich größer. |
| Wie groß ist der Betriebsaufwand? | Stärke Vollsicherung und differenzielle Sicherung genügen, es gibt keine Protokollkette, die reißen könnte. | Kommt darauf an Eine zusätzliche geplante Protokollsicherung, deren Ausfall auffallen muss, und eine Aufbewahrung, die zur Sicherungskette passt. | Schwäche Derselbe Aufwand wie FULL, dazu die Pflicht, das Umschalten und Zurückschalten samt Protokollsicherung an den Ladevorgang zu koppeln. |
| Lässt sich eine Verfügbarkeitsgruppe oder ein Protokollversand betreiben? | Schwäche Nein. Always On, Protokollversand und Datenbankspiegelung setzen sämtlich FULL voraus, weil sie auf dem Protokollstrom aufsetzen. | Stärke Ja, das ist die Voraussetzung dafür. Der Protokollstrom ist genau der Mechanismus, über den die zweite Kopie versorgt wird. | Kommt darauf an Technisch möglich, aber unüblich, weil minimal protokollierte Vorgänge das Nachziehen der Kopie erschweren und den Datenstrom aufblähen. |
| Wie schnell läuft ein Index-Neuaufbau über eine große Tabelle? | Stärke Schnell, solange der Neuaufbau offline läuft, denn dann wird er minimal protokolliert und die Protokolldatei wächst kaum. Ein Neuaufbau mit ONLINE = ON wird dagegen in jedem Modell vollständig protokolliert, dort gibt es diesen Vorteil nicht. | Schwäche Langsamer, und die Protokolldatei wächst ungefähr auf die Größe des neu aufgebauten Index, weil jede Seite vollständig protokolliert wird. | Stärke Bei einem Neuaufbau offline genauso schnell wie unter SIMPLE, und die Wiederherstellungskette bleibt trotzdem erhalten. Genau dafür ist das Modell gedacht. Mit ONLINE = ON entfällt der Vorteil, weil solche Vorgänge in jedem Modell vollständig protokolliert werden. |
| Was passiert, wenn jemand das Modell heimlich umstellt? | Schwäche Der Wechsel von FULL nach SIMPLE bricht die Protokollkette sofort und ohne Warnung. Bestehende Protokollsicherungen schlagen danach fehl. | Kommt darauf an Der Wechsel nach FULL wirkt erst mit der nächsten Voll- oder differenziellen Sicherung, bis dahin verhält sich die Datenbank weiter wie unter SIMPLE. | Kommt darauf an Der Wechsel ist unauffällig und jederzeit möglich, fällt aber später auf, wenn STOPAT in genau der betroffenen Protokollsicherung verweigert wird. |
Wie weit kommst du nach einem versehentlichen DELETE um 14:40 Uhr zurück?
Bis zur letzten Voll- oder differenziellen Sicherung, also in der Regel bis zur vergangenen Nacht. Alles danach ist nachzuarbeiten.
Auf 14:39 Uhr. Du sicherst das Protokollende, stellst die Vollsicherung mit NORECOVERY her und beendest die Kette mit STOPAT.
Genauso genau, solange in der betroffenen Protokollsicherung kein minimal protokollierter Vorgang steckt. Steckt einer darin, ist STOPAT innerhalb dieser Sicherung nicht möglich.
Was passiert mit der Protokolldatei im laufenden Betrieb?
Der Bereich hinter der ältesten offenen Transaktion wird beim Prüfpunkt zur Wiederverwendung freigegeben, die Datei bleibt weitgehend stabil.
Sie wächst, bis eine Protokollsicherung läuft. Ohne geplante Sicherung wächst sie so lange, bis die Platte voll ist und Fehler 9002 erscheint.
Dasselbe Verhalten wie FULL. Nur die Protokolldatei bleibt bei Massenvorgängen kleiner, die Protokollsicherung wird dafür deutlich größer.
Wie groß ist der Betriebsaufwand?
Vollsicherung und differenzielle Sicherung genügen, es gibt keine Protokollkette, die reißen könnte.
Eine zusätzliche geplante Protokollsicherung, deren Ausfall auffallen muss, und eine Aufbewahrung, die zur Sicherungskette passt.
Derselbe Aufwand wie FULL, dazu die Pflicht, das Umschalten und Zurückschalten samt Protokollsicherung an den Ladevorgang zu koppeln.
Lässt sich eine Verfügbarkeitsgruppe oder ein Protokollversand betreiben?
Nein. Always On, Protokollversand und Datenbankspiegelung setzen sämtlich FULL voraus, weil sie auf dem Protokollstrom aufsetzen.
Ja, das ist die Voraussetzung dafür. Der Protokollstrom ist genau der Mechanismus, über den die zweite Kopie versorgt wird.
Technisch möglich, aber unüblich, weil minimal protokollierte Vorgänge das Nachziehen der Kopie erschweren und den Datenstrom aufblähen.
Wie schnell läuft ein Index-Neuaufbau über eine große Tabelle?
Schnell, solange der Neuaufbau offline läuft, denn dann wird er minimal protokolliert und die Protokolldatei wächst kaum. Ein Neuaufbau mit ONLINE = ON wird dagegen in jedem Modell vollständig protokolliert, dort gibt es diesen Vorteil nicht.
Langsamer, und die Protokolldatei wächst ungefähr auf die Größe des neu aufgebauten Index, weil jede Seite vollständig protokolliert wird.
Bei einem Neuaufbau offline genauso schnell wie unter SIMPLE, und die Wiederherstellungskette bleibt trotzdem erhalten. Genau dafür ist das Modell gedacht. Mit ONLINE = ON entfällt der Vorteil, weil solche Vorgänge in jedem Modell vollständig protokolliert werden.
Was passiert, wenn jemand das Modell heimlich umstellt?
Der Wechsel von FULL nach SIMPLE bricht die Protokollkette sofort und ohne Warnung. Bestehende Protokollsicherungen schlagen danach fehl.
Der Wechsel nach FULL wirkt erst mit der nächsten Voll- oder differenziellen Sicherung, bis dahin verhält sich die Datenbank weiter wie unter SIMPLE.
Der Wechsel ist unauffällig und jederzeit möglich, fällt aber später auf, wenn STOPAT in genau der betroffenen Protokollsicherung verweigert wird.
Was passt wann
- Wenn die Datenbank fachliche Daten führt, die Menschen im Lauf des Tages eingeben
- nimm FULL und richte die Protokollsicherung im selben Arbeitsgang ein, typischerweise alle fünfzehn Minuten.
- Wenn die Datenbank jede Nacht vollständig neu befüllt wird, etwa eine Auswertungsschicht oder eine Kopie zum Lesen
- ist SIMPLE die richtige Wahl, denn der Weg zurück führt ohnehin über den Ladevorgang und nicht über das Protokoll.
- Wenn ein großer Ladevorgang oder ein Index-Neuaufbau ansteht und die Kette nicht reißen darf
- schalt vorher auf BULK_LOGGED, sichere danach das Protokoll und schalt sofort wieder auf FULL zurück.
Fünf Fragen, und das Modell steht fest
- 01 Darf ein Ausfall die Arbeit seit der letzten Nacht kosten? Dann reicht SIMPLE.
- 02 Soll die Rückkehr auf eine bestimmte Minute möglich sein? Dann brauchst du FULL.
- 03 Gibt es eine Verfügbarkeitsgruppe oder Protokollversand? Dann ist FULL Pflicht.
- 04 FULL gesetzt, aber keine Protokollsicherung geplant? Dann wächst die Datei.
- 05 Steht ein großer Ladevorgang an? Dann ist BULK_LOGGED der Schalter für ein paar Stunden.
Was du danach in fünf Minuten entscheidest
Die Frage lautet nicht, welches Modell besser ist, sondern wie viel Datenverlust für diese eine Datenbank vertretbar ist und wer diese Grenze festlegen darf. Aus der Antwort folgt das Modell, und aus dem Modell folgt der Betriebsaufwand. In dieser Reihenfolge, nicht umgekehrt.
Den zulässigen Datenverlust benennen
Du klärst mit der Fachabteilung, wie viele Stunden Arbeit im Notfall nachgearbeitet werden können. Diese Zahl ist eine fachliche Entscheidung, keine technische, und sie legt Modell und Sicherungsintervall fest.
Die Protokollsicherung einplanen
Unter FULL richtest du die Protokollsicherung im selben Arbeitsgang ein wie die Umstellung. Zwischen beiden Schritten darf keine Nacht liegen, sonst ist die Umstellung genau die Ursache für die volle Platte, die alle fürchten.
Die Protokollkette verstehen
Du weißt, dass ein Wechsel nach SIMPLE die Kette bricht und dass nach dem Zurückschalten auf FULL erst eine Voll- oder differenzielle Sicherung nötig ist, bevor Protokollsicherungen wieder etwas nützen.
Ein volles Protokoll richtig lesen
Du fragst log_reuse_wait_desc in sys.databases ab, statt zu raten. Der Wert LOG_BACKUP bedeutet fehlende Protokollsicherung, ACTIVE_TRANSACTION eine offene Transaktion, REPLICATION eine hängende Verteilung. Jede Ursache hat eine eigene Antwort.
Bulk Logged gezielt einsetzen
Für einen großen Index-Neuaufbau oder ein Massenladen schaltest du für die Dauer des Vorgangs auf BULK_LOGGED, sicherst danach das Protokoll und schaltest zurück. Als Dauerzustand bringt das Modell nur Nachteile.
Die Wiederherstellung wirklich üben
Du spielst eine Sicherung mit STOPAT auf einer Testinstanz zurück und misst, wie lange das dauert. Erst danach weißt du, ob dein Notfallplan die zugesagte Zeit einhält, und nicht erst am Tag des Vorfalls.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Was beim Umschalten mit der Protokollkette geschieht
Die Protokollkette ist die lückenlose Folge von Protokollsicherungen ab der letzten Vollsicherung. Nur wenn jedes Glied vorhanden ist, kann der SQL Server die Änderungen bis zu einem gewünschten Zeitpunkt nachspielen. Der Wechsel nach SIMPLE zerschneidet diese Kette in dem Moment, in dem er ausgeführt wird, weil ab sofort Protokollbereiche überschrieben werden, die noch nicht gesichert waren. Es gibt keine Warnung, keinen Eintrag im Fehlerprotokoll, der wie ein Problem aussieht, und keinen Weg zurück.
Beim Zurückschalten auf FULL entsteht die Kette nicht von selbst neu. Bis zur nächsten Voll- oder differenziellen Sicherung verhält sich die Datenbank weiter so, als wäre sie im einfachen Modell, das nennt sich Pseudo-Simple. Eine Protokollsicherung in dieser Phase schlägt mit dem Hinweis fehl, dass noch keine Datenbanksicherung vorliegt. Wer nach der Umstellung nicht sofort eine Vollsicherung zieht, hat also FULL eingestellt und trotzdem keine Wiederherstellung zum Zeitpunkt seiner Wahl.
Der umgekehrte Fall verdient dieselbe Aufmerksamkeit. Jede neue Datenbank erbt ihr Modell von der Systemdatenbank model, und dort steht in den üblichen Editionen FULL. Eine Datenbank, die jemand für einen Test anlegt und vergisst, läuft damit im vollständigen Modell ohne jede Protokollsicherung. Das ist der häufigste Weg, auf dem eine Protokolldatei von wenigen Megabyte auf mehrere hundert Gigabyte anwächst, ohne dass irgendjemand etwas Falsches getan hätte.
Ein volles Protokoll hat immer genau eine Ursache
Die erste Abfrage bei einer wachsenden Protokolldatei lautet SELECT name, log_reuse_wait_desc FROM sys.databases. Der Wert in dieser Spalte benennt den Grund, aus dem der SQL Server den Protokollbereich nicht zur Wiederverwendung freigeben kann, und jeder Wert hat eine eigene Antwort. LOG_BACKUP heißt: Es fehlt eine Protokollsicherung, und zwar nur diese. ACTIVE_TRANSACTION heißt: Eine Transaktion ist seit Stunden offen, meist weil eine Anwendung ein COMMIT vergessen hat oder eine Sitzung mit offener Transaktion schläft.
Weitere Werte, die im Alltag auftauchen: REPLICATION oder AVAILABILITY_REPLICA bedeuten, dass die Verteilung oder eine sekundäre Kopie hinterherhinkt und deshalb den Protokollbereich festhält. CHECKPOINT bedeutet, dass unter SIMPLE seit dem letzten Prüfpunkt viel geschrieben wurde und der nächste noch aussteht. Bei ACTIVE_TRANSACTION liefert DBCC OPENTRAN die älteste offene Transaktion samt Sitzungsnummer, und sys.dm_tran_session_transactions verbindet sie mit der Anwendung, die sie geöffnet hat.
Das reflexhafte Verkleinern mit DBCC SHRINKFILE ist an dieser Stelle die schlechteste Antwort. Solange die Ursache besteht, gibt die Datei ohnehin keinen Platz frei, und wenn sie es tut, wächst sie beim nächsten Lauf wieder an derselben Stelle. Jedes Wachstum kostet Zeit, weil der SQL Server neue Protokollbereiche im Gegensatz zu Datendateien mit Nullen füllt, seit SQL Server 2022 immerhin nicht mehr bei Schritten bis 64 Megabyte. Der Wechsel aus Verkleinern und Wachsen erzeugt zudem eine große Zahl kleiner virtueller Protokolldateien, was die Wiederherstellung nach einem Neustart spürbar verlangsamt. Verkleinern ist einmalig nach einem außergewöhnlichen Vorgang vertretbar, danach setzt du die Datei auf eine Größe, die den normalen Betrieb trägt.
Bulk Logged ist ein Schalter, kein Zustand
Unter BULK_LOGGED protokolliert der SQL Server bestimmte Vorgänge minimal: das Massenladen mit BULK INSERT oder bcp, SELECT INTO, den Neuaufbau von Indizes im Offlinebetrieb und einige weitere. Statt jeder einzelnen Zeile werden nur die betroffenen Bereiche vermerkt. Der Vorgang läuft dadurch schneller und die Protokolldatei bleibt klein, die Wiederherstellungskette bleibt aber im Gegensatz zu SIMPLE erhalten. Die Einschränkung ist wichtig: Sobald ein Index mit ONLINE = ON neu gebaut wird, protokolliert der Server vollständig, und zwar in jedem Modell.
Der Haken steckt in der nächsten Protokollsicherung. Damit die minimal protokollierten Änderungen wiederherstellbar sind, liest der SQL Server die betroffenen Datenseiten aus und packt sie in die Protokollsicherung hinein. Diese Sicherung wird dadurch groß, unter Umständen größer als das Protokoll unter FULL gewesen wäre. Der Gewinn liegt bei der Protokolldatei und bei der Laufzeit des Ladevorgangs, nicht beim Speicherbedarf der Sicherungen.
Der eigentliche Grund, dieses Modell nicht dauerhaft zu setzen, ist ein anderer: Innerhalb einer Protokollsicherung, die minimal protokollierte Vorgänge enthält, ist STOPAT nicht möglich. Du kannst diese Sicherung nur ganz oder gar nicht einspielen. Passiert das versehentliche Löschen genau in diesem Zeitfenster, ist die punktgenaue Rückkehr weg, obwohl formal alles gesichert war. Deshalb lautet das Muster: umschalten, laden, Protokoll sichern, zurückschalten, und zwar im selben Auftrag und nicht als drei Aufgaben für drei verschiedene Wochen.
Die Wiederherstellung auf einen Zeitpunkt in der Reihenfolge, in der sie läuft
Der erste Griff im Ernstfall gilt nicht der alten Sicherung, sondern dem Protokollende. Mit BACKUP LOG ... WITH NORECOVERY sicherst du den Teil, der noch nicht gesichert ist, und versetzt die Datenbank dabei in den wiederherstellenden Zustand. Wer diesen Schritt auslässt und direkt mit der Wiederherstellung beginnt, verliert genau die Änderungen zwischen der letzten geplanten Protokollsicherung und dem Vorfall.
Danach folgt die Kette: RESTORE DATABASE aus der Vollsicherung mit NORECOVERY, falls vorhanden die jüngste differenzielle Sicherung mit NORECOVERY, anschließend alle Protokollsicherungen der Reihe nach mit NORECOVERY. Nur die letzte bekommt WITH STOPAT = '2026-08-16 14:39:00' und RECOVERY. Der Zeitpunkt ist dabei kein Wunsch, sondern eine Grenze: Alle Transaktionen, die bis dahin bestätigt waren, sind drin, alles danach ist weg, und offene Transaktionen werden zurückgerollt.
Zwei Details, die im Ernstfall Zeit kosten. Erstens ist die Wiederherstellung in eine Kopie mit anderem Namen fast immer der bessere Weg, weil du dann die gelöschten Zeilen aus der Kopie zurückschreiben kannst, statt den ganzen Tag zu verwerfen. Dafür brauchst du MOVE für Daten- und Protokolldatei. Zweitens kannst du dir mit RESTORE HEADERONLY den Inhalt einer Sicherungsdatei ansehen und mit RESTORE VERIFYONLY die Lesbarkeit prüfen. Eine Sicherung, die noch nie zurückgespielt wurde, ist eine Vermutung, keine Sicherung, und der einzige Weg, das zu ändern, ist eine Übung auf einer Testinstanz mit gestoppter Uhr.
Dazu passende Kurse
Wenn du Sicherung und Wiederherstellung einmal vollständig durchspielen willst, statt sie im Ernstfall zum ersten Mal zu üben, führen dich die SQL-Server-2017-Kurse für Administratoren durch genau diese Abläufe.
Wer verstehen will, warum ein Protokoll überhaupt existiert und was eine Transaktion zusammenhält, findet die Grundlagen zu Transaktionen und Datenbankbetrieb als soliden Unterbau.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Verkleinert das Umschalten auf SIMPLE meine Protokolldatei?
Reicht eine tägliche Vollsicherung, wenn ich FULL eingestellt habe?
Welches Modell brauchen die Systemdatenbanken?
Wie oft sollte die Protokollsicherung laufen?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Sicherungsstrategie, die im Ernstfall hält, was sie verspricht
Bei cmt spielst du die Wiederherstellung auf einen Zeitpunkt selbst durch, samt Sicherung des Protokollendes und der Frage, wie lange das bei deiner Datenmenge dauert.