Verkleinern des Transaktionsprotokolls bei SQL Server 7

[Holger Möller]

Hallo!

Gelegentlich kommt auf dem SQL Server (Service Pack 2) unserer Firma zu folgendem Problem:

Eine Datenbank hat eine große Menge an Speicherplatz für das Transaktionsprotokoll reserviert (beispielsweise 2 GB), aber belegt ist nur wenig (beispielsweise 50 MB). Bei einigen dieser Datenbank ist der Aspekt der Sicherheit nicht so hoch, so dass Sicherungen in größeren Abständen ausreichen und das Transaktionsprotokoll keine Rolle spielt.
Allerdings war die Option "Bei Prüfpunkt abschneiden" nicht gesetzt. Und der Wachstum der Protokolls ist unbegrenzt.
Dann kommt es vor, dass sich das Transaktionsprotokoll bzw. die gesamte Datenbank nicht verkleiner läßt. Ich habe folgendes probiert:

Zuerst eine komplette Sicherung der Datenbank
Die erwähnte Option aktivieren
Das Protokoll abschneiden (über Enterprise Manager und über dbcc)
Die Datenbank zu verkleinern (über Enterprise Manager und über dbcc)
Mit dbcc shrink den Log-File zu verkleinern

Keine der Aktionen war erfolgreich. Der reservierte Speicher für das Protokoll blieb bei der Ausgangsgröße.

Kennt jemand dieses Problem und kann mir etwas dazu sagen?

Vielen Dank!

MfG

Holger Möller

[Andreas Kosch]

Hallo,

was passiert, wenn folgendes über den SQL Query Analyzer abgesetzt wird?
<pre>
checkpoint -- explizit einen Prüfpunkt setzen, Logeinträge werden in DB geschrieben
backup log Datenbankname with no_log -- Inaktive Einträge abschneiden
dbcc shrinkfile(Datenbankname_Log) -- Logdatei soll verkleinert werden
dbcc shrinkdatabase(Datenbankname)
</pre>
Beim SQL Server 2000 steht dafür ein direkter Dialog zur Verfügung: <i>Enterprise Manager -> Datenbankname anklicken -> Rechter Mausklick -> Ansicht Taskpad -> Reservierter Speicherplatz (gelbes Symbol) -> Menüeintrag <b>Datenbank verkleinern</b></i>.

&#10

[Holger Möller]

Vielen Dank für den Tip!
Der Befehl "checkpoint" hat mir bis dato gefehlt.
Die Wirkung dieser Befehlskette ist ziemlich gut (gerade getestet). Er verkleinert den für das Transaktionsprotokoll reservierten Speicher erheblich. Aber es scheint mir, dass er doch noch zu viel reserviert.

Ich habe folgendes Ergebnis:
Vor der Befehlskette: belegt: ca. 70 MB (bin mir nicht mehr ganz sicher); reserviert ca. 2,1 GB
Nach der Befehlskette: belegt ca. 70 MB; reserviert ca. 630 MB

Woran kann es liegen, daß nicht noch mehr freigeben wird?

Nochmals vielen Dank!

MfG

Holger Mölle

[Andreas Kosch]

Hallo,

ich würde die Grösse der Logdatei normalerweise überhaupt nicht verkleinern, es sei denn, der Platz auf der Festplatte wird knapp. Der SQL Server verkleinert die Logdatei nicht sofort, um später bei Bedarf sofort auf die Datei zugreifen zu müssen, ohne jedesmal die Datei vergrössern zu müssen. Beim Import von umfangreichen Daten wird man feststellen, dass der Vorgang um ein mehrfaches schneller abläuft, wenn bereits eine grosse Logdatei vorhanden ist.

Beim Anlegen der Datenbank kann man festlegen, wie gross die Logdatei mindestens sein muss und um vieviel Prozenz diese bei Bedarf automatisch vergrössert wird

[Holger Möller]

Hallo!

