Nach index neu organisieren und index reorganisieren wird das log File riesig
Hallo zusammen,
ich habe das Problem das meine ldf Datei meiner Datenbank nach einem "index neu erstellen" & "index neu organisieren" um ca. 250 GB wächst.
Hier erstmal was zu meiner Umgebung.
Win2003 Server mit SQL2005 Server
Größe der Datenbank um die es geht 100 GB
Wiederherstellungsmodus Vollständig
Die Datenbank wird einmal Täglich gesichert, über einen Backupserver mit Symantec BackupExec2010.
Backup-Methode: "Gesamt - Gesamte Datenbank oder Dateigruppe sichern"
Die Transactionlogs werden stündlich über den gleichen Backupserver mit der gleichen Backupsoftware gesichert
Backup-Methode: Protokoll - Transaktionsprotokoll sichern
Einmal in der Woche (am WE) wird ein Wartungsplan "index neu erstellen" ausgeführt. Dieser dauert ca. 3,5 Stunden. Danach wird der Wartungsplan "Index neu organisieren" ausgeführt und dauert ca. 30m.
Dabei wächst mein ldf File um ca. 250 GB.
Der Speicherplatz wird nicht wieder automatisch frei gegeben.
Liegt hier ein Fehler vor oder muss ich das ldf immer per Script verkleinern lassen?
USE [DATENBANK]
GO
DBCC SHRINKFILE (N'DATENBANK_Log' , 0, TRUNCATEONLY)
GO
Dieses Problem tritt erst seit kurze Zeit auf. Vorher wurde der Speicherplatz automatisch wieder frei gegeben bzw. das Log File wurde erst gar nicht so groß. Bevor das Problem aufgetreten ist wurde das ldf File nicht per Script verkleinert und wurde bei den Wartungsaufträgen auch nicht größer.
Kann mir jemand helfen?
ich habe das Problem das meine ldf Datei meiner Datenbank nach einem "index neu erstellen" & "index neu organisieren" um ca. 250 GB wächst.
Hier erstmal was zu meiner Umgebung.
Win2003 Server mit SQL2005 Server
Größe der Datenbank um die es geht 100 GB
Wiederherstellungsmodus Vollständig
Die Datenbank wird einmal Täglich gesichert, über einen Backupserver mit Symantec BackupExec2010.
Backup-Methode: "Gesamt - Gesamte Datenbank oder Dateigruppe sichern"
Die Transactionlogs werden stündlich über den gleichen Backupserver mit der gleichen Backupsoftware gesichert
Backup-Methode: Protokoll - Transaktionsprotokoll sichern
Einmal in der Woche (am WE) wird ein Wartungsplan "index neu erstellen" ausgeführt. Dieser dauert ca. 3,5 Stunden. Danach wird der Wartungsplan "Index neu organisieren" ausgeführt und dauert ca. 30m.
Dabei wächst mein ldf File um ca. 250 GB.
Der Speicherplatz wird nicht wieder automatisch frei gegeben.
Liegt hier ein Fehler vor oder muss ich das ldf immer per Script verkleinern lassen?
USE [DATENBANK]
GO
DBCC SHRINKFILE (N'DATENBANK_Log' , 0, TRUNCATEONLY)
GO
Dieses Problem tritt erst seit kurze Zeit auf. Vorher wurde der Speicherplatz automatisch wieder frei gegeben bzw. das Log File wurde erst gar nicht so groß. Bevor das Problem aufgetreten ist wurde das ldf File nicht per Script verkleinert und wurde bei den Wartungsaufträgen auch nicht größer.
Kann mir jemand helfen?
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 192054
Url: https://administrator.de/forum/nach-index-neu-organisieren-und-index-reorganisieren-wird-das-log-file-riesig-192054.html
Ausgedruckt am: 23.12.2024 um 03:12 Uhr
13 Kommentare
Neuester Kommentar
Hallo,
hier sind mehrere Vorgänge sinnvoll durchzuführen:
Vor dem Index neu erstellen sollte das Wiederherstellungsmodell auf Massenprotokolliert gestellt werden - dann wächst das Transaktionsprotokoll relativ gering.Danach umstellen nicht vergessen.
2. Backup des Tranasktionsprotokolls mit anschließendem Abschneiden der Protokolleinträge
Ein Shrink macht an dieser Stelle nur wenig Sinn. Die Datei sollte auf einem "ordentlichen" Wert belassen und regelmäßig gesichert werden. Nach dem Sichern sollte das Transaktionsprotokoll abgeschnitten werden -> Speicherplatz in der .ldf wird wieder frei. Auf diese Art und Weise entsteht keine Fragmentierung innerhalb der .ldf-Datei (interne Fragmentierung mit Seiten - nicht die NTFS-Fragmentierung!)
Der Wartungsplan sollte sich auf einen burchteil der Zeit verringern.
Versuche alternativ auch mal folgendes Script zum Erstellen der Indizes:
DECLARE @tablename VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @tablename + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @tablename
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Gruss
Grinskeks
hier sind mehrere Vorgänge sinnvoll durchzuführen:
Vor dem Index neu erstellen sollte das Wiederherstellungsmodell auf Massenprotokolliert gestellt werden - dann wächst das Transaktionsprotokoll relativ gering.Danach umstellen nicht vergessen.
2. Backup des Tranasktionsprotokolls mit anschließendem Abschneiden der Protokolleinträge
Ein Shrink macht an dieser Stelle nur wenig Sinn. Die Datei sollte auf einem "ordentlichen" Wert belassen und regelmäßig gesichert werden. Nach dem Sichern sollte das Transaktionsprotokoll abgeschnitten werden -> Speicherplatz in der .ldf wird wieder frei. Auf diese Art und Weise entsteht keine Fragmentierung innerhalb der .ldf-Datei (interne Fragmentierung mit Seiten - nicht die NTFS-Fragmentierung!)
Der Wartungsplan sollte sich auf einen burchteil der Zeit verringern.
Versuche alternativ auch mal folgendes Script zum Erstellen der Indizes:
DECLARE @tablename VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @tablename + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @tablename
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Gruss
Grinskeks
Hallo,
das Wiederherstellungsmodell sagt aus, wie Änderungen an den Daten wiederhergestellt werden können. Beim vollständigen Wiederherstellungsmodell werden alle Änderungen im Transaktionsprotokoll hinterlegt. Wir sichern die Datendateien bei kritischen Datenbanken z.B. einmal täglich und das Transaktionsprotokoll alle 15 Minuten - somit sind unsere Backups relativ klein und der Datenverlust beträgt max. 15 Minuten.
Sobald das Transaktionsprotokoll gesichert wird, gibt es den Platz für Änderungen, die nun gesichert wurden, wieder frei. Das Protokoll kann abgeschnitten werden und wächst somit nicht / kaum. Wird das Transaktionsprotokoll nicht explizit (alleine) gesichert, wird kein Prüfpunkt erzeugt und das Abschneiden hat keinen Effekt.
Bevor die Indizes neu erstellt werden, würde ich das Wiederherstellungsmodell auf bulk logged stellen, damit das Transaktionsprotokoll nicht so enorm wächst und der Wartungsvorgang auch schneller abläuft. Danach per Script wieder umstellen und gut ist.
Das Script läuft durch alle Tabellen der Datenbank auf der es ausgeführt wird und macht einen Rebuild der Indizes. Es setzt den Füllfaktor nach Eingabe (80% Voreinstellung). Somit hast du pro Indexseite nur 80% Befüllung und kommt ein neuer Datensatz hinzu, fragmentieren dir die Indizes nicht so schnell.
Den Index Rebuild würde ich versuchen, auf Zeiten setzen zu denen keiner arbeitet und offline durchführen. Online kann das Rebuild nur mit der Enterprise Version des SQL Servers durchgeführt werden.
Es scheint mir so, als ob du den SQl Server noch nicht allzu lange administrierst - daher empfehle ich, auf jeden Fall eine Testumgebung einzurichten! Virtualisiere doch das OS mit dem SQL Server und teste das Ganze durch, bevor du dich an das Produktivsystem traust.
Gruss Grinskeks
das Wiederherstellungsmodell sagt aus, wie Änderungen an den Daten wiederhergestellt werden können. Beim vollständigen Wiederherstellungsmodell werden alle Änderungen im Transaktionsprotokoll hinterlegt. Wir sichern die Datendateien bei kritischen Datenbanken z.B. einmal täglich und das Transaktionsprotokoll alle 15 Minuten - somit sind unsere Backups relativ klein und der Datenverlust beträgt max. 15 Minuten.
Sobald das Transaktionsprotokoll gesichert wird, gibt es den Platz für Änderungen, die nun gesichert wurden, wieder frei. Das Protokoll kann abgeschnitten werden und wächst somit nicht / kaum. Wird das Transaktionsprotokoll nicht explizit (alleine) gesichert, wird kein Prüfpunkt erzeugt und das Abschneiden hat keinen Effekt.
Bevor die Indizes neu erstellt werden, würde ich das Wiederherstellungsmodell auf bulk logged stellen, damit das Transaktionsprotokoll nicht so enorm wächst und der Wartungsvorgang auch schneller abläuft. Danach per Script wieder umstellen und gut ist.
Das Script läuft durch alle Tabellen der Datenbank auf der es ausgeführt wird und macht einen Rebuild der Indizes. Es setzt den Füllfaktor nach Eingabe (80% Voreinstellung). Somit hast du pro Indexseite nur 80% Befüllung und kommt ein neuer Datensatz hinzu, fragmentieren dir die Indizes nicht so schnell.
Den Index Rebuild würde ich versuchen, auf Zeiten setzen zu denen keiner arbeitet und offline durchführen. Online kann das Rebuild nur mit der Enterprise Version des SQL Servers durchgeführt werden.
Es scheint mir so, als ob du den SQl Server noch nicht allzu lange administrierst - daher empfehle ich, auf jeden Fall eine Testumgebung einzurichten! Virtualisiere doch das OS mit dem SQL Server und teste das Ganze durch, bevor du dich an das Produktivsystem traust.
Gruss Grinskeks
Hi,
mache ich gerne
Das Wiederherstellungsmodell sollte im Anschluss an die Index Vorgänge wieder auf vollständig umgestellt werden.
Der Füllfaktor ist viel zu niedrig! Stell dir vor du schreibst ein Buch (Tabelle) und das Stichwortverzeichnis am Ende füllt sich automatisch mit. Hast du jede Seite im Verzeichnis zu 100% gefüllt, musst du bei jedem neuen Stichwort im Buch an der Stelle, wo du das Stichwort dazwischen schiebst, Blätter reinkleben und den Rest der Seite auf die nächste Seite legen.Hast du jede Seite nur zu 10% gefüllt, ist dein Stichwortverzeichnis 10 mal so groß und du blätterst sehr viel beim Suchen.
Ist der Füllfaktor also zu gering muss der Server zuviel lesen. Ist er zu hoch, muss er zuviel umschichten und Seiten erzeugen, was Fragmentierung erzeugt. Daher würde ich die 80% vorerst belassen und mich in die Thematik einlesen. Je nachdem, was ihr für Indizes habt, kann man die Indizes später immer noch anpassen bzgl. Füllfaktor. Die 80% sind ein guter Ausgangswert für viele Indizes (z.B. Namen, fortlaufende Nummern).
Der Füllfaktor ist ein Mittel zu Performanceoptimierung und sollte aktuell noch nicht im Fokus stehen, da es hier schnell sehr komplex wird (Ausführungspläne,Häufigkeitsanalysen, Indexoptimierung, Profiling)
Gruss
Grinskeks
mache ich gerne
Das Wiederherstellungsmodell sollte im Anschluss an die Index Vorgänge wieder auf vollständig umgestellt werden.
Der Füllfaktor ist viel zu niedrig! Stell dir vor du schreibst ein Buch (Tabelle) und das Stichwortverzeichnis am Ende füllt sich automatisch mit. Hast du jede Seite im Verzeichnis zu 100% gefüllt, musst du bei jedem neuen Stichwort im Buch an der Stelle, wo du das Stichwort dazwischen schiebst, Blätter reinkleben und den Rest der Seite auf die nächste Seite legen.Hast du jede Seite nur zu 10% gefüllt, ist dein Stichwortverzeichnis 10 mal so groß und du blätterst sehr viel beim Suchen.
Ist der Füllfaktor also zu gering muss der Server zuviel lesen. Ist er zu hoch, muss er zuviel umschichten und Seiten erzeugen, was Fragmentierung erzeugt. Daher würde ich die 80% vorerst belassen und mich in die Thematik einlesen. Je nachdem, was ihr für Indizes habt, kann man die Indizes später immer noch anpassen bzgl. Füllfaktor. Die 80% sind ein guter Ausgangswert für viele Indizes (z.B. Namen, fortlaufende Nummern).
Der Füllfaktor ist ein Mittel zu Performanceoptimierung und sollte aktuell noch nicht im Fokus stehen, da es hier schnell sehr komplex wird (Ausführungspläne,Häufigkeitsanalysen, Indexoptimierung, Profiling)
Gruss
Grinskeks
Hallo chappert,
wenn Du die DB einmalig verkleinern willst, weil sie jetzt über lange Zeit ohne Protokllsicherung angewachsen ist, kannst Du das über "DBCC SHRINKDATABASE" oder gezielt eine Datei über "DBCC SHRINKFILE" tun. Alternativ kannst Du im Management Studio zu der DB gehen und im Kontextmenü "Tasks / Verkleinern" auswählen.
Automatisch könnte die DB auch verkleinert werden, würde ich an Deiner Stelle aber nicht tun. Dazu könnte man in den DB-Eigenschaften bei den Optionen "Automatisch verkleinern" einstellen (oder auch mit "alter database xyz set auto_shrink on"). Aber wie gesagt, ich würds nicht tun.
Gruß, Mad Max
wenn Du die DB einmalig verkleinern willst, weil sie jetzt über lange Zeit ohne Protokllsicherung angewachsen ist, kannst Du das über "DBCC SHRINKDATABASE" oder gezielt eine Datei über "DBCC SHRINKFILE" tun. Alternativ kannst Du im Management Studio zu der DB gehen und im Kontextmenü "Tasks / Verkleinern" auswählen.
Automatisch könnte die DB auch verkleinert werden, würde ich an Deiner Stelle aber nicht tun. Dazu könnte man in den DB-Eigenschaften bei den Optionen "Automatisch verkleinern" einstellen (oder auch mit "alter database xyz set auto_shrink on"). Aber wie gesagt, ich würds nicht tun.
Gruß, Mad Max
Hallo chappert,
Wenn Du eine TP-Sicherung durchführst, wird intern der Platz freigegeben und kann anschließend durch das TP wieder überschrieben werden. Die Datei selber wird nicht kleiner, aber sie wächst auch nur weiter an, wenn der freigegebene Platz in der LDF-Datei nicht ausreicht.
Fall eins, daß die LDF-Datei nicht kleiner wird, wäre also normal, solange keine automatische DB-Verkleinerung eingeschaltet ist. Du könntest die Datei dann von Hand verkleinern, anschließend würde sie bei der Index-Reorganisation auf die dafür benötigte Größe anwachsen und dürfte sich bei weiteren Index-Reorganisationen dann allenfalls noch in kleineren Schritten vergrößern.
Fall zwei, die LDF-Datei wächst trotz TP-Sicherung bei jeder Index-Reorganisation um etwa den gleichen Wert an, würde darauf hindeuten, daß bei der TP-Sicherung etwas nicht richtig läuft, weil dann scheinbar der Platz intern nicht freigegeben wird.
Gruß, Mad Max
es geht mir ja nicht um die DB sondern um das log File (ldf) welches immer um zig GB anwächst.
Die Aussage ist widersprüchlich, das Transaktionslog gehört nämlich zur DB.Allerdings wächst das Log File ständig weiter. Durch das Sichern der Transaktionlogs
wird das Log File auch nicht kleiner sondern behält seine Größe die es nach jedem Index neu und reorg bekommen
hat.
Was ist jetzt eigentlich das Problem, daß die LDF-Datei nicht kleiner wird oder daß sie trotz TP-Sicherung immer noch wächst?wird das Log File auch nicht kleiner sondern behält seine Größe die es nach jedem Index neu und reorg bekommen
hat.
Wenn Du eine TP-Sicherung durchführst, wird intern der Platz freigegeben und kann anschließend durch das TP wieder überschrieben werden. Die Datei selber wird nicht kleiner, aber sie wächst auch nur weiter an, wenn der freigegebene Platz in der LDF-Datei nicht ausreicht.
Fall eins, daß die LDF-Datei nicht kleiner wird, wäre also normal, solange keine automatische DB-Verkleinerung eingeschaltet ist. Du könntest die Datei dann von Hand verkleinern, anschließend würde sie bei der Index-Reorganisation auf die dafür benötigte Größe anwachsen und dürfte sich bei weiteren Index-Reorganisationen dann allenfalls noch in kleineren Schritten vergrößern.
Fall zwei, die LDF-Datei wächst trotz TP-Sicherung bei jeder Index-Reorganisation um etwa den gleichen Wert an, würde darauf hindeuten, daß bei der TP-Sicherung etwas nicht richtig läuft, weil dann scheinbar der Platz intern nicht freigegeben wird.
Gruß, Mad Max
Schau mal bitte im Datei verkleinern Dialog im Management Studio wie groß der verfügbare Speicherplatz ist.
Danach mache ein Full Backup
Danach Transactional Backup ohne Transaktionsprotokoll abschneiden
Danach Transactional Backup mit Transaktionsprotokoll abschneiden
Dann schau wie am Anfang nach, wie groß der verfügbare Speicherplatz im Logfile ist.
Ein Shrinkfile bedeutet immer Fragmentierung und sollte möglichst sparsam eingesetzt werden.
---
Plan B (vorher sichern, keine Nutzer auf dem System):
Datenbank trennen.
Datenbank anfügen -> im Dialog bei Datenbankdetails das Protokollfile über "entfernen" ... entfernen
mit Ok bestätigen.
Danach ist das Logfile erstmal mimimal und wieder vorhanden. Setze es nun auf einen Wert und mache das absolute Wachstum absolut, d.h. keine Prozentzahlen sondern eine Zweierpotenz in MB, z.B. 512MB (was m.E. sehr viel ist, aber je nach Datenbanknutzung...)
---
Gruss
Grinskeks
Danach mache ein Full Backup
Danach Transactional Backup ohne Transaktionsprotokoll abschneiden
Danach Transactional Backup mit Transaktionsprotokoll abschneiden
Dann schau wie am Anfang nach, wie groß der verfügbare Speicherplatz im Logfile ist.
Ein Shrinkfile bedeutet immer Fragmentierung und sollte möglichst sparsam eingesetzt werden.
---
Plan B (vorher sichern, keine Nutzer auf dem System):
Datenbank trennen.
Datenbank anfügen -> im Dialog bei Datenbankdetails das Protokollfile über "entfernen" ... entfernen
mit Ok bestätigen.
Danach ist das Logfile erstmal mimimal und wieder vorhanden. Setze es nun auf einen Wert und mache das absolute Wachstum absolut, d.h. keine Prozentzahlen sondern eine Zweierpotenz in MB, z.B. 512MB (was m.E. sehr viel ist, aber je nach Datenbanknutzung...)
---
Gruss
Grinskeks