3
Transaktionsprotokoll Microsoft SQL Server: Automatisches Wachstum, automatisches Schrumpfen, Fragmentierung
Hallo,
ich habe mal ein paar Fragen zum Transaktionsprotokoll des Microsoft SQL-Server.
Wenn ich beim Anlegen einer Datenbank (Recoverymodel: Simple) das Transaktionsprotokoll auf einen Anfangswert setze und das automatische Wachsen abschalte, kann es passieren, das bei irgendwelchen Aktionen in der Datenbank das Protokoll überläuft. Schlecht!
Nun kann man das Protokoll auf automatisches Wachstum konfigurieren, in einer Anleitung habe ich gefunden man sollte in 64MB Schritten erhöhen weil dies zu einer internen Struktur passen würde, soweit in Ordnung. Nun kann auch im Fall einer sehr großen Menge an Änderungen in der Datenbank (fast) nichts mehr passieren, das Transaktionsprotokoll kann mit wachsen, nach erreichen des checkpoints wird das Protokoll abgeschnitten und alles ist gut. Fast.
Das Transaktionsfile ist immer noch sehr groß. Jetzt stellt sich mir die Frage: Welche Nachteile hat ein nachträglich gewachsenes Transaktionsprotokoll ausser das es Platz auf der Platte versperrt? wird es durch Fragmentierung der Datei auf der Platte oder Fragmentierung innerhalb der Datenstrukturen langsamer als das ursprüngliche Protokoll?
Kann es durch irgendeine automatische Aktion wieder verkleinert werden? (ohne DBCC Shrinkfile) Die Option AutoShrink betrifft ja die ganze Datenbank, nicht nur das Transaktionsprotokoll.
Grundsätzlich noch eine Frage:
Ist Fragmentierung einer Datenbankdatei auf einer SSD noch ein Thema oder ist das durch den schnellen wahlfreien Zugriff in den Hintergrund gerückt?
Ich hoffe ich habe meine Frage klar genug formuliert
vielen Dank schon im Voraus
Armin
ich habe mal ein paar Fragen zum Transaktionsprotokoll des Microsoft SQL-Server.
Wenn ich beim Anlegen einer Datenbank (Recoverymodel: Simple) das Transaktionsprotokoll auf einen Anfangswert setze und das automatische Wachsen abschalte, kann es passieren, das bei irgendwelchen Aktionen in der Datenbank das Protokoll überläuft. Schlecht!
Nun kann man das Protokoll auf automatisches Wachstum konfigurieren, in einer Anleitung habe ich gefunden man sollte in 64MB Schritten erhöhen weil dies zu einer internen Struktur passen würde, soweit in Ordnung. Nun kann auch im Fall einer sehr großen Menge an Änderungen in der Datenbank (fast) nichts mehr passieren, das Transaktionsprotokoll kann mit wachsen, nach erreichen des checkpoints wird das Protokoll abgeschnitten und alles ist gut. Fast.
Das Transaktionsfile ist immer noch sehr groß. Jetzt stellt sich mir die Frage: Welche Nachteile hat ein nachträglich gewachsenes Transaktionsprotokoll ausser das es Platz auf der Platte versperrt? wird es durch Fragmentierung der Datei auf der Platte oder Fragmentierung innerhalb der Datenstrukturen langsamer als das ursprüngliche Protokoll?
Kann es durch irgendeine automatische Aktion wieder verkleinert werden? (ohne DBCC Shrinkfile) Die Option AutoShrink betrifft ja die ganze Datenbank, nicht nur das Transaktionsprotokoll.
Grundsätzlich noch eine Frage:
Ist Fragmentierung einer Datenbankdatei auf einer SSD noch ein Thema oder ist das durch den schnellen wahlfreien Zugriff in den Hintergrund gerückt?
Ich hoffe ich habe meine Frage klar genug formuliert
vielen Dank schon im Voraus
Armin
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 399737
Url: https://administrator.de/contentid/399737
Printed on: April 19, 2024 at 18:04 o'clock
3 Comments
Latest comment
Hi
die Wachstumsrate richtet sich an die Anforderung was man z.B. je Woche erwartet, wir haben den Wert auf 50GB liegen bei einigen Datenbanken, was zu einer deutliche geringeren IO Last führt, aber immer abhängig von der Wachstumsrate. Auch ist die Fragmentierung geringer wenn du größere Blöcke verwendest und die Optmierungsläufe laufen deutlich schneller durch.
Belegter Speicher durch ungenutzte Blöcke machen nichts, das bekommt auch das Backup entsprechend mit. Wir sichern die Logs alle 60 Minuten um im Fehlerfall max. 1h Differenz zu haben und nicht ein Vollbackup vom Vorabend nehmen müssen.
Das verkleinern von Datenbank oder Log Dateien sollte man nur nach Migrations oder großen Transaktionen vornehmen die sehr selten vorkommen (1-2 x im Jahr oder weniger), ansonsten würde ich es lassen.
Gruß
@clSchak
die Wachstumsrate richtet sich an die Anforderung was man z.B. je Woche erwartet, wir haben den Wert auf 50GB liegen bei einigen Datenbanken, was zu einer deutliche geringeren IO Last führt, aber immer abhängig von der Wachstumsrate. Auch ist die Fragmentierung geringer wenn du größere Blöcke verwendest und die Optmierungsläufe laufen deutlich schneller durch.
Belegter Speicher durch ungenutzte Blöcke machen nichts, das bekommt auch das Backup entsprechend mit. Wir sichern die Logs alle 60 Minuten um im Fehlerfall max. 1h Differenz zu haben und nicht ein Vollbackup vom Vorabend nehmen müssen.
Das verkleinern von Datenbank oder Log Dateien sollte man nur nach Migrations oder großen Transaktionen vornehmen die sehr selten vorkommen (1-2 x im Jahr oder weniger), ansonsten würde ich es lassen.
Gruß
@clSchak
Hi
beim Recovery Simple sollte eigentlich kaum was auf dem Logfile passieren.
Aber in jedem Fall gilt: Logfiles werden vom System automatisch klein gehalten, sofern man das Backup macht. Wenn das Backup nicht über den SQL Internen Job geschieht, sondern über eine Software, dann muss darauf geachtet werden, das ein "Application Aware Backup" verwendet wird.
Die Transaktionslogs werden nach 2 Backups weggeschmissen.
Ein kleines anheben der Log/DB Grösse führt zu höheren IO Belastungen, weil die DB öfters neu organisiert wird (Physisch auf der Platte). Also lieber großzügig vergrössern. Plattenplatz kostet ja nix mehr.
Autoshrink ist, wie @clshak schon gesagt hat, keine gute Idee und nur in wenigen fällen überhaupt sinnvoll.
Fragmentierung auf der SSD: Was den Zugriff auf die Daten angeht: Nein, ist egal. Was die Logik der Software angeht: Wenn die Software die Daten auf der SSD Komplett verschiebt, weil ständig eine Re-Organisation der Daten ausgelöst wird (autoshrink, Vergrössern der DBs, Neuanlegen von DB-Dateien etc), dann natürlich schon. Aber nur weil es
1. IO Verursacht
2. Schreibvorgänge auf der SSD auslöst, was dieser bekanntlich nicht gut tut.
beim Recovery Simple sollte eigentlich kaum was auf dem Logfile passieren.
Aber in jedem Fall gilt: Logfiles werden vom System automatisch klein gehalten, sofern man das Backup macht. Wenn das Backup nicht über den SQL Internen Job geschieht, sondern über eine Software, dann muss darauf geachtet werden, das ein "Application Aware Backup" verwendet wird.
Die Transaktionslogs werden nach 2 Backups weggeschmissen.
Ein kleines anheben der Log/DB Grösse führt zu höheren IO Belastungen, weil die DB öfters neu organisiert wird (Physisch auf der Platte). Also lieber großzügig vergrössern. Plattenplatz kostet ja nix mehr.
Autoshrink ist, wie @clshak schon gesagt hat, keine gute Idee und nur in wenigen fällen überhaupt sinnvoll.
Fragmentierung auf der SSD: Was den Zugriff auf die Daten angeht: Nein, ist egal. Was die Logik der Software angeht: Wenn die Software die Daten auf der SSD Komplett verschiebt, weil ständig eine Re-Organisation der Daten ausgelöst wird (autoshrink, Vergrössern der DBs, Neuanlegen von DB-Dateien etc), dann natürlich schon. Aber nur weil es
1. IO Verursacht
2. Schreibvorgänge auf der SSD auslöst, was dieser bekanntlich nicht gut tut.
Ich danke euch beiden für den Input!
Autoshrink bleibt aus.
Armin
Autoshrink bleibt aus.
Armin