Im Normalfall würde ich dies auch nicht tun, aber wir sind hier derzeit dabei den SQL Server neu einzurichten (neues RAID) und dann auch alles neu zu initialisieren (insbesondere im Bezug auf die Ausgagnsgrößen der Files). Auch werden einige Datenbanken im Laufe der Zeit gelöscht werden. Und bei einigen Datenbanken ist es so, dass dort in größeren Zeitabständen umfangreiche Transaktionen stattfinden, die dann dieses große Transaktionsprotokoll hinterlassen. Dies wird dann aber nicht mehr benötigt und für diese DBs ist das Thema relevant. Nach der Neuinstallation sollte es nicht mehr nötig sein.

Nochmals danke für die Hilfe.

MfG

Holge

[Patrick Sack]

Hallo A.Kosch
<br>
<br>ich habe gerade Ihren SQL Code getestet, hat jedoch nicht so richtig Wirkung gezeigt (die ldf Datei wurde von 2,6 GB auf 2.3 GB verkleinert).
<br>"Der SQL Server verkleinert die Logdatei nicht sofort, ..."
<br>Darf man das wörtlich nehmen? D.h. wenn in der nächsten Zeit kein großer Traffic auf der Datenbank stattfindet wird die Datei noch etwas kleiner.
Sind nach der Ausführung des obigen Scriptes, obwohl die Datei noch groß ist, alle älteren Transaktionen unwiederbringbar gelöscht? D.h. stehen da noch rund 2 GB an Transaktionsdaten drin (die ich ja eigentlich gelöscht haben möchte)?
<br>
<br>Danke!
<br>
<br>mfg
<br>P

[Andreas Kosch]

Hallo,

die physische Log-Datei ist intern in virtuelle Logdateien unterteilt, deren Anzahl und Größe von der aktuellen Datenbankaktivität abhängt. Jeder Eintrag im Transaction Log erhält eine eindeutige Log Sequence Number (LSN). Die physische Datei für das Transaktions-Log wächst immer dann an, wenn der Platzbedarf für die virtuellen Logdateien am Ende der physischen Datei angekommen ist und der SQL Server noch nicht die Log-Einträge vor der minimalen LSN gesichert hat. Die Log-Eintrag vor der minimalen LSN spielen beim Recovery keine Rolle mehr, werden allerdings für das Rollforward benötigt, wenn die Datenbank nach dem Einspielen des letzen Voll-Backups über die gesicherten Log-Dateien wiederhergestellt werden soll. Wenn diese Log-Sequenzen nicht benötigt werden, kann der SQL Server die Log-Einträge vor der minimalen LSN auch überschreiben, wenn eine der folgenden Bedingungen erfüllt wurde:<br>
- Das Log wurde über die T-SQL-Aufrufe BACKUP LOG WITH NO_LOG oder BACKUP LOG WITH TRUNCATE_ONLY abgeschnitten. <br>
- Das Abschneiden der Log-Datei wurde über <b>trunc. log on chkpt</b> automatisch angefordert (SQL Server 7) oder über das Wiederherstellungs-Modell <b>Einfach</b> angefordert (SQL Server 2000). <br>
- Es wird seit dem letzten vollständigen Backup eine Operation durchgeführt, die keine Log-Dateien anlegt (Bulkcopy).<br>
- Es wurde noch niemals ein vollständiges Datenbank-Backup ausgelöst.<br>
Wenn eine dieser Bedindungen zutrifft, recycelt der SQL Server immer denn den "alten" Logbereich, wenn eine virtuelle Logdatei an das Ende der physischen Logdatei kommt. Das Abschneiden des Logs markiert also nur den wiederverwendbaren Platz der Logdatei, verkleinert diese jedoch nicht. Zur Verkleinerung der physischen Logdatei stehen 2 Wege offen:<br>
- Datenbank-Optionsdialog: Automatisch verkleinern <br>
- Oder manuell über SQL Query Analyzer über die o.g. Anweisungen

Wenn die Log-Datei nach dem Verkleinern immer noch "gross" ist, dann benötigt der SQL Server die dort vorgefundenen Daten für das Rollforward (je nach dem konfigurierten Wiederherstellungs-Modell)

[Olaf Steffan]

