HowTo: Verkleinern einer Microsoft SQL Server Datenbank

Der normale Weg, eine Microsoft SQL Server-Datenbank zu verkleinern ist über das Microsoft SQL Server Management Studio bzw. über den Befehl DBCC SHRINKDATABASE.
Doch was ist, wenn die Datei der Datenbank nicht kleiner wird? Was ist, wenn die Datenbank anzeigt, dass nur noch ein paar Megabyte Speicherplatz frei sind, obwohl Sie jede Menge Datensätze aus der Datenbank gelöscht haben?
Das ist mir mit einer Datenbank passiert. Inhalt: ca. 40 Mio. Datensätze, Größe: ca. 12 GB. Nachdem ich ca. 34 Mio. Datensätze gelöscht habe, zeigte die Datenbank über das SQL Server Management Studio an, dass gerade einmal 12 MB freier Speicher verfügbar war. DBCC SHRINKFILE oder DBCC SHRINKDATABASE haben keine Dateiverkleinerung erreicht. Die Größe blieb bei 12 GB.

Warum?

SQL Server schreibt die Daten in die Datenbank und erstellt sogenannte Seiten für die Indizes. Eine Seite hat eine bestimmte Größe. Ist eine Seite mit einer bestimmten Anzahl an Index-Einträgen gefüllt, dann wird eine neue Seite erstellt, auf die dann die nächsten Index-Daten geschrieben werden. So viel grob gesagt zur Technik.
Die Indizes in meiner Datenbank waren also auf viele Seiten verteilt, und diese Seiten wiederum in der gesamten Datei. DBCC SHRINKFILE und DBCC SHRINKDATABASE können zwar Speicher in der Datei reorganisieren, allerdings nichts gegen die Fragmentation der Seiten tun.

Die Lösung

Um die Seiten zu reorganisieren müssen Sie die Indizes in der Datenbank neu aufbauen und können danach die Befehle DBCC SHRINKFILE oder DBCC DBCC SHRINKDATABASE  ausführen.

2 Möglichkeiten

Hierfür haben Sie 2 Möglichkeiten zur Verfügung.

  1. Über ein Skript
  2. Über das Microsoft SQL Server Management Studio

Das Skript

Das Skript, das Sie für die Ausführung der Reorganisation benötigen, finden Sie als Nächstes in diesem Post. Die Ausführung über das Microsoft SQL Server Management Studio finden Sie als Videotutorial in diesem Post.
Das folgende Skript führt die Reorganisation der Indizes in der Datenbank aus und sorgt für eine Füllung der Seiten zu 90%.

USE DatenbankName -- Geben Sie den Namen der Datenbank an, deren
                  -- Indizes neu aufgebaut werden sollen

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Da mit DBCC DBREINDEX immer nur einzelne Tabellen bearbeitet werden können, geht dieses Skript jede Tabelle einzeln durch.

Die Reorganisation via Microsoft SQL Server Management Studio

Nach der Ausführung der Reorganisation funktioniert dann auch der DBCC SHRINKDATABASE Befehl:

use DatenbankName
go
dbcc shrinkdatabase    (DatenbankName, NOTRUNCATE)
go
dbcc shrinkdatabase    (DatenbankName, TRUNCATEONLY)

Danach war die Datenbank von 12 GB auf 2 GB geschrumpft.

Achtung!

Die Ausführung von DBCC DBREINDEX ist ein Offline-Vorgang, d.h. die Tabelle wird für die Zeit der Ausführung gesperrt und Benutzer können nicht darauf zugreifen. Deshalb empfehle ich, diesen Vorgang außerhalb der normalen Betriebszeiten auszuführen.

