Tipps & Tricks: Datenbanken richtig verkleinern

Manchmal ist es tatsächlich sinnvoll, Datenbanken zu verkleinern, damit ungenutzter Platz wieder freigegeben wird. Auch wenn viele ausgewiesene SQL-Experten wie Brent Ozar, Paul Randal das Verkleinern von Datenbanken generell verteufeln, gibt es doch Ausnahmefälle, in denen eine Verkleinerung angebracht ist, sofern man diese richtig durchführt und sich der Auswirkungen bewusst ist.

„normale“ Vergrößerung und Verkleinerung des Datenvolumens

In den meisten Datenbanken, werden Daten nicht nur gelesen, sondern auch geändert, hinzugefügt und gelöscht. Dabei sind die Datenbanken oft so konfiguriert, dass sie automatisch vergrößert werden, wenn durch Hinzufügen von neuen Daten mehr Platz benötigt wird. Das ist zwar nicht die optimale Variante (effektiver ist es, die Datenbank explizit so zu vergrößern, dass der benötigte Platz von vornherein schon zur Verfügung steht), aber immer noch besser als zu riskieren, dass keine Daten mehr geschrieben werden können, da das Datenfile komplett gefüllt ist. Wenn dagegen Daten aus der Datenbank gelöscht werden, so wird das eigentliche Datenvolumen zwar kleiner, das Datenfile aber nicht, da lediglich Lücken innerhalb des Datenfiles entstehen. Diesen Platz kann man nun für andere Dateien zur Verfügung stellen, indem man das Datenfile per SHRINK verkleinert. Dabei werden die Datenseiten vom Ende der Datei in die Lücken verschoben um diese zu schließen. Anschließend kann dann der nicht mehr verwendete Platz am Ende des Datenfiles freigegeben werden. Das klingt zwar auf den ersten Blick ganz gut, bringt jedoch diverse Probleme mit sich, weshalb allgemein empfohlen wird, Datenbanken nicht zu shrinken:

1. Durch das Verschieben der Datenseiten im File werden die Indizes massiv fragmentiert. Während also die externe Fragmentierung der Datendatei aufgelöst wird, entsteht eine interne Fragmentierung der Indizes, was schnell dazu führen kann, dass diese nicht mehr verwendet werden können.
2. Wenn später wieder neue Daten in die Datenbank geschrieben werden, muss das Datenfile wieder wachsen, was zusätzlichen Aufwand bedeutet und außerdem zu erneuter externer Fragmentierung führt.

Aus diesen Gründen ist insbesondere das regelmäßige Verkleinern von Datenbanken über SQL Agent Jobs im Zuge von Wartungstasks keine gute Idee. Schlimmer noch ist die Verwendung der AUTO SHRINK Option in den Datenbankeigenschaften, da dies in Kombination mit der Automatischen vergrößerung zu einem ständigen Verkleinern und Vergrößern der Datenbankdatei führt, was eine hohe I/O-Last zur Folge hat und die Datenbank extrem fragmentiert. Geoff N. Hiten zieht daher in seinem Blog treffenderweise die Analogie von Auto-Shrink zu Auto-Fragmenting.

Wenn schon verkleinern, dann aber richtig!

Aber wie bereits eingangs erwähnt, bin ich der festen Überzeugung, dass es gelegentlich auch Situationen gibt, in denen ein Shrink sinnvoll sein kann. Das ist genau dann der Fall, wenn aufgrund einer größeren Bereinigungsaktion große Datenmengen aus der Datenbank gelöscht werden. Achtung: Ich rede hier nicht von regelmäßigen (z.B. jährlichen) Bereinigungsaktionen, denn dann kann man sich den Aufwand für den Shrink sparen, da der Platz bis zur nächsten Bereinigungsaktion ohnehin wieder benötigt wird. Es geht vielmehr um einmalige Aktionen, beispielsweise wenn eine große Tabelle mit mehreren hundert GB komplett entfernt wird, so dass der damit frei werdende Platz auch in absehbarer Zeit nicht durch das natürliche Wachstum der Datenbank benötigt wird.

Was ist nun zu beachten, um bei dieser SHRINK-Aktion keinen Schaden anzurichten:

1. Nach dem Shrink eine Defragmentierung der Indizes durchführen.
2. Genügend Zeit einplanen (sowohl Shrink als auch Index-Defrag können sehr lange dauern).
3. Notfalls in mehreren kleinen Steps (z.B. 50 GB) Shrinken und danach jeweils Defragmentierung durchführen, dann kann die Aktion auf mehrere Wartungsfenster verteilt werden. Allerdings muss man sich dann bewusst sein, dass sich die Gesamtdauer der Aktion durch die mehrfach notwendige Index-Defragmentierung weiter erhöht.
4. Wenn der Shrink unbeaufsichtigt (z.B. am Wochenende) läuft, einen separaten SQL Agent-Job einplanen, der den Shrink-Prozess rechtzeitig abbricht (falls dieser noch nicht fertig ist) und den Index-Defrag startet. Das Shrinken selbst kann jederzeit problemlos abgebrochen werden (selbst durch einen KILL des Prozesses). Die bis dahin verschobenen Speicherseiten bleiben an der neuen Position, auch wenn sich das Datenfile selbst erst nach komplettem Abschluss einer Shrink-Anweisung verkleinert. Bei erneuter Ausführung der Shrink-Anweisung wird dann im Prinzip an derselben Stelle weiter gemacht.
5. Der Shrink sollte nie bis zur kleinstmöglichen Datenbankgröße durchgeführt werden. Stattdessen immer das natürliche Wachstum der Datenbank mit einplanen, so dass nicht kurz danach wieder ein Vergrößern des Datenfiles erforderlich wird (und zu erneuter externer Fragmentierung führt).

Zusammenfassend kann man also festhalten:

• Regelmäßiges SHRINKen ist böse!
• Auto-Shrink ist sehr böse!
• Aber: Gezieltes Shrinken kann in Ausnahmefällen sinnvoll sein, wenn man danach auch die Indizes defragmentiert!

Weitere Blog-Beiträge zum Thema:

• Brent Ozar: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now
• Paul Randal: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files
• Geoff N. Hiten: http://weblogs.sqlteam.com/geoffh/archive/2009/03/19/Auto-Fragmenting-Databases.aspx
• Thomas Larock: http://thomaslarock.com/2009/03/when-to-use-auto-shrink
• Mike Walsh: http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button