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 

DBCC SHRINKDATABASE 
( 'database_name' | database_id | 0 
[ ,target_percent ] 
[ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

Parameters:
‘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.
target_percent
Is the percentage of free space that you want left in the database file after the database has been shrunk.
NOTRUNCATE 
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.
TRUNCATEONLY 
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.
WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.
 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.