Oktober 8

HowTo: Verkleinern einer Microsoft SQL Server Datenbank

HowTo

8  comments

Lesedauer ca.  Minuten

Dieser Artikel beschreibt, wie Sie eine Mirosoft SQL Server-Datenbank verkleinern und die Datei auf der Festplatte tatsächlich schrumpfen lassen können.

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 schrumpft die Datei nicht?

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. 1
    Über ein Skript
  2. 2
    Über das Microsoft SQL Server Management Studio

1. 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.

2. Die Reorganisation via Microsoft SQL Server Management Studio

Die Reorganisation via Microsoft SQL Server Management Studio zeige ich Ihnen im Video.

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.



Über den Autor

Frank Piotrowsky ist IT-Consultant und beschäftigt sich mit der Automatisierung von Arbeitsprozessen, Netzwerkadministration und deren Automatisierung, sowie mit Software-Entwicklung, die ebenfalls in diese Richtung zielt. "Wenn der Computer die Arbeit für Sie erledigen kann, warum sollten Sie sie dann tun?" ist die Philosophie, die hinter dem Thema steckt.

Tags

2005, Datenbank, Microsoft, Performancesteigerung, Server, Skripting, Speicherverkleinerung, SQL, T-SQL


Das könnte Sie auch interessieren:

E-Mail Verschlüsselung Microsoft 365

E-Mail Verschlüsselung Microsoft 365

Remotedesktop über Gateway ohne VPN verbinden

Remotedesktop über Gateway ohne VPN verbinden

Remotedesktopverbindung: RDG Zertifikat installieren

Remotedesktopverbindung: RDG Zertifikat installieren

  • Vielen Dank für die klasse Anleitung 😉
    Ich habe den Vorgang bei einer 670 GB großen Datenbank gestartet. Wie lange in etwa wird der Vorgang abgeschlossen sein? (Bis jetzt schon ca. 40h)

    VG

    • Hallo Christian,

      danke für das nette Feedback. Das ist schwer zu sagen, wie lange der Vorgang dauern kann. Das kommt auf die unterliegende Hardware an (Festplatten, SSDs, RAID, etc.), auf die Konfiguration der Datenbank (Single Server Instanz, dediziert, clustered, verteilt über mehrere Rechenzentren, etc.)
      Fakt ist, dass 670GB schon eine Größe ist, die Zeit braucht. Meiner Erfahrung nach läuft der SQL-Server sehr stabil und führt die Aufgaben, die man ihm gibt, zuverlässig aus.
      Wenn er den Vorgang abgeschlossen hat, würde mich interessieren, wie lange er gebraucht hat.

      • Hallo Frank,

        der Vorgang läuft mittlerweile gute fünf Tage. Ich weiß nicht, ob das so richtig ist… Leider kann ich Dir hier keinen Screenshot senden, damit man einmal sieht wie das jetzt ausschaut. Ich habe ihn noch nicht abgebrochen…

        VG
        Christian

      • Hallo Frank,
        ich habe den Vorgang nun nach 14 Tagen abgebrochen.
        Die Daten werden immer mehr obwohl so gut nicht mehr genutzt wird.

        Ich hatte das Script bei mir (auf kleineren Datenbanken) getestet. Einwandfrei 😉

        Wenn man das Script vielleicht in Etappen starten könnte – das wäre prima.

        VG
        Christian

  • 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

  • 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 ;-)…

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    >