LKBEN10677: Howto shrink an SQL database on Microsoft SQL Server 2005
LKB | Created: 02/04/2020 | Version: 0 | Language: EN | Rating: 0 | Outdated: False | Marked for deletion: False
Author: Wim Peeters - Keskon GmbH & Co. KG
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.
About the Author
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 in different European countries and different European languages. He writes knowledge base articles to solve IT problems and publishes them on the Lubby Knowledge Platform where he is one of the most important contributors and the main developer.