DBA 101 : DBCC SHRINKFILE

WARNING : ONLY SHRINK NON LIVE DATABASES E.G TEST ENVIRONMENTS AND ONLY WHEN ABSOLUTELY NECESSARY E.G RESTORES FROM LIVE WHERE DATA SPACE IS PRE-ALLOCATED! 

The Shrinkfile screen shown above is familiar, but what do the options do???

1) Release unused space

This uses the TRUNCATEONLY option and releases unused space at the end of the file.
No reorganization of pages occurs, i.e if empty pages exist in the middle of the file , the space will not be reclaimed.

The size '0' is scripted by Management Studio but is ignored

-- Release unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , 0, TRUNCATEONLY)
GO

2) Reorganize pages before releasing unused space

Pages are reorganized to free up all available free space. This option can take some time
Update : See this tip for shrinking a datafile in stages

-- Reorganize pages before releasing unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , 3600)
GO

3) Empty file by migrating the data to other files in the same filegroup

If other files are assigned to a filegroup , pages are distributed between those files.

-- Empty file by migrating the data to other files in the same filegroup
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , EMPTYFILE)
GO

4) Not available from the interface - NOTRUNCATE

NOTRUNCATE is only available by TSQL.
Use it to reorganize the pages to the start of the file, but leave the datafile the same size.

-- DOES NOT Release unused space
USE [DBName]
GO
DBCC SHRINKFILE (N'JF_2006Data' , NOTRUNCATE)
GO