Neu SQL Frage zu Wiederherstellungsmodell, Full/Diff Backup und wachsender eazybusiness_log.ldf Datei

[John]

Hallo,

ich habe eine Frage zum SQL (Standard) Server.

Datenbank Wiederherstellungsmodell steht auf Vollständig weil das default war.
Es wird täglich ein Full und stündlich ein Diff Backup mit SQLBackupAndFTP gemacht und zwar nur mit diesem Tool eben damit die Full/Diff Integrität gewahrt bleibt.
Ich mache keine Transaktion Log Backups, einfach weil mir stündliche Sicherheit genügt.
Die Backups sind funktionsfähig, das ist getestet.

Trotzdem wächst die eazybusiness_log.ldf immer weiter.

Woran liegt das?
Merkt der SQL Server, daß ich eigentlich nicht alles gesichert habe, sondern nur bis zum Zeitpunkt des Diffs und schreibt deshalb immer weiter in die eazybusiness_log.ldf? Aber wieso wird dann nicht wenigstens zum Erstellzeitpunkt des Diffs oder des Fulls die log zurückgesetzt? Ich habe doch zu genau dem Zeitpunkt alle Daten für ein Restore.

Was ist hier die Lösung?
Wiederherstellungsmodell auf Einfach? Oder als Alternative in SQLBackupAndFTP auch die Transaktion Logs sichern?

John

 

[mh1]

Ans Transaktionsprotokoll wird einfach sequentiell angehängt, d.h. es wächst und wächst.... bis es abgeschnitten wird.

Dieses Abschneiden erfolgt

• mit aktiviertem Recovery Model FULL (in der deutschen Version: Vollständig) erst wenn das Transaktionsprotokoll gesichert wird (also mit BACKUP LOG ....)
• mit aktiviertem Recovery Model SIMPLE (in der deutschen Version: Einfach) bei jeder Sicherung der Datenbank (BACKUP DATABASE .....)

Nach deiner Beschreibung zu urteilen machst du immer nur BACKUP DATABASE und sicherst aber dein Transaktionsprotokoll nie, obwohl du REOVERY auf FULL gestellt hast.
D.h. dein Transaktionsprotokoll wächst ins Unendliche bzw. bis zu dem Punkt, wo dir der Server gegen die Wand fährt weil die Platte voll ist...

Was ist hier die Lösung?
Wiederherstellungsmodell auf Einfach? Oder als Alternative in SQLBackupAndFTP auch die Transaktion Logs sichern?

Beide Lösungen führen zum Ziel, dass dein Transaktionsprotokoll abgeschnitten wird.
Es kommt auf deine Backup-Strategie drauf an. U.a. die Datenbankgröße (wie lange dauert ein Backup?), oder auch was du bereit bist zu verlieren ...

Wir machen z.b. auch morgens ein Backup und dann stündlich am Tag ein differentielles Backup. Das Recovery Model hab ich auf SIMPLE (Einfach) gestellt.
D.h. wenn Hans Bär um 15:53 den Artikelstamm löscht, kann ich bestenfalls den Datenstand 15:00 wiederherstellen und die Artikel, die um 15:23 und um 15:52 angelegt worden sind weg.

Viele Hoster machen z.b. einmal im Monat ein vollständiges Backup. Täglich differentiell und alle 15 Minuten ein Backup vom Transaktionslog. Das Recovery Model ist dann auf FULL eingestellt.
D.h. wenn Hans Bär am 28. um 15:53 den Artikelstamm löscht, musst du das vollständige Backup vom 01. und das differentielle Backup vom 27., sowie Log Backup einspielen. Mehr Aufwand, aber du hast dann wieder alle Daten
du siehst, es kommt drauf an, was man erreichen will

 

[John]

Vielen Dank, dann ist es wie bereits vermutet und ich werde entsprechend Ändern...

 

[mh1]

@John hat oben geschrieben, dass sein Recovery Model deshalb auf FULL steht, weil es per Default bei der Wawi Installation so eingestellt wurde. Also ist das wohl bei vielen auch so.
Deshalb der Hinweis an alle:

Wenn das Recovery Model auf FULL steht (in den deutschen Versionen "Wiederherstellungsmodell: Vollständig"), ist eine Sicherung der Protokolldateien zwingend notwendig.
Ohne eine Sicherung des Transaktionsprotokolls wird die Protokolldatei immer weiterwachsen, bis Sie den gesamten Platz auf der Festplatte einnimmt und der SQL Server seinen Betrieb einstellt.

Wer also das Recovery Model Auf FULL stellt und sichert die Transaktionsprotokolle nicht, hat sich eine tickende Zeitbombe in sein System eingebaut.
Schuld daran ist aber nicht der SQL-Server an sich oder so, sondern einfach diese zugegeben weitverbreitete Konfigurationskombination, die wahrscheinlich jedem DB Admin schonmal über den Weg gelaufen ist.

Wem also seine zu festen Zeitpunkten erstellten Backups ausreichen, sollte das Recovery Model auf SIMPLE (deutsch: Wiederherstellungsmodell: EINFACH) stellen.

Manch einer denkt vielleicht "Ich will ja meine Daten im Falle eines Falles vollständig wiederherstellen, darum muss ich ja auch beim Wiederherstellungsmodell vollständig auswählen".
Dieser Gedanke ist aber falsch. Denn die Wiederherstellung der Daten aus einer Datensicherung erfolgt immer vollständig. (da muss also niemand Angst haben... )

