After the database has been in use for a period of time, there are too many free space in the database due to data deletion, then the disk space allocated to the database files and transaction log files needs to be reduced so as not to waste disk space. When there is no data in the database, you can modify the database file properties to directly change their footprint, but when there is data in the database, this will destroy the data in the database, so you need to use compression to reduce the database space. You can select the "Auto shrink" option in the Database property options to allow the system to automatically compress the database, or you can compress it in a manual way. The manual compression database has the following two ways:
1, using Enterprise Manager to compress the database
in Enterprise Manager, right-click on the database you want to compress, and select the Shrink database option from all Tasks in the shortcut menu, the dialog box shown in Figure 6-10 will appear. You can choose how you want to compress the database in the dialog box shown in Figure 6-10, or you can choose to use a compression plan or compress a single file.
clicking the "Files" button in Figure 6-10 shows the compressed Database file dialog box shown in Figure 6-11, which allows different compression settings for each database file.
Click the Change button in Figure 6-10, the Compression Plan editing dialog box, shown in Figure 6-12, allows you to specify how the compression plan should be executed. When you click the Change button in Figure 6-12, a circular Work Plan editing dialog box appears as shown in Figure 6-13, which allows you to edit the cycle or point of time for scheduled execution. When the settings are complete, click the OK button to begin compressing the database, and a compression information box will be displayed when the compression is finished.
2, COMPRESSED database with Transact-SQL command
can use the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands to compress the database. Where the DBCC SHRINKDATABASE command compresses the database, the DBCC SHRINKFILE command compresses the files specified in the database.
(1) DBCC shrinkdatabase
The
DBCC shrinkdatabase command syntax is as follows:
DBCC shrinkdatabase (database_name [, target_percent]
[, {notruncate | TRUNCATEONLY}])
The
parameters are described as follows:
- target_percent specifies that unused space is a percentage of the database size after compressing the database. If the specified percentage is too large to exceed the proportion of unused space before compression, the database is not compressed. and the compressed database cannot be smaller than the initial size of the database.
- Notruecate
The remaining space is kept in the database after the database is reduced and not returned to the operating system. If this option is not selected, the remaining space is returned to the operating system.
- Truncateonly
Return the remaining space to the operating system after the database has been reduced. When you use this command, SQL Server shrinks the file to the last file assignment, but does not move any data files. When this item is selected, the target_percent option is invalid.
Example 6-14: The unused space of the compressed database mytest is 20% of the database size.
DBCC SHRINKDATABASE (mytest, 20)
The results of the operation are as follows:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(2) DBCC Shrinkfile
The
DBCC shrinkfile command compresses files in the current database. The syntax is as follows:
DBCC shrinkfile ({file_name | file_id}
{[, target_size] |
[, {emptyfile | Notruncate | TRUNCATEONLY}]})
The
parameters are described as follows:
- file_id
Specifies the identification number of the file to compress (identification, or ID). The ID number of the file can be obtained by the file_id () function or the sp_helpdb system stored procedure described earlier in this chapter.
- Target_size
Specifies the size of the file after compression. In megabytes. If you do not specify this option, SQL Server shrinks files to the maximum extent possible.
- Emptyfile
Indicates that the file is no longer in use and will move all data in this file to other files in the same filegroup. After you execute the command with this parameter, the file can be deleted with the Alter DATABASE command.
The remaining parameters notruncate and truncateonly the same meaning as in the DBCC SHRINKDATABASE command.
Example 6-15: Compress the database files in the database mydb to 1MB mydb_data2 size. Use MyDB DBCC SHRINKFILE (MYDB_DATA2, 1)
