DBCC SHRINKFILE (Transact-SQL)

Artikel
03/18/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Reduziert die Größe der angegebenen Daten oder Protokolldateien der aktuellen Datenbank. Damit können Sie Daten von einer Datei in andere Dateien derselben Dateigruppe verschieben. So wird die Datei geleert und die Entfernung der Datenbank ermöglicht. Sie können eine Datei auf weniger als die Größe bei der Erstellung verkleinern, um die minimale Dateigröße auf den neuen Wert zurücksetzen.

Transact-SQL-Syntaxkonventionen

Syntax

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

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

file_name

Der logische Name der Datei, die verkleinert werden soll.

file_id

Die Datei-ID der Datei, die verkleinert werden soll. Verwenden Sie zum Ermitteln einer Datei-ID die FILE_IDEX-Systemfunktion, oder fragen Sie die sys.database_files-Katalogsicht in der aktuellen Datenbank ab.

target_size

Ein Integer stellt die neue Größe der Datei in Megabyte dar. Wenn nichts angegeben wird oder der Wert 0 ist, wird die Größe von DBCC SHRINKFILE auf die Dateigröße bei der Erstellung reduziert.

Sie können die Standardgröße einer leeren Datei mit DBCC SHRINKFILE <target_size> verringern. Wenn Sie z. B. eine 5 MB große Datei erstellen und die Dateigröße dann auf 3 MB herabsetzen, während die Datei noch leer ist, wird die Standarddateigröße auf 3 MB festgelegt. Dies gilt nur für leere Dateien, die nie Daten enthalten haben.

Diese Option wird in FILESTREAM-Dateigruppencontainern nicht unterstützt.

Wenn angegeben, versucht DBCC SHRINKFILE, die Datei auf target_size zu verkleinern. Verwendete Seiten im Bereich der freizugebenden Datei werden auf freien Speicherplatz in den beibehalten Bereichen der Datei verschoben. Beispielsweise verschiebt ein DBCC SHRINKFILE-Vorgang mit einer target_size von 8 bei einer Datendatei mit 10 MB alle verwendeten Seiten in den letzten 2 MB der Datei in alle nicht zugeordneten Seiten in den ersten 8 MB der Datei. DBCC SHRINKFILE verkleinert eine Datei nicht über die erforderliche Speichergröße hinaus. Werden beispielsweise 7 MB einer 10 MB großen Datendatei verwendet, verkleinert eine DBCC SHRINKFILE-Anweisung mit einem target_size-Wert von 6 die Datei lediglich auf 7 MB statt auf 6 MB.

EMPTYFILE

Verlagert alle Daten aus der angegebenen Datei in andere Dateien in derselben Dateigruppe. EMPTYFILE migriert die Daten also aus der angegebenen Datei zu anderen Dateien in derselben Dateigruppe. EMPTYFILE stellt sicher, dass keine neuen Daten zur Datei hinzugefügt werden, obwohl diese Datei nicht schreibgeschützt ist. Sie könne eine ALTER DATABASE-Anweisung verwenden, um eine Datei zu entfernen. Wenn zum Ändern der Dateigröße die ALTER DATABASE-Anweisung verwenden, wird das Flag „schreibgeschützt“ zurückgesetzt, und Daten können hinzugefügt werden.

Die Datei kann aus FILESTREAM-Dateigruppencontainern erst dann mit ALTER DATABASE entfernt werden, nachdem der FILESTREAM-Garbage Collector ausgeführt und alle nicht benötigten Dateigruppen-Containerdateien gelöscht wurden, die von EMPTYFILE in einen anderen Container kopiert wurden. Weitere Informationen finden Sie unter sp_filestream_force_garbage_collection. Informationen zum Entfernen eines FILESTREAM-Containers finden Sie im entsprechenden Abschnitt ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

NOTRUNCATE

Verschiebt zugeordnete Seiten vom Ende einer Datendatei in nicht zugeordnete Seiten am Dateianfang, und zwar mit oder ohne Angabe von target_percent. 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 Datei bei Angabe von NOTRUNCATE nicht verkleinert zu werden.

NOTRUNCATE kann nur auf Datendateien angewendet werden. Die Protokolldateien sind nicht betroffen.

Diese Option wird in FILESTREAM-Dateigruppencontainern nicht unterstützt.

TRUNCATEONLY

Gibt den gesamten freien Speicherplatz am Dateiende an das Betriebssystem frei, es werden jedoch keine Seiten innerhalb der Datei verschoben. Die Datendatei wird nur bis zum letzten zugeordneten Block verkleinert.

Der Parameter target_size wird ignoriert, wenn er mit TRUNCATEONLY angegeben wird.

