DBCC SHRINKDATABASE (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse Analytics

Reduziert die Größe der Daten- und Protokolldateien in der angegebenen Datenbank.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Syntax für Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

Argumente

database_name | database_id | 0

Dies ist der Name oder die ID der Datenbank, die verkleinert werden soll. Mit „0“ (null) wird angegeben, dass die aktuelle Datenbank verwendet wird.

target_percent

Dies ist der gewünschte Prozentsatz an freiem Speicherplatz, der in der Datenbankdatei übrig bleiben soll, nachdem die Datenbank verkleinert wurde.

NOTRUNCATE

Hiermit werden zugewiesene Seiten vom Dateiende zu nicht zugewiesenen Seiten am Dateianfang verschoben. Mit dieser Aktion werden die Daten in der Datei komprimiert. target_percent ist optional. Azure Synapse Analytics unterstützt diese Option nicht.

Der freie Speicherplatz am Dateiende wird nicht an das Betriebssystem zurückgegeben, und die physische Größe der Datei bleibt unverändert. Daher scheint die Datenbank bei Angabe von NOTRUNCATE nicht kleiner zu werden.

NOTRUNCATE kann nur auf Datendateien angewendet werden. NOTRUNCATE wirkt sich nicht auf die Protokolldatei aus.

TRUNCATEONLY

Hiermit wird der gesamte freie Speicherplatz am Ende der Datei für das Betriebssystem freigegeben. Seiten in der Datei werden nicht verschoben. Die Datendatei wird nur auf die zuletzt zugewiesene Erweiterung verkleinert. Das Argument target_percent wird ignoriert, wenn es mit TRUNCATEONLY angegeben wird. Azure Synapse Analytics unterstützt diese Option nicht.

DBCC SHRINKDATABASE mit der Option TRUNCATEONLY wirkt sich nur auf die Protokolldatei für die Datenbanktransaktion aus. Um die Datendatei abzuschneiden, verwenden Sie stattdessen DBCC SHRINKFILE. Weitere Informationen finden Sie unter DBCC SHRINKFILE.

WITH NO_INFOMSGS

Unterdrückt alle Informationsmeldungen mit einem Schweregrad von 0 bis 10.

WAIT_AT_LOW_PRIORITY mit Verkleinerungsvorgängen

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Das Feature zum Warten bei niedriger Priorität verringert Sperrkonflikte. Weitere Informationen finden Sie unter Grundlegendes zu Parallelitätsproblemen mit DBCC SHRINKDATABASE.

Dieses Feature ähnelt WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen, weist aber einige Unterschiede auf.

• ABORT_AFTER_WAIT kann nicht auf NONE festgelegt werden.

WAIT_AT_LOW_PRIORITY

Wenn ein Verkleinerungsbefehl im Modus WAIT_AT_LOW_PRIORITY ausgeführt wird, werden neue Abfragen, die Schemastabilitätssperren (Sch-S-Sperren) erfordern, nicht durch den wartenden Verkleinerungsvorgang blockiert, bis die Ausführung des Verkleinerungsvorgangs gestartet wird. Der Verkleinerungsvorgang wird ausgeführt, wenn er eine Schemaänderungssperre (Schema Modify, Sch-M) abrufen kann. Wenn ein neuer Verkleinerungsvorgang im Modus WAIT_AT_LOW_PRIORITY aufgrund einer zeitintensiven Abfrage keine Sperre abrufen kann, tritt für den Verkleinerungsvorgang nach einer Minute ein Timeout auf, und er wird ohne Fehler beendet.

Wenn ein neuer Verkleinerungsvorgang im Modus WAIT_AT_LOW_PRIORITY aufgrund einer zeitintensiven Abfrage keine Sperre abrufen kann, tritt für den Verkleinerungsvorgang nach einer Minute ein Timeout auf, und er wird ohne Fehler beendet. Dieser Fall tritt ein, wenn der Verkleinerungsvorgang aufgrund mindestens einer gleichzeitigen Abfrage, die eine Sch-S-Sperre aufrechterhält, keine Sch-M-Sperre abrufen kann. Wenn ein Timeout auftritt, wird eine Meldung mit dem Fehler 49516 an das SQL Server-Fehlerprotokoll gesendet. Beispiel: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. An diesem Punkt können Sie den Verkleinerungsvorgang im Modus WAIT_AT_LOW_PRIORITY einfach wiederholen, da Sie wissen, dass dies keine Auswirkungen auf die Anwendung hat.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

• SELF

  SELF ist die Standardoption. Beenden Sie den SHRINKDATABASE-Vorgang, der aktuell ausgeführt wird, ohne eine Aktion durchzuführen.

• BLOCKERS

  Bricht alle Benutzertransaktionen ab, die den SHRINKDATABASE-Vorgang blockieren, sodass der Vorgang fortgesetzt werden kann. Die Option BLOCKERS setzt voraus, dass bei der Anmeldung die Berechtigung ALTER ANY CONNECTION vorliegt.

Resultsets

In der folgenden Tabelle werden die Spalten des Resultsets beschrieben:

Spaltenname	BESCHREIBUNG
DbId	Die Datenbank-ID der Datei, die das Datenbank-Engine zu verkleinern versuchte.
FileId	Datei-ID der Datei, die das Datenbank-Engine zu verkleinern versuchte.
CurrentSize	Die Anzahl von 8-KB-Seiten, die die Datei derzeit belegt.
MinimumSize	Die Anzahl von 8-KB-Seiten, die die Datei minimal belegen könnte. Dieser Wert entspricht der Mindestgröße bzw. der ursprünglich erzeugten Dateigröße.
UsedPages	Die Anzahl von 8-KB-Seiten, die derzeit von der Datei verwendet werden.
EstimatedPages	Die Anzahl an 8-KB-Seiten, auf die die Datei wahrscheinlich vom Datenbank-Engine verkleinert werden kann.

Hinweis

Das Datenbank-Engine zeigt keine Zeilen für Dateien an, die nicht verkleinert wurden.

Bemerkungen

Hinweis

In Azure Synapse wird die Ausführung eines Verkleinerungsbefehls nicht empfohlen, da es sich um einen E/A-intensiven Vorgang handelt, durch den Ihr dedizierter SQL-Pool (früher SQL DW) offline geschaltet werden kann. Darüber hinaus ändern sich die Kosten Ihrer Datawarehouse-Momentaufnahmen nach dem Ausführen dieses Befehls.

Zum Verkleinern aller Daten- und Protokolldateien einer bestimmten Datenbank führen Sie den DBCC SHRINKDATABASE-Befehl aus. Führen Sie zum Verkleinern einer einzelnen Daten- oder Protokolldatei einer bestimmten Datenbank den Befehl DBCC SHRINKFILE aus.

Führen Sie sp_spaceused zum Anzeigen des aktuellen freien (nicht zugeordneten) Speicherplatzes in der Datenbank aus.

DBCC SHRINKDATABASE-Vorgänge können an jeder Stelle des Vorgangs beendet werden, wobei der bereits abgeschlossene Anteil erhalten bleibt.

Die Datenbank kann nicht kleiner als die konfigurierte Mindestgröße der Datenbank sein. Die Mindestgröße legen Sie bei der ursprünglichen Erstellung der Datenbank fest. Die Mindestgröße kann alternativ die letzte explizit festgelegte Größe sein, die mithilfe eines Vorgangs für die Dateigrößenänderung festgelegt wurde. Vorgänge wie DBCC SHRINKFILE oder ALTER DATABASE sind Beispiele für Vorgänge für Dateigrößenänderungen.

Stellen Sie sich eine Datenbank vor, die ursprünglich mit einer Größe von 10 MB erstellt wurde. Dann erreicht diese Datenbank eine Größe von 100 MB. Die Mindestgröße, auf die die Datenbank verkleinert werden kann, beträgt 10 MB. Dies gilt auch, wenn alle Daten in der Datenbank gelöscht werden.

Legen Sie beim Ausführen von DBCC SHRINKDATABASE entweder die Option NOTRUNCATE oder TRUNCATEONLY fest. Wenn Sie dies nicht tun, ist das Ergebnis dasselbe, als wenn Sie einen DBCC SHRINKDATABASE-Vorgang mit NOTRUNCATE und anschließend einen DBCC SHRINKDATABASE-Vorgang mit TRUNCATEONLY ausführen.

Die verkleinerte Datenbank muss sich nicht im Einzelbenutzermodus befinden. Andere Benutzer können an der Datenbank arbeiten, wenn sie verkleinert wird. Dies gilt auch für Systemdatenbanken.

Sie können eine Datenbank nicht verkleinern, während sie gesichert wird. Sie können eine Datenbank hingegen auch nicht sichern, während ein Verkleinerungsvorgang für die Datenbank durchgeführt wird.

Bei Angabe mit WAIT_AT_LOW_PRIORITY wartet die Sch-M-Sperranforderung des Verkleinerungsvorgangs beim Ausführen des Befehls 1 Minute lang mit niedriger Priorität. Wenn der Vorgang während des Zeitraums blockiert wird, wird die angegebene ABORT_AFTER_WAIT-Aktion ausgeführt.

Funktionsweise von DBCC SHRINKDATABASE

DBCC SHRINKDATABASE verkleinert Datendateien pro Datei, Protokolldateien jedoch so, als lägen alle Protokolldateien in einem zusammenhängenden Protokollpool vor. Dateien werden immer am Ende verkleinert.

Angenommen Sie verfügen über mehrere Protokolldateien, eine Datendatei und eine Datenbank namens mydb. Die Datendatei und die Protokolldateien sind jeweils 10 MB groß, die Datendatei enthält 6 MB an Daten. Die Datenbank-Engine berechnet für jede Datei eine Zielgröße. Auf diesen Wert soll die Datei verkleinert werden. Wenn DBCC SHRINKDATABASE mit target_percent angegeben wird, berechnet die Datenbank-Engine die Zielgröße als prozentualen Anteil (target_percent) des freien Speicherplatzes in der Datei nach der Verkleinerung.

Wenn Sie für die Verkleinerung von mydb beispielsweise den target_percent-Wert 25 angeben, berechnet die Datenbank-Engine die Zielgröße für die Datendatei mit 8 MB (6 MB Daten plus 2 MB freier Speicherplatz). Daher verschiebt die Datenbank-Engine alle Daten aus den letzten 2 MB der Datendatei in beliebigen freien Speicherplatz in den ersten 8 MB der Datendatei und verkleinert dann die Datei.

Angenommen, die Datendatei von mydb enthält 7 MB Daten. Durch die Angabe eines target_percent-Werts von 30 kann diese Datendatei auf einen freien Prozentsatz von 30 verkleinert werden. Wenn Sie jedoch eine unter target_percent eine Prozentvorgabe von 40 angeben, wird die Datendatei nicht verkleinert, da nicht genügend freier Speicherplatz in der aktuellen Gesamtgröße der Datendatei erstellt werden kann.

Sie können dieses Problem anders betrachten: 40 Prozent benötigter freier Speicherplatz plus eine zu 70 Prozent volle Datendatei (7 MB von 10 MB) ergeben mehr als 100 Prozent. Bei target_percent-Werten über 30 wird die Datendatei nicht verkleinert. Sie wird nicht verkleinert, weil der gewünschte freie Prozentsatz plus dem aktuell von der Datendatei belegten Prozentsatz einen Wert über 100 % ergibt.

Für Protokolldateien verwendet die Datenbank-Engine target_percent, um die Zielgröße für das gesamte Protokoll zu berechnen. Deshalb entspricht target_percent nach dem Verkleinerungsvorgang dem freien Speicherplatz im Protokoll. Die Zielgröße für das gesamte Protokoll wird dann in eine Zielgröße für jede Protokolldatei umgewandelt.

DBCC SHRINKDATABASE versucht, jede physische Protokolldatei sofort auf ihre Zielgröße zu verkleinern. Angenommen, abgesehen von der Dateigröße der Protokolldatei bleiben keine Bestandteile des logischen Protokolls in den virtuellen Protokollen zurück. Die Datei wird dann erfolgreich gekürzt, und der Vorgang DBCC SHRINKDATABASE wird ohne Meldungen abgeschlossen. Wenn sich dagegen ein Teil des logischen Protokolls in den virtuellen Protokollen befindet, die außerhalb der Zielgröße liegen, gibt die Datenbank-Engine so viel Speicherplatz wie möglich frei und gibt dann eine Informationsmeldung aus. Die Meldung beschreibt, welche Aktionen erforderlich sind, um das logische Protokoll aus den virtuellen Protokollen am Ende der Datei zu verschieben. Nachdem diese Aktionen ausgeführt wurden, kann der verbleibende Speicherplatz mit DBCC SHRINKDATABASE freigegeben werden.

Protokolldateien können nur auf den Grenzwert für virtuelle Protokolldateien verkleinert werden. Aus diesem Grund können Protokolldateien nicht auf eine kleinere Größe als virtuelle Protokolldateien verkleinert werden. Dies ist möglicherweise auch nicht möglich, wenn sie nicht verwendet werden. Die Größe der virtuellen Protokolldatei wird dynamisch vom Datenbank-Engine ausgewählt, wenn Protokolldateien erstellt oder erweitert werden.

Grundlegendes zu Parallelitätsproblemen mit DBCC SHRINKDATABASE

Die Befehle zum Verkleinern einer Datenbank oder Datei können zu Parallelitätsproblemen führen. Das gilt insbesondere bei der aktiven Wartung – z. B. beim Neustellen von Indizes – und in ausgelasteten OLTP-Umgebungen. Wenn Ihre Anwendung Abfragen für Datenbanktabellen ausführt, rufen diese Abfragen eine Schemastabilitätssperre (Sch-S-Sperre) ab und erhalten sie aufrecht, bis die Abfragen ihre Vorgänge abschließen. Beim Versuch, während der regulären Nutzung Speicherplatz freizugeben, benötigen Datenbank- und Dateiverkleinerungsvorgänge aktuell eine Schemaänderungssperre (Schema Modify, Sch-M), wenn IAM-Seiten (Index Allocation Map) verschoben oder gelöscht werden. Dadurch werden die Sch-S-Sperren blockiert, die von Benutzerabfragen benötigt werden. Das führt dazu, dass zeitintensive Abfragen einen Verkleinerungsvorgang blockieren, bis die Abfragen abgeschlossen sind. Das bedeutet, dass alle neuen Abfragen, die Sch-S-Sperren erfordern, ebenfalls hinter dem wartenden Verkleinerungsvorgang in die Warteschlange eingereiht und ihrerseits blockiert werden, was das Parallelitätsproblem weiter verschärft. Dies kann sich erheblich auf die Anwendungsabfrageleistung auswirken und führt auch zu Schwierigkeiten beim Abschließen der erforderlichen Wartung, um Datenbankdateien zu verkleinern. Das in SQL Server 2022 (16.x) eingeführte Feature zum Warten mit Verkleinerungsvorgängen bei niedriger Priorität (WLP) behandelt dieses Problem mithilfe einer Schemaänderungssperre im Modus WAIT_AT_LOW_PRIORITY. Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY mit Verkleinerungsvorgängen.

Weitere Informationen zu Sch-S- und Sch-M-Sperren finden Sie im Handbuch zu Transaktionssperren und Zeilenversionsverwaltung.

Bewährte Methoden

Berücksichtigen Sie die folgenden Informationen, wenn Sie eine Datenbank verkleinern möchten:

• Ein Verkleinerungsvorgang ist am effektivsten nach einem Vorgang, durch den nicht verwendeter Speicherplatz bereitgestellt wird, z. B. das Abschneiden oder Löschen einer Tabelle.
• Die meisten Datenbanken erfordern verfügbaren freien Speicherplatz für die normalen alltäglichen Vorgänge. Wenn Sie eine Datenbankdatei wiederholt verkleinern und feststellen, dass die Datenbankgröße wieder zunimmt, deutet das darauf hin, dass der freie Speicherplatz für normale Vorgänge benötigt wird. In diesem Fall ist das Verkleinern der Datenbankdatei vergeblich. Automatische Vergrößerungsereignisse, die zum Vergrößern der Datenbankdatei erforderlich sind, beeinträchtigen die Leistung.
• Bei einem Verkleinerungsvorgang bleibt der Fragmentierungszustand der Indizes in der Datenbank nicht erhalten. Im Allgemeinen wird die Fragmentierung zu einem gewissen Grad verstärkt. Dies ist ein weiterer Grund, die Datenbank nicht wiederholt zu verkleinern.
• Legen Sie die Datenbankoption AUTO_SHRINK nicht auf „ON“ fest, es sei denn, besondere Anforderungen erfordern dies.

Problembehandlung

Es kann vorkommen, dass Verkleinerungsvorgänge durch eine Transaktion blockiert werden, die auf einer auf Zeilenversionsverwaltung basierenden Isolationsstufe ausgeführt wird. Beispiel: Ein umfangreicher Löschvorgang wird auf einer auf Zeilenversionsverwaltung basierenden Isolationsstufe ausgeführt, wenn ein DBCC SHRINKDATABASE-Vorgang ausgeführt wird. In diesem Fall wartet der Verkleinerungsvorgang auf den Abschluss des Löschvorgangs, bevor die Dateien verkleinert werden. Wenn der Verkleinerungsvorgang wartet, wird von den DBCC SHRINKFILE- und DBCC SHRINKDATABASE-Vorgängen eine Informationsmeldung (5202 für SHRINKDATABASE und 5203 für SHRINKFILE) ausgegeben. Diese Meldung zeigt in der ersten Stunde alle fünf Minuten und dann jede Stunde das SQL Server-Fehlerprotokoll an. Das Fehlerprotokoll kann beispielsweise eine Fehlermeldung wie die folgende enthalten:

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Dieser Fehler bedeutet, dass Momentaufnahmetransaktionen, mit Zeitstempeln über 109 den Verkleinerungsvorgang blockieren. Diese Transaktion ist die letzte, die der Verkleinerungsvorgang abschließt. Außerdem wird angezeigt, dass die Spalte transaction_sequence_num oder first_snapshot_sequence_num in der dynamischen Verwaltungssicht sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) den Wert 15 enthält. Die Spalte transaction_sequence_num oder first_snapshot_sequence_num in der Sicht enthalten möglicherweise eine Zahl, die kleiner als die der letzten Transaktion ist, die vom Verkleinerungsvorgang (109) abgeschlossen wurde. Wenn dies der Fall ist, wartet der Verkleinerungsvorgang auf den Abschluss dieser Transaktionen.