Es geht hier um zwei verschiedene Dinge: Einmal die Wiederherstellung der Daten aus einer Datensicherung, also das Gegenteil von einem Backup. Das andere aber ist die Einstellung für die Wartung des Transaktionsprotokolls.
Im Englischen ist es ganz klar, dass es sich um zwei verschiedene Dinge handelt: Das Gegenteil vom BACKUP heißt RESTORE. Das ander Wort ist RECOVERY.
In der deutschen Übersetzung wird beides einfach mit demselben Wort übersetzt: Wiederherstellung. Und genau das führt immer wieder zu Verwirrungen und Fehlinterpretationen ....auch bei erfahrenen IT-Dienstleistern (da wird dann gerne über Recovery gesprochen, obwohl ein Restore gemeint ist )

Wie auch immer, die Einstellungen in diesem Bereich und vor allem auch die Backup Strategie sind wichtige Grundlagen für einen sicheren Betrieb des SQL-Servers.
Wer auf dem Gebiet unsicher ist, oder sich nicht selbst damit befassen will, findet in diesem Forum sicherlich Servicepartner, die das ganze auch gerade im Hinblick auf JTL richtig aufstellen.

 

[John]

So, heute das Problem bearbeitet.
Lustiger Effekt: Die log Datei ließ sich auch nach einer vollständigen Backupsequenz aus Full, Diff, Log nicht im SQL Studio verkleinern oder nur marginal.
Ich hab dann eine SELECT * FROM sys.databases gemacht, um den Status von log_reuse_wait bzw. log_reuse_wait_desc gemäß
https://learn.microsoft.com/en-us/p...08-r2/ms345414(v=sql.105)?redirectedfrom=MSDN
zu prüfen aber dasstand erwartungsgemäß auf 0.

Da ich sowie alle Clients getrennt und erst danach das Backup gemacht habe, wurde mir das zu doof und ich habe das Wiederherstellungsmodell auf EINFACH umgestellt. Danach erst ließ sich die Log per DBCC SHRINKFILE verkleinern.
Anschließend habe ich den DB Dienst neu gestartet, wieder auf Wiederherstellungsmodell VOLLSTÄNDIG geschaltet und nochmal den Dienst neu gestartet.
Ich werde erstmal beim VOLLSTÄNDIG bleiben und Backups des Logs machen, weil es weniger Daten erzeugt.

Aber wenn man da kein Auge drauf haben kann, ist EINFACH für viele User die sicherere Wahl..

 

[Christoph E.]

Lustiger Effekt: Die log Datei ließ sich auch nach einer vollständigen Backupsequenz aus Full, Diff, Log nicht im SQL Studio verkleinern oder nur marginal.

Hallo @John, schau am besten mal auf diese Website: "SQL Server Transaction Log Backup, Truncate and Shrink Operations", da ist das Zusammenspiel zwischen den Wiederherstellungsmodi FULL / SIMPLE und Truncation / Shrink gut erklärt.

 

[mh1]

So, heute das Problem bearbeitet.
Lustiger Effekt: Die log Datei ließ sich auch nach einer vollständigen Backupsequenz aus Full, Diff, Log nicht im SQL Studio verkleinern oder nur marginal.

Das Verkleinern mit SHRINKFILE im Rocvery Model FULL würde vorraussetzen, dass du einen CHECKPOINT nach dem letzten Backup ausführst, aber du konntest es ja siehe unten bereits lösen, in dem du remporär da Recovery Model umgestellt hast

Prinzipiell sollte man die Dateien aber nicht manuell verkleinern, oder besser gesagt man sollte genau wissen was man tut und welche Größe benötigt wird.
Weil wenn z.b. Autogrowth fürs LDF falsch eingestellt ist, hat man sich auch wieder einen ressourcenfressenden Käfer in den Server gesetzt.

 

[John]

Das Verkleinern mit SHRINKFILE im Rocvery Model FULL würde vorraussetzen, dass du einen CHECKPOINT nach dem letzten Backup ausführst

Hätte das nicht durch meine Backupsequenz aus Full, Diff, Log nach dem Abschluß des Log Backups automatisch passieren müßen?

 

[Christoph E.]

Hätte das nicht durch meine Backupsequenz aus Full, Diff, Log nach dem Abschluß des Log Backups automatisch passieren müßen?

Hallo @John, nach dem Log-Backup wird die Logdatei truncated. "Truncation" bedeutet aber nur, das die verwendeten VLFs als frei "markiert" werden. Der Speicherplatz bleibt weiterhin reserviert. Das macht der SQL Server deswegen, weil das Auto-Wachstum einer Datei während des laufenden Betriebes immer einen kurzzeitigen Performanceeinbruch bedeutet und deshalb möglichst vermieden werden sollte. Deswegen bleibt der Speicher weiterhin reserviert, um das Autoanwachsen zu vermeiden. Mit shrinken kann man den Speicher wirklich entfernen, so dass das Logfile wieder eine kleinere Grösse bekommt. Das hilft aber meistens nur kurzfristig, weil SQL Server früher oder später zu einem ähnlichen Speicherbedarf (wie vorher) im Logfile zurückkehren wird.

Shrinken kann allerdings dann Sinn machen, wenn (außergewöhnlich) umfangreiche Updates / Inserts / Deletes auf der DB erfolgt sind, so dass die Logdatei untypisch stark angewachsen ist und deshalb wieder auf einen kleineren Wert zurückgesetzt werden soll; im Normalfall sollte man es nicht regelmässig tun.
Bei Dir würde ich also ein 1-maliges Shrinken empfehlen, weil Dein Logfile aufgrund des lange fehlenden Log-Backups auf einen untypischen hohen Speicherplatz angewachsen ist.