Share via

  • Article

How to: Shrink a Database (SQL Server Management Studio)

This topic describes how to shrink a database by using Object Explorer in SQL Server Management Studio. The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

To shrink a database

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

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

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

  4. Optionally, select the Reorganize files before releasing unused space check box. If selected, a value must be specified for Maximum free space 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.

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

  6. Click OK.