SPS Home    >   Dgreath    >   RDBMS    >   Database Console Command (DBCC)

DATABASE CONSOLE COMMANDS (DBCC)

SQL Server provides a number of server-side commands to simply database administration. The following commands are launched from Query Analyzer as shown:
Database, Shrinkage of:
Oversize databases can be shrunk to a specified size using the 'shrinkdatabase' application. Specify the database name, the desired percentage of free space left after shrinking (optionally), and the truncation method (optionally).  Databases will not be shrunk below their actual size.


DBCC SHRINKDATABASE ( dbname, 20, NOTRUNCATE)

The database name is the one created by the CREATE DATABASE function.

NOTRUNCATE
Causes the freed file space to be retained in the database files.
 
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.
 
IF TRUNCATION IS NOT SPECIFIED
The freed file space is released to the operating system.
File, Shrinkage of:
Oversize files can be shrunk to a specified size using the 'shrinkfile' application. Specify the logical file name, the desired ending size (optionally), and truncation method (optionally).  Files will not be shrunk below their actual size.

DBCC SHRINKFILE ( dbfilename,  40, NOTRUNCATE )

The filename is the one created by the CREATE DATABASE function. Alternatively, the file ID can also be used.

target_size
Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size as much as possible.

If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.
 
NOTRUNCATE
Causes the freed file space to be retained in the files.

When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. 

TRUNCATEONLY
Causes any unused space in the 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_size is ignored when TRUNCATEONLY is used.
 
IF TRUNCATION IS NOT SPECIFIED
all freed file space is returned to the operating system.