Shrink SQL Server Database

The following example reduces the size of the data and log files in the UserDB user database to allow for 10 percent free space in the database.

 DBCC SHRINKDATABASE (UserDB, 10);

The following example shrinks the data and log files in the AdventureWorks2022 sample database to the last assigned extent.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

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

The following example shrinks the log file in the AdventureWorks2022 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

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

The following example truncates the primary data file in the AdventureWorks2022 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

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

The following example demonstrates emptying a file so it can be removed from the database. For this example's purposes, a data file is first created and contains data.

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

The following example attempts to shrink the size of a data file in the current user database to 1 MB. The sys.database_files catalog view is queried to obtain the file_id of the data file, in this example, file_id 5. If a lock can't be obtained within one minute, the shrink operation will abort.

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