Führen Sie eine der folgenden Aufgaben aus, um das Problem zu beheben:

• Beenden Sie die Transaktion, die den Verkleinerungsvorgang blockiert.
• Beenden Sie den Verkleinerungsvorgang. Die bereits abgeschlossene Arbeit bleibt erhalten.
• Führen Sie keine besonderen Aktionen aus, und lassen Sie zu, dass mit dem Verkleinerungsvorgang gewartet wird, bis die blockierende Transaktion abgeschlossen ist.

Berechtigungen

Erfordert die Mitgliedschaft in der festen Serverrolle sysadmin oder der festen Datenbankrolle db_owner .

Beispiele

A. Verkleinern einer Datenbank und Angeben des freien Speicherplatzes in Prozent

Im folgenden Beispiel wird die Größe der Daten- und Protokolldateien in der UserDB-Benutzerdatenbank so verringert, dass die Datenbank 10 % freien Speicherplatz enthält.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Abschneiden einer Datenbank

Im folgenden Beispiel werden die Daten- und Protokolldateien in der AdventureWorks2022-Beispieldatenbank auf die letzte zugeordnete Erweiterung verkleinert.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Verkleinern einer Azure Synapse Analytics-Datenbank

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D: Verkleinern einer Datenbank mit WAIT_AT_LOW_PRIORITY

Das folgende Beispiel versucht, die Größe der Daten- und Protokolldateien in der AdventureWorks2022-Benutzerdatenbank so zu verringern, dass die Datenbank 20 % freien Speicherplatz enthält. Sollte innerhalb einer Minute keine Sperre abgerufen werden können, wird der Verkleinerungsvorgang abgebrochen.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Weitere Informationen

• Überlegungen zu den Einstellungen für automatische Vergrößerung und Verkleinerung in SQL Server
• Datenbankdateien und Dateigruppen
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)

Nächste Schritte

• ALTER DATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• Verkleinern einer Datenbank
• Verkleinern einer Datei