Wenn Sie Anregungen zu der Anleitung haben oder Kommentare, so zögern Sie nicht, hier zu kommentieren. Ich freue mich über Ihr Feedback.

  • John

    Danke. Hat mir geholfen.

    John

    • http://www.frankpiotrowsky.com/ Frank Piotrowsky

      Sehr gerne, John.

      Diese Verkleinerung ist ein Prozess, der nicht sofort offensichtlich oder intuitiv ist. Ich musste auch lange recherchieren, um dahinter zu kommen. Aber die Arbeit hat sich gelohnt, wenn ich helfen konnte. :-D

  • John

    Danke. Hat mir geholfen.

    John

    • http://www.frankpiotrowsky.com/ Frank Piotrowsky

      Sehr gerne, John.

      Diese Verkleinerung ist ein Prozess, der nicht sofort offensichtlich oder intuitiv ist. Ich musste auch lange recherchieren, um dahinter zu kommen. Aber die Arbeit hat sich gelohnt, wenn ich helfen konnte. :-D

  • Mish

    Wie lange hat Reindex für die 12gb Datenbank gedauert?

    • http://www.frankpiotrowsky.com/ Frank Piotrowsky

      Hallo @mikesh333,

      ich wollte Ihnen eigentlich auf Youtube (http://www.youtube.com/watch?v=n6Y1hlqrflU) antworten, da Sie dort ebenfalls gefragt haben, habe dort aber nur 500 Zeichen zur Verfügung… :-(. Deswegen hier. Die Frage auf Youtube bezog sich darauf, wie lange die Reindizierung der 40 Mio. Einträge gedauert hat.

      Da ich ca. 34 Mio. Einträge gelöscht hatte – es waren Meta-Daten von Transport-Aufträgen, die via EDIFact verschickt wurden – kann ich nicht sagen, wie lange eine Reindizierung bei 40 Mio. Datensätzen dauert. Das hängt im Wesentlichen von Ihrer unterliegenden Hardware – hier besonders die Festplatten – ab. Liegt die Datenbank auf nur einer Festplatte wird das länger dauern, als wenn sie auf einem RAID-Verbund von Platten liegt, oder vielleicht sogar auf mehrere Rechner verteilt wurde.

      Die Reindizierung der ca. 6 Mio. verbliebenen Datensätze hat in meinem Fall (wie im Video zu sehen – unten rechts im SQL Server Management-Studio) ca. 25 Min. gedauert und ist auf einem RAID-5 mit 3 Festplatten ausgeführt worden. Die CPU war ein einfacher Xeon 4-Kern Prozessor. Seine Taktung kann ich aber aufgrund der lange zurückliegenden Zeit nicht mehr sagen.

      Hoffe, das hat geholfen?

      Beste Grüße,

      Frank Piotrowsky

  • Mish

    Wie lange hat Reindex für die 12gb Datenbank gedauert?

    • http://www.frankpiotrowsky.com/ Frank Piotrowsky

      Hallo @mikesh333,

      ich wollte Ihnen eigentlich auf Youtube (http://www.youtube.com/watch?v…) antworten, da Sie dort ebenfalls gefragt haben, habe dort aber nur 500 Zeichen zur Verfügung… :-(. Deswegen hier. Die Frage auf Youtube bezog sich darauf, wie lange die Reindizierung der 40 Mio. Einträge gedauert hat.

      Da ich ca. 34 Mio. Einträge gelöscht hatte – es waren Meta-Daten von Transport-Aufträgen, die via EDIFact verschickt wurden – kann ich nicht sagen, wie lange eine Reindizierung bei 40 Mio. Datensätzen dauert. Das hängt im Wesentlichen von Ihrer unterliegenden Hardware – hier besonders die Festplatten – ab. Liegt die Datenbank auf nur einer Festplatte wird das länger dauern, als wenn sie auf einem RAID-Verbund von Platten liegt, oder vielleicht sogar auf mehrere Rechner verteilt wurde.

      Die Reindizierung der ca. 6 Mio. verbliebenen Datensätze hat in meinem Fall (wie im Video zu sehen – unten rechts im SQL Server Management-Studio) ca. 25 Min. gedauert und ist auf einem RAID-5 mit 3 Festplatten ausgeführt worden. Die CPU war ein einfacher Xeon 4-Kern Prozessor. Seine Taktung kann ich aber aufgrund der lange zurückliegenden Zeit nicht mehr sagen.

      Hoffe, das hat geholfen?

      Beste Grüße,

      Frank Piotrowsky

  • Lutz

    Vielen Dank für das HowTo, das mir sehr geholfen :-). Bin noch etwas “neu” im SQL-Server Umfeld, daher ist solche eine Anleitung echt Gold Wert ;-)…

  • Lutz

    Vielen Dank für das HowTo, das mir sehr geholfen :-). Bin noch etwas “neu” im SQL-Server Umfeld, daher ist solche eine Anleitung echt Gold Wert ;-)…

  • Michael Turlo

    Hallo ,

    ich bin extrem begeistert von diesem Beitrag – ich habe gerade eine 120 GB DB durch löschen der Transaktions Logs und der Neuerstellung des Indexes und Optimierung die Datenbank auf 22GB gedrückt.

    Weiter so!

    Liebe Grüße,
    Michael

  • Michael Turlo

    Hallo ,

    ich bin extrem begeistert von diesem Beitrag – ich habe gerade eine 120 GB DB durch löschen der Transaktions Logs und der Neuerstellung des Indexes und Optimierung die Datenbank auf 22GB gedrückt.

    Weiter so!

    Liebe Grüße,
    Michael

  • Pingback: SQL by sundrummer - Pearltrees

  • Pingback: Offen: Datenbank Defekt ? Was tun ?

  • Dejan

    Hallo Frank,
    Ich wollte Dich fragen ob Du in Richtung AD-Analyse vertraut bist? Ich würde gerne Attribute von Usern/Gruppen aus der AD entziehen. Hast Du Erfahrung in diesem Bereich? Und kann man das auch bei einem FileServer? Gibts da evtl. Tools dafür? Besten Dank

  • Dejan

    Hallo Frank,
    Ich wollte Dich fragen ob Du in Richtung AD-Analyse vertraut bist? Ich würde gerne Attribute von Usern/Gruppen aus der AD entziehen. Hast Du Erfahrung in diesem Bereich? Und kann man das auch bei einem FileServer? Gibts da evtl. Tools dafür? Besten Dank