LKBEN10677: Howto shrink an SQL database on Microsoft SQL Server 2005

Symptom

You noticed that a certain database has a lot of unused space and want to release this space

Cause

This is the normal behaviour. The database used the space and a lot of records have been deleted

Solution

Connect to the instance of the SQL Server 2005 Database Engine, and then expand that instance.

Expand Databases, and then right-click the database that you want to shrink.

Point to Tasks, point to Shrink, and then click Database.

Optionally you can choose to reorganize files before releasing unused space check box. If selected, a value must be

specified for Maximum free space (which I recommend) in files after shrinking.

Selecting this option is the same as specifying a target_percent value when executing DBCC SHRINKDATABASE. Clearing this

option is the same as executing DBCC SHRINKDATABASE using the TRUNCATEONLY option. TRUNCATEONLY shrinks the file to the

last allocated extent. This reduces the file size without moving any data. By default, the option is cleared.

The shrinking on older versions of sql server still use the DBCC SHRINKDATABASE tool.

Enter the maximum percentage of free space to be left in the database files after the database has been shrunk.

Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space

is selected.

Click OK.

Disclaimer:

About the Author

Author: Wim Peeters - Keskon GmbH & Co. KG

Wim Peeters is electronics engineer with an additional master in IT and over 30 years of experience, including time spent in support, development, consulting, training and database administration. Wim has worked with SQL Server since version 6.5. He has developed in C/C++, Java and C# on Windows and Linux. He writes knowledge base articles to solve IT problems and publishes them on the Lubby Knowledge Platform.