Die Option TRUNCATEONLY verschiebt Informationen nicht in das Protokoll, entfernt jedoch inaktive VLFs am Ende der Protokolldatei. Diese Option wird in FILESTREAM-Dateigruppencontainern nicht unterstützt.

WITH NO_INFOMSGS

Alle Informationsmeldungen werden unterdrückt.

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

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

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 standardmäßig einer Minute ein Timeout auf, und er wird ohne Meldung 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 standardmäßig einer Minute ein Timeout auf, und er wird ohne Meldung 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 einfach den Verkleinerungsvorgang im Modus WAIT_AT_LOW_PRIORITY wiederholen, da Sie wissen, dass dies keine Auswirkungen auf die Anwendung hat.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

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

SELF

Beenden Sie den Vorgang zur Verkleinerung der Datei, der aktuell ausgeführt wird, ohne eine Aktion durchzuführen.
BLOCKERS

Bricht alle Benutzertransaktionen ab, die den Vorgang zum Verkleinern der Datei blockieren, sodass der Vorgang fortgesetzt werden kann. Die BLOCKERS-Option erfordert für die ALTER ANY CONNECTION-Berechtigung eine Anmeldung.

Resultsets

In der folgenden Tabelle sind die Resultsetspalten beschrieben.

Spaltenname	BESCHREIBUNG
DbId	Die Datenbank-ID der Datei, die das Datenbank-Engine zu verkleinern versuchte.
FileId	Die Datei-ID der Datei, die 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. Diese Zahl 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.

Hinweise

DBCC SHRINKFILE gilt für die Dateien der aktuellen Datenbank. Weitere Informationen zum Ändern der aktuellen Datenbank finden Sie unter USE (Transact-SQL).

Sie können den DBCC SHRINKFILE-Vorgang jederzeit beenden, und alle abgeschlossenen Vorgänge bleiben erhalten. Wenn Sie den Parameter EMPTYFILE verwenden und den Vorgang abbrechen, wird die Datei nicht markiert, um zu verhindern, dass zusätzliche Daten hinzugefügt werden.

Wenn ein DBCC SHRINKFILE-Vorgang fehlschlägt, wird ein Fehler ausgelöst.

Andere Benutzer können während der Dateiverkleinerung in der Datenbank arbeiten – die Datenbank muss nicht im Einzelbenutzermodus sein. Um die Systemdatenbanken zu verkleinern, muss auch SQL Server nicht im Einzelbenutzermodus ausgeführt werden.

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.

Grundlegendes zu Parallelitätsproblemen mit DBCC SHRINKFILE

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

Verkleinern einer Protokolldatei

Für Protokolldateien verwendet Datenbank-Enginetarget_size, um die Zielgröße des gesamten Protokolls zu berechnen. Daher ist target_size der freie Speicherplatz des Protokolls nach dem Verkleinern. Die Zielgröße des gesamten Protokolls wird dann in die Zielgröße der einzelnen Protokolldateien umgewandelt. DBCC SHRINKFILE versucht, jede physische Protokolldatei sofort auf ihre Zielgröße zu verkleinern. Wenn sich dagegen ein Teil des logischen Protokolls in den virtuellen Protokollen befindet, die außerhalb der Zielgröße liegen, gibt das Datenbank-Engine so viel Speicherplatz frei wie möglich 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 SHRINKFILE freigegeben werden.

Da eine Protokolldatei nur auf eine Grenze einer virtuellen Protokolldatei verkleinert werden kann, ist eine Verkleinerung der Protokolldatei auf eine geringere Größe als die einer virtuellen Protokolldatei u. U. nicht möglich, selbst wenn die Protokolldatei nicht verwendet wird. Datenbank-Engine wählt dynamisch die Größe des virtuellen Dateiprotokolls, wenn Protokolldateien erstellt oder erweitert werden.

Bewährte Methoden

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

Ein Verkleinerungsvorgang ist am effektivsten nach einem Vorgang, durch den umfangreicher 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. Ereignisse für automatisches Wachstum, 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 Fragmentierung ist ein weiterer Grund, die Datenbank nicht wiederholt zu verkleinern.
Verkleinern Sie mehrere Dateien in der gleichen Datenbank sequenziell statt gleichzeitig. Konflikte bei Systemtabellen können zu einer Blockierung führen und Verzögerungen verursachen.

Problembehandlung

In diesem Abschnitt wird beschrieben, wie Probleme, die beim Ausführen des DBCC SHRINKFILE-Befehls auftreten können, diagnostiziert und behoben werden.

Die Datei wird nicht verkleinert.

Wenn sich die Dateigröße nach einem fehlerfreien Verkleinerungsvorgang nicht ändert, versuchen Sie Folgendes, um sicherzustellen, dass die Datei über ausreichend freien Speicherplatz verfügt:

