Shrinking Data Files

In SQL Server 2005 and SQL Server 2008/R2, administrators can reclaim free space from the end of data files to remove unused pages and recover disk space.

However, shrinking data files is not recommended unless the content database has lost at least half of its content. This typically happens after some activities that create white space in the content database, such as moving a site collection from a content database to another one or deleting a massive amount of data. Shrinking SharePoint databases other than content databases is not recommended, because they do not generally experience as many necessary deletions to contain considerable free space.

Shrinking a Database by Using SQL Server 2008 R2 Management Studio

The following steps show how to shrink a database by using SQL Server 2008 R2 Management Studio:

1. Click Start, All Programs, Microsoft SQL Server 2008 R2, SQL Server Management Studio.
2. Connect to the desired SQL Server database engine instance and expand that instance.
3. Expand Databases, right-click the database to be shrunk, click Tasks, click Shrink, and click Files.
4. Select the file type and filename from the dialog box shown in Figure 9.6.

   Figure 9.6. Shrinking a database using SQL Server 2008.

   

5. Optionally, select Release Unused Space. Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
6. Optionally, select Reorganize Files Before Releasing Unused Space. If this option is selected, the Shrink File option must be set to value. Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.
7. Optionally, select Empty File by Migrating the Data to Other Files in the Same Filegroup. Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.
8. Click OK.