Home > 2009 > 10 > 08 > HowTo: Verkleinern einer Microsoft SQL Server Datenbank

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.