How to Shrink Database File ?

SQL Script to shrink a unused space from a database file.

-- ***********************************************************************************/
-- Description  :    SQL Script to shrink a non used space from a database
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use <database name>
go
-- Knowing information about database file
sp_helpfile
go
--- Shrinking a particular database file
dbcc shrinkfile (name= <datafile name>, <target percent>)
go

Where

• file_name, Is the logical name of the file to be shrunk.
• target_size, Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Example, to shrink the database file
First, I need to get the get the information about the current database file, specially the logical file name, which should be shrunk by executing sp_helpfile stored procedure.
In my example, I executed sp_helpfile system stored procedure in pubs database to get current database files information (logical file name and size is important). In my case file index_alter, is of 204800 KB (200 MB), which I wanted to shrunk, as show in below screen shot.

Shrinking index_alter file size to 20 MB, using the following script.
-- ***********************************************************************************/ -- Description : SQL Script to shrink a non used space from a database -- Compatibility : 7.0+ -- This Script should be executed in master -- ***********************************************************************************/ use pubs go sp_helpfile go DBCC SHRINKFILE ( 'index_alter',20) go OUTPUT

Once the File is shrunk and you recheck the file status by running sp_helpfile, you will see the new size, which 20480 in this case, as shown below.