How to release database unused space with MSSQL

  • A database is usually a single file .MDF. The database can be split between different files with the filegroup option.
  • Because the database is a single file [most of the times], the file contains all the data, the indexes, the stored procedures, it's own meta-data... It's a mish-mash. Each database object is allocated it's own page [usually 8k unit].
  • A page can be either data or an index or an object, but not a combination of these.
  • You can do a lot of cleanup by releasing the empty space in the database.
  • By releasing the unused space you will allow SQL Server to use these unused space for other objects, instead of adding them at the end of the file.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Releasing unused space with Visual Studio

  • Select the database
Menu access
Accessing the shrink option from the menu
  • Selecting the: release unused space
Release empty space
Releasing the empty space