SQL Server 2005/2008 - Transaktionsprotokoll verkleinern
Andreas Mueller
Wie kann man es wieder verkleinern?
Mit DBCC SHRINKFILE ?
Habe da was gefunden, bezieht sich allerdings auf SQL 2000.
http://blog.schelian.de/2005/01/10/SQL2000TransaktionsprotokollVerkleinern.aspx
Sollte man das Transaktionsprotokoll immer auf eine maximale Dateigröße
beschränken?
Andreas Mueller
gesichert (vollständig) wird?
Muss man das Transaktionsprotokoll noch extra sichern (auch täglich)?
Christa Kurschat
Andreas Mueller <Andreas...@discussions.microsoft.com> schrieb:
> Wächst das Transaktionsprotokoll (LDF) auch, wenn die
> Datenbank täglich gesichert (vollständig) wird?
>
Ja. Die Vollsicherung der DB macht mit dem Log rein gar nichts.
> Muss man das Transaktionsprotokoll noch extra sichern (auch
> täglich)?
>
Ja.
>>
>> Sollte man das Transaktionsprotokoll immer auf eine maximale
>> Dateigröße beschränken?
Das kannst Du machen, die sollte aber wirklich groß genug sein, weil Du
sonst nämlich außer Fehlermeldungen keinen Vorteil hast.
Besser:
Das Log regelmäßig, notfalls auch mehrfach tagsüber, sicher. Die darin
enthaltenen abgeschlossenen Transaktionen werden dann als überschreibbar
gekennzeichnet, der Platz wird also wiederverwendet. Das Log bleibt dann auf
einer angemessenen Größe.
Sollte es jetzt sehr groß sein, kannst Du *nach* der Prokollsicherung ein
DBCC shrinkfile ausführen (im Mangement Studio: Kontextmenü auf der DB,
Tasks, verkleinern, Dateien). Bedenke aber, was verkleinert wurde, muß auch
wieder wachsen, was Ressourcen kostet. Deshalb nur im Ausnahmefall anwenden.
Gruß
Christa
--
Access-FAQ: http://www.donkarl.com
SQL-Server-FAQ: www.sqlfaq.de
InsideSql: www.insidesql.org
Andreas Mueller
Danke vorab für die Infos.
>>Bedenke aber, was verkleinert wurde, muß auch wieder
>>wachsen, was Ressourcen kostet. Deshalb nur im Ausnahmefall anwenden.
Was meinst du genau damit, das es Ressourcen kostet?
Macht es auch Sinn, die Datenbank zu verkleinern, wenn eine bestimmte Grenze
erreicht ist?
Habe mein Datenbackup (Wartungsplan) so eingerichtet:
1. Datenbankintegrität überprüfen
2. Datenbank sichern (vollständig)
3. Verlaufscleanup
4. Datenbank verkleinern (Grenze: 500 MB, Freier Speicherplatz 25%)
Danach die Sicherung des Transaktionsprotokolls (beides täglich).
Gruß
Andreas
"Christa Kurschat" wrote:
> .
>
Christa Kurschat
Andreas Mueller <Andreas...@discussions.microsoft.com> schrieb:
>>> Bedenke aber, was verkleinert wurde, muß auch wieder
>>> wachsen, was Ressourcen kostet. Deshalb nur im Ausnahmefall
>>> anwenden.
>
> Was meinst du genau damit, das es Ressourcen kostet?
>
Daß der SQL Server Speicher und Zeit braucht, den Platz wieder zu
allokieren.
>
> Macht es auch Sinn, die Datenbank zu verkleinern, wenn eine
> bestimmte Grenze erreicht ist?
>
Nein, wozu?
Die Datenbank wird doch wieder wachsen.
Wenn Du Platzprobleme auf dre Platte hast, solltest Du über eine neue oder
weitere Platte nachdenken.
> Habe mein Datenbackup (Wartungsplan) so eingerichtet:
> 1. Datenbankintegrität überprüfen
> 2. Datenbank sichern (vollständig)
> 3. Verlaufscleanup
> 4. Datenbank verkleinern (Grenze: 500 MB, Freier Speicherplatz
> 25%)
>
> Danach die Sicherung des Transaktionsprotokolls (beides
> täglich).
>
Das Verkleinern würde ich rauslassen.
Die ist nur im Ausnahmefall angesagt, wenn man z:b. sehr viele Daten
gelöscht hat oder wenn das Log längere Zeit nicht gesichert wurd.
Der SQL Server verwendet freigewordenen Platz wieder. Es ist kein Access.
Andreas Mueller
groß wird?
Die Datenbank (mdf) hat gerade mal eine Größe von 20 MB, das Protokoll (ldf)
allerdings 1,3 GB.
Bei dieser Datenbank lässt sich das Transaktionsprotokoll auch nicht seperat
sichern (Sicherungstyp: Transaktionsprotokoll fehlt in der Auswahl)
Dafür hat die Sicherung der Datenbank (vollständig) eine Größe von 1,2 Gb.
"Christa Kurschat" wrote:
> .
>
Andreas Mueller
>sichern (Sicherungstyp: Transaktionsprotokoll fehlt in der Auswahl)
Schon gefunden woran es liegt. Das Wiederherstellungsmodell steht auf SIMPLE
Lässt sich das Wiederherstellungsmodell nachträglich ändern? Wenn ja wo?
Christa Kurschat
Andreas Mueller <Andreas...@discussions.microsoft.com> schrieb:
>> Bei dieser Datenbank lässt sich das Transaktionsprotokoll
>> auch nicht seperat sichern (Sicherungstyp:
>> Transaktionsprotokoll fehlt in der Auswahl)
>
> Schon gefunden woran es liegt. Das Wiederherstellungsmodell
> steht auf SIMPLE
>
OK, dann kannst Du es nicht sichern.
Dann sollte das Log aber auch nicht so groß werden.
Geh doch mal auf die Datenbank, Kontextmenü - Tasks - Verkleinern - Dateien
Dort stell mal das log ein und schau, wieviel ungenutzer Platz darin ist.
Bei Wiederherstellungsmodell "simple" kannst Du das Log ruhig verkleinern.
Es wächst dann natürlich wieder.
Wenn es übermäßig wächst, solltest Du mal schauen, was für Transaktionen da
ablaufen. Machst Du täglich riesige Datenimporte, updates, Deletes?
z.B. kann man ein "delete from Tabelle" meistens sehr gut durch ein
"truncate table Tabelle" ersetzen, was nur einen minimalen Bruchteil
Sepicherplatz im Log belegt.
> Lässt sich das Wiederherstellungsmodell nachträglich ändern?
> Wenn ja wo?
Ja, sicher.
Unter Eigenschaften der Datenbank, zu finden bei Optionen.
Andreas Mueller
Unter: Datenbankeigenschaften | Optionen | Wiederherstellungsmodell (Auswahl)
Uwe Ricken
vorsicht mit TRUNCATE. Da gibt es erhebliche Einschränkungen:
Auf die Tabelle wird mit einer FOREIGN KEY-Einschränkung verwiesen. (Sie
können eine Tabelle abschneiden, die einen Fremdschlüssel mit einem Verweis
auf sich aufweist.)
Die Tabelle ist an einer indizierten Sicht beteiligt.
Die Veröffentlichung wird mithilfe einer Transaktionsreplikation oder
Mergereplikation vorgenommen
Desweiteren gilt zu bedenken, dass für das Ausführen von TRUNCATE db_owner
oder ddl_admin Berechtigungen erforderlich sind.
Das kann man auch nicht mit EXECUTE AS in einer Prozedur kapseln.
Mehr Details unter:
http://msdn.microsoft.com/de-de/library/ms177570.aspx
--
Uwe Ricken
db Berater GmbH
http://www.db-berater.de
MCTS: Microsoft SQL Server 2005
MCDBA: Microsoft SQL Server 2005
Christa Kurschat
Uwe Ricken <nos...@db-berater.de> schrieb:
> Hallo Christa,
>
> vorsicht mit TRUNCATE. Da gibt es erhebliche Einschränkungen:
>
Das weiß ich doch, deshalb schrieb ich ja auch "meistens"
Einsetzen, wo möglich, ist meine Devise.
Andreas Mueller
>Dort stell mal das log ein und schau, wieviel ungenutzer Platz darin ist.
Verfügbarer freier Speicherplatz bei:
- Daten: 2,31 MB (11 %)
- Protokoll: 87,01 MB (6 %) von 1276,50 MB!
Im Protokoll scheint eine Menge drin zu stehen.
Es stellt sich die Frage weshalb.
"Christa Kurschat" wrote:
> .
>
Christa Kurschat
Andreas Mueller <Andreas...@discussions.microsoft.com> schrieb:
>> Geh doch mal auf die Datenbank, Kontextmenü - Tasks -
>> Verkleinern - Dateien Dort stell mal das log ein und schau,
>> wieviel ungenutzer Platz darin ist.
>
> Verfügbarer freier Speicherplatz bei:
> - Daten: 2,31 MB (11 %)
> - Protokoll: 87,01 MB (6 %) von 1276,50 MB!
>
> Im Protokoll scheint eine Menge drin zu stehen.
> Es stellt sich die Frage weshalb.
>
Nicht sauber abgeschlossene Transaktionen? KA.
Hast Du es denn mal verkleinert, bevor Du da sWiederherstellungsmodell
umgestellt hast?
Andreas Mueller
"Vollständig". Ich möchte allerdings nicht dieses große Protokoll
mitschleppen.
Für die Wiederherrstellung ist das Log bei "Einfach" wohl nicht relevant.
Kann man das Protokoll vor dem Wechsel gegen Null MB verkleinern?
Statt der Verkleinerungsaktion "Nicht verwendeten Speicherplatz freigeben"
folgende auswählen
"Seiten vor dem Freigeben von nicht verwendeten ... "
und "Datei verkleinern auf: ... MB" (Maximalwert)?
Zur Zeit gibt es ja nur 6% frei verfügbaren Speicherplatz beim Protokoll.
Da würde eine normale Verkleinerung nicht viel bringen.
"Christa Kurschat" wrote:
> .
>
Christa Kurschat
Andreas Mueller <Andreas...@discussions.microsoft.com> schrieb:
> Ich will bei der DB das Wiederherstellungsmodell wechseln von
> "Einfach" auf "Vollständig". Ich möchte allerdings nicht
> dieses große Protokoll mitschleppen.
>
> Für die Wiederherrstellung ist das Log bei "Einfach" wohl
> nicht relevant. Kann man das Protokoll vor dem Wechsel gegen
> Null MB verkleinern?
>
Ja, schrieb ich doch schon.
Aber das Log ist auch bei "Einfach" relevant. Der SQL Server benötigt es in
jedem Fall für's zurückrollen von fehlgeschlagenen Transaktionen. Die
Transaktionen werden dann beim Checkpoint allerdings als "abgeschlossen"
gekennzeichnet und der Platz zum Wiederverwenden freigegeben.
Bei "Massenprotokolliert" oder "Vollständig" geschieht dies Freigeben erst
bei der Sicherung des Logs.
> Statt der Verkleinerungsaktion "Nicht verwendeten
> Speicherplatz freigeben" folgende auswählen
> "Seiten vor dem Freigeben von nicht verwendeten ... "
> und "Datei verkleinern auf: ... MB" (Maximalwert)?
>
Auf jeden Fall "Seiten vor dem Freigeben " als Option wählen, damit der
aktive Teil an den Anfang verschoben wird.
Eine Größe mußt Du in dem Fall erstmal nicht angeben.
> Zur Zeit gibt es ja nur 6% frei verfügbaren Speicherplatz beim
> Protokoll. Da würde eine normale Verkleinerung nicht viel
> bringen.
Probier's doch einfach aus.
Das Wiederherstellungsmodell muß aber noch auf "einfach" stehen.
Andreas Mueller
1. Wiederherstellungsmodell noch auf "Einfach"
1.1 Sicherung der Datenbank (Vollständig)
1.2 Verkleinerungsaktion (Protokoll) - Seiten vor dem Freigeben ...
(Datei verkleinern auf: Maximalwert (1.234 MB))
1.3 danach Verkleinerungsaktion (Protokoll) - Nicht verwendeten
Speicherplatz freigeben
> Nach der Aktion gibt es frei verfügbaren Speicher 0% (zuvor 6%)
Das Transaktionsprotokoll ist danach immer noch 1,2 GB groß.
Bekomm ich das Protokoll nicht kleiner?
http://msdn.microsoft.com/de-de/library/ms188493(SQL.90).aspx
Gruß
Andreas
"Christa Kurschat" wrote:
> .
>
Christoph Ingenhaag
Lies dir das mal durch:
Understanding Logging and Recovery in SQL Server:
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
In dem Artikel sind auch Links auf
Faktoren, die das Abschneiden des Protokolls verzögern können:
http://msdn.microsoft.com/de-de/library/ms345414.aspx
Wenn das im Thread schon mal gepostet wurde, tuts mir leid, ich hab den
Thread nur grob überflogen...
Viele Grüße
Christoph
"Andreas Mueller" <Andreas...@discussions.microsoft.com> schrieb im
Newsbeitrag news:AD527ABA-6643-4F46...@microsoft.com...
Elmar Boye
"Andreas Mueller" <Andreas...@discussions.microsoft.com> schrieb ...
> 1. Wiederherstellungsmodell noch auf "Einfach"
> 1.1 Sicherung der Datenbank (Vollständig)
> 1.2 Verkleinerungsaktion (Protokoll) - Seiten vor dem Freigeben ...
> (Datei verkleinern auf: Maximalwert (1.234 MB))
> 1.3 danach Verkleinerungsaktion (Protokoll) - Nicht verwendeten
> Speicherplatz freigeben
>> Nach der Aktion gibt es frei verfügbaren Speicher 0% (zuvor 6%)
>
> Das Transaktionsprotokoll ist danach immer noch 1,2 GB groß.
>
> Bekomm ich das Protokoll nicht kleiner?
Ich habe jetzt nicht die ganze Konversation verfolgt:
Wenn Deine Protokoll-Datei nicht kleiner werden will,
schau mittels DBCC OPENTRAN nach
http://msdn.microsoft.com/de-de/library/ms182792.aspx
ob es noch ältere Transaktionen gibt, die das verhindern -
evtl. gibt es eine vergessene Replikation.
Bei SQL Server 2005 sollte SP3 installiert sein:
http://support.microsoft.com/kb/959376
Siehe auch http://support.microsoft.com/kb/907511
"How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005"
Gruß Elmar
Andreas Mueller
Habe DBCC OPENTRAN ausgeführt.
Transaktionsinformationen für die 'medien'-Datenbank.
Replizierte Transaktionsinformationen:
Älteste verteilte LSN : (0:0:0)
Älteste nicht verteilte LSN : (385:231:1)
Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen
ausgegeben hat, wenden Sie sich an den Systemadministrator.
Info: Die Datenbank wird repliziert (SnapShot)
"Elmar Boye" wrote:
> .
>
Elmar Boye
"Andreas Mueller" <Andreas...@discussions.microsoft.com> schrieb...
> SQL Server 2005 sollte SP3 muss noch installiert werden.
>
> Habe DBCC OPENTRAN ausgeführt.
>
> Transaktionsinformationen für die 'medien'-Datenbank.
>
> Replizierte Transaktionsinformationen:
> Älteste verteilte LSN : (0:0:0)
> Älteste nicht verteilte LSN : (385:231:1)
Dann hängt da noch eine Transaktion aus einer (Transaktions)Replikation.
Überprüfe mal die Abbonnments. Evtl. existiert da noch eine Leiche.
Helfen kann sp_repldone http://msdn.microsoft.com/de-de/library/ms173775.aspx
Pass aber auf, dass Du Dir nicht die gültigen Publikationen zerschiesst.
Gruß Elmar
Uwe Ricken
neben dem Hinweis von Elmar noch ein Link, um das Problem zu verstehen:
http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx
Andreas Mueller
Habe da noch ein anderes Problem.
Bei der Datenbank die repliziert wird (SQL Server 2005 Express) gibt es frei
verfügbaren Speicher beim Protokoll von 97%. Das Protokoll ist 2GB groß.
Wenn ich das Protokoll vekleiner, ändert sich nicht viel (bleibt ca. bei 97%).
Replikation: Lokale Abonnements
Publikationstyp: Transaktionsreplikation
Speicherort des Verteiler-Agents: Verteiler
Muss man zuvor die Replikation aufheben, damit die Verkleinerung Wirkung hat?
Uwe Ricken
mal im Ernst - hast Du dich überhaupt mal mit den Links von Elmar und mir
beschäftigt?
Wie bereits von Elmar dargelegt - es werden sicherlich noch nicht
abgeschlossene Transaktionen im Log vorhanden sein.
Solange die nicht geschlossen sind, wird das Log nicht freigegeben und Du
kannst es nicht verkleinern!!!