Führen Sie die folgende Abfrage aus.

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Führen Sie den Befehl DBCC SQLPERF aus, um den vom Transaktionsprotokoll verwendeten Speicherplatz zurückzugeben.

Falls nicht ausreichend Speicherplatz verfügbar ist, kann die Dateigröße durch den Verkleinerungsvorgang nicht weiter reduziert werden.

In der Regel ist es die Protokolldatei, die scheinbar nicht verkleinert wurde. Ist dies der Fall, liegt es gewöhnlich daran, dass die Protokolldatei nicht abgeschnitten wurde. Um das Protokoll abzuschneiden, können Sie das Datenbankwiederherstellungsmodell auf SIMPLE setzen, oder das Protokoll sichern und dann den DBCC SHRINKFILE-Vorgang erneut ausführen.

Der Verkleinerungsvorgang ist blockiert

Eine Transaktion, die unter einer auf Zeilenversionsverwaltung basierenden Isolationsstufe ausgeführt wird, kann Verkleinerungsvorgänge blockieren. Erfolgt z. B. während eines DBCC SHRINKDATABASE-Vorgangs gleichzeitig ein umfangreicher Löschvorgang, der auf einer auf Zeilenversionsverwaltung basierenden Isolationsstufe ausgeführt wird, wird auf den Abschluss des Löschvorgangs gewartet, bevor die Dateien weiter verkleinert werden. Wenn diese Blockierung auftritt, wird von den DBCC SHRINKFILE- und DBCC SHRINKDATABASE-Vorgängen eine Informationsmeldung (5202 für SHRINKDATABASE und 5203 für SHRINKFILE) an das SQL Server-Fehlerprotokoll ausgegeben. Diese Meldung wird in der ersten Stunde alle fünf Minuten und dann jede Stunde protokolliert. Wenn das Fehlerprotokoll beispielsweise folgende Fehlermeldung enthält, tritt folgender Fehler auf:

DBCC SHRINKFILE for file ID 1 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.

Diese Meldung bedeutet, dass Momentaufnahmentransaktionen mit Zeitstempeln, die älter als 109 sind (die letzte Transaktion, die der Verkleinerungsvorgang abgeschlossen hat), den Verkleinerungsvorgang blockieren. Außerdem zeigt es an, dass die transaction_sequence_num-Spalte oder die first_snapshot_sequence_num-Spalte in der dynamischen Verwaltungssicht sys.dm_tran_active_snapshot_database_transactions einen Wert von 15 enthält. Wenn entweder die Ansichtsspalte transaction_sequence_num oder first_snapshot_sequence_num eine Zahl enthält, die kleiner ist als die zuletzt abgeschlossene Transaktion (109) eines Verkleinerungsvorgangs ist, wartet der Verkleinerungsvorgang darauf, dass diese Transaktionen abgeschlossen sind.

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. Wenn der Verkleinerungsvorgang beendet wird, bleibt der bereits abgeschlossene Teil 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 Datendatei auf eine angegebene Zielgröße

Im folgenden Beispiel wird die Größe der Datendatei DataFile1 in der UserDB-Benutzerdatenbank auf 7 MB verkleinert.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Verkleinern einer Protokolldatei auf eine angegebene Zielgröße

Im folgenden Beispiel wird die Protokolldatei in der AdventureWorks2022-Datenbank auf 1 MB verkleinert. Damit die Datei mit dem DBCC SHRINKFILE-Befehl verkleinert werden kann, wird die Datei zunächst abgeschnitten, indem das Wiederherstellungsmodell für die Datenbank auf SIMPLE festgelegt wird.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Abschneiden einer Datendatei

Im folgenden Beispiel wird die primäre Datendatei in der AdventureWorks2022-Datenbank abgeschnitten. Die sys.database_files-Katalogsicht wird abgefragt, um den file_id-Wert für die Datendatei zu erhalten.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D: Leeren einer Datei

Das folgende Beispiel veranschaulicht das Leeren einer Datei, sodass sie aus der Datenbank entfernt werden kann. Für dieses Beispiel wird zunächst eine Datei mit Daten erstellt.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Verkleinern einer Datenbankdatei mit WAIT_AT_LOW_PRIORITY

Im folgenden Beispiel wird versucht, eine Datendatei in der aktuellen Benutzerdatenbank auf 1 MB zu verkleinern. Die Katalogsicht sys.database_files wird abgefragt, um die Datei-ID (file_id) der Datendatei zu erhalten (in diesem Beispiel: file_id 5). Sollte innerhalb einer Minute keine Sperre abgerufen werden können, wird der Verkleinerungsvorgang abgebrochen.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Freigeben über