DBCC SHRINKDATABASE in Microsoft SQL Server

DBCC SHRINKDATABASE

Shrinks the size of the data files in the specified database.

Syntax:

DBCC SHRINKDATABASE      ( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]    )

Arguments
DATABASE_NAME:
Is the name of the database to be shrink.
TARGET_PERCENT:
Is the desired percentage of free space 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.

Remarks
Execute DBCC SHRINKDATABASE for shrink all the data or log file at a time for a specific database. Execute DBCC SHRINKFILE is used for shrinking one file at a time.

Assume a database named mydb with two data files and two log files. Both data and log files are 10 MB in size. The first data file contains 6 MB of data.
For each file, SQL Server calculates a target size, which is the size to which the file is to be shrunk. When DBCC SHRINKDATABASE is specified with target_percent, SQL Server calculates target size to be the target_percent amount of space free in the file after shrinking. For example, if you specify a target_percent of 25 for shrinking mydb. SQL Server calculates the target size for this file to be 8 MB (6 MB of data plus 2 MB of free space). Therefore, SQL Server moves any data from the last 2 MB of the data file to any free space in the first 8 MB of the data file and then shrinks the file.

Examples
This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 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.