Shrink the database in MS SQL Server

Shrink the database in SQL Server

1. Check the file size and status:

DBCC ShowFileStats

Note: Show the files status of the Total Extents and used Extent.

2. Change the database full recovery mode to simple mode:

Alter database SET Recovery simple;

3. Shrink the files by providing the file path in following command otherwise use 4 step.

DBCC SHRINKFILE('', 1) -- use logical file name

4. For Shrink the completer databaae , it cover all files of the database:

dbcc shrinkdatabase('ADVENTUREWORKS')

5. Change recovery mode of the database to full recovery mode:

Alter database SET Recovery full;

6. Check the size again for verify:

DBCC ShowFileStats

Syntax of Shrink the database 

( 'database_name' | database_id | 0 
[ ,target_percent ] 

‘database_name’ | database_id | 0 
Is the name or ID of the database to be shrunk. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.
Is the percentage of free space that you want left in the database file after the database has been shrunk.
Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.
Suppresses all informational messages that have severity levels from 0 through 10.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s