Thursday 9 September 2010

How to shrink a database using DBCC

Syntax for shrinking a database using DBCC on SQL server is as follows:

Syntax

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

Arguments

database_name

Is the name of the database to be shrunk. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

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.

Example


This will shrink the database 'DemoData' leaving 10% free space.

No comments:

Post a Comment