Share via

  • Article

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

This topic describes how to shrink a data or log file by using Object Explorer in SQL Server Management Studio. The primary data file cannot be made smaller than the size of the primary file in the model database.

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

Warning

Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

To shrink a data or log file

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

  4. Select the file type and file name.

  5. Optionally, select the Release unused space check box.

    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 the Reorganize files before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.

    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, enter the maximum percentage of free space to be left in the database file 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 enabled.

  8. Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.

    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.

  9. Click OK.