Hallo,<br><br>
schön, dass dieses Thema aufgegriffen wurde. Bei mir ist die Lage nämlich schon ziemlich hoffnungslos - aber nicht ernst ;-)!<br>Aber Spaß beiseite, das Transaktionsprotokoll meiner Datenbank ist auf über 15GB(!) angewachsen. Der Enterprise-Manager verrät mir, dass aber nur 98MB belegt sind. Ich habe schon den ganzen Tag damit verbracht eine Lösung zu finden. Die Platten unseres Servers sind nun schon fast voll.<br>Leider haben die Befehle von weiter oben bei mir keinerlei Wirkung gezeigt. Ich verstehe auch nicht ganz, warum das Protokoll so voll geschrieben wurde. Wir führen jede Nacht ein Vollbackup durch, da müsste der interne Zeiger doch wieder an den Anfang gesetzt werden!?<br><br>Hat nicht noch jemand etwas aus der untersten Trickkiste? Vielen Dank schon jetzt!<br><br>Oll

[Patrick Sack]

Hi,
<br>
<br>nun ist fast ein Monat vergangen nachdem ich "versucht" habe das Transaktionsprotokoll meiner Datenbank zu verkleinern. Wie schon beschrieben hatte ich keinen großen Erfolg gehabt. Als ich jedoch nun wieder mal auf die größe der Protokolldatei schaute, so wurde mir eine Größe < 50 MB angezeigt.
<br>
<br>Kann es sein das die von A.Kosch beschriebenen Befehle erst nach einer gewissen Zeit ihre volle Wirkung entfalten (ich habe natürlich nicht das nächtliche Backup abgewartet, liegt es womöglich daran, das diese Zeilen erst wirkung zeigen wenn ein Vollständiges Backup gemacht wurde)?
<br>Wie sieht es im Moment bei dir aus O.Stefan?
<br>Hat sich die Protokolldatei bei dir nun auch verkleinert?
<br>
<br>mfg
<br>P

[Olaf Steffan]

Hi Patrick,<br><br>nun, ich habe das automatische Abschneiden des Transaktionsprotokolls am Prüfpunkt und die automatische Verkleinerung des Transaktionsprotokolls aktiviert. Es hat zwar nicht die Welt geholfen, aber es ist doch etwas geschrumpft. Zwar umfasst es noch stattliche 11GB, aber immerhin sind die Reserven etwas aufgebessert worden. Das Protokoll ist aber inhaltlich nur mit 56MB gefüllt.<br>Das ist also nicht so sehr befriedigend, aber immerhin...<br>Bei einer etwas kleineren Datenbank hatte ich auch ein Vollbackup gemacht nachdem ich das Protokoll abgeschnitten hatte, habe den Server heruntergefahren, und ganz einfach die Protokolldatei gelöscht. Nach dem Start hat er eine automatisch eine neue Protokolldatei angelegt, die jetzt natürlich richtig klein war. Auch ein DB-Check war erfolgreich, man konnte problemlos arbeiten<br>Den gleichen Trick habe ich bei meiner GigaDB versucht, aber da hat er gemeckert. Die ganze Sache ist natürlich auch ziemlich - krass!<br>Ja, mehr weiß ich leider auch nicht, aber falls neue Erkenntnisse gewonnen werden, immer her damit!<br><br>Bis dann<br>O. Steffa

[Andreas Kosch]

Siehe "Hilfe das Transaktionsprotokoll vergrößert sich beim Versuch es zu verkleinern&quot

[Olaf Steffan]

Es hat funktioniert!!!!<BR><BR>Nach etlichen Tagen ist mein Transaktionsprotokoll geschrumpft. Wie gesagt, sichern wir täglich die DB als Vollbackup. Warum das so lange gedauert hat, ist mir nicht ganz klar. Aber die weiter oben beschriebenen Optionen scheinen nun endlich gewirkt zu haben.<BR><BR>O. Steffa

[Patrick Sack]

Hi Olaf,
<br>
<br>danke für die Info. Jetzt kann ich endlich mit Gewissheit davon ausgehen, das es doch funktioniert, es braucht nur seine Zeit.
<br>
<br>mfg
<br>P

[Patrick Sack]

Noch ein Link zu diesem Thema hier im Forum:
<pre>
<a href="/webx?50@@.ee87d7c">Peter Schoch "Hilfe das Transaktionsprotokoll vergrößert sich beim Versuch es zu verkleinern!" 05.11.2001 20:06</a>
</pre>
mfg
p