DBCC database shrinking

Original http://www.cnblogs.com/sunshineflower/archive/2013/04/17/3026233.html

DBCC is the Database Console Command of SQL Server. It provides multiple commands for database maintenance, verification, and information retrieval.

Usage 1: Use the DBCC shrinkdatabase statement to contract the database
You can use the DBCC shrinkdatabase statement to shrink the size of data files and log files in the specified database. The basic syntax structure is as follows:

Basic Syntax:
DBCC shrinkdatabase (Database Name | Database ID | 0, target percentage, notruncate | truncateonly)

After DBCC shrinkdatabase, you must specify the name or ID of the database to be shrunk. If 0 is used, the current database is shrunk.

The "target percentage" parameter is optional and only valid for shrinking data files. After this parameter is used, the allocated page at the end of the file is moved before the file

Configuration page. The available space at the end of the file will not be returned to the operating system, and the physical size of the file will not change.
Optional. It is only valid for shrinking data files. With this parameter, all available space at the end of the file will be released

Operating system, but does not execute the page movement operation inside the file. Therefore, the data file with this parameter can only contract the recently allocated zone.

For example, to shrink the database db_test, the available space is 10%,CodeAs follows:

 
DBCCShrinkdatabase (db_test,10)
 

 

Note that the database space is not as small as possible. Because most databases need to reserve some space for daily operations. Because

In this case, if the size of the database file remains the same or the size of the database file increases, it means that the space to be reduced is required by conventional operations. This is

You do not need to shrink the database.

Usage 2: Use the DBCC shrinkdatabase statement to contract the specified database file

Basic Syntax:
DBCC shrinkfile (file name | File ID | 0, emptyfile, the size of the compressed file, notruncate | truncateonly)

Parameter file name, required. It refers to the name of the database file to be shrunk.Logical name.
(Optional) parameter emptyfile. The database engine migrates all data in the current file to other files in the same file group.

Alter database statement to delete the file.
The parameter "Size of the compressed file" is represented by an integer in MB. If this parameter is not specified, the file size is reduced to the default file size.
Optional. It is only valid for shrinking data files. With this parameter, all available space at the end of the file will be released

Operating system, but does not execute the page movement operation inside the file. Therefore, the data file with this parameter can only contract the recently allocated zone.

For example, to contract the db_test1 file in db_test to 20 mb, the Code is as follows:

 
DBCCShrinkfile (db_test1,20);
 

 

For example, use the emptyfile keyword to clear database files.
Clear the db_test1 file in database db_test, and then use the alter database statement to delete the file. The Code is as follows:

 
DBCCShrinkfile (db_test1, emptyfile)GoAlter DatabaseDb_test1 removeFileDbtest1
 

 

For example, if the log file is large, you can use DBCC shrinkfile to compress the log file. The Code is as follows:

 
DBCCShrinkfile (db_test_log,1);