Shrinking a Database
In SQL Server 2005, each file within a database can be reduced to remove unused pages. Although the Database Engine will reuse space effectively, there are times when a file no longer needs to be as large as it once was; shrinking the file may then become necessary. Both data and transaction log files can be reduced, or shrunk. The database files can be shrunk manually, either as a group or individually, or the database can be set to shrink automatically at specified intervals.
Files are always shrunk from the end. For example, if you have a 5-GB file and specify 4 GB as the target_size in a DBCC SHRINKFILE statement, the Database Engine will free as much space as it can from the last 1 GB of the file. If there are used pages in the part of the file being released, the Database Engine first relocates the pages to the part of the file being retained. You can only shrink a database to the point where it has no free space remaining. For example, if a 5-GB database has 4 GB of data and you specify 3 GB as the target_size of a DBCC SHRINKFILE statement, only 1 GB will be freed.
Automatic Database Shrinking
When the AUTO_SHRINK database option has been set to ON, the Database Engine automatically shrinks databases that have free space. This option is set using the ALTER DATABASE statement. By default, it is set to OFF. The Database Engine periodically examines the space usage in each database. If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.
To set a database to shrink automatically
Manual Database Shrinking
You can manually shrink a database or files within a database using the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement. If a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement cannot reclaim all the specified space in a log file, the statement will issue an informational message that indicates what action you must perform to make more space eligible to be freed. For more information about shrinking log files, see Shrinking the Transaction Log.
DBCC SHRINKDATABASE and DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.
When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.
Note
You cannot shrink the database or transaction log while the database or transaction log is being backed up. Conversely, you cannot create a database or transaction log backup while you are trying to shrink the database or transaction log.
To shrink a database
To shrink a data or log file
Shrinking the Transaction Log
There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log files within the log determines the possible reductions in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. Also, the log file is reduced in increments equal to the size of the virtual log file size. For example, a transaction log file of 1 GB can consist of five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least two virtual log files. Because each virtual log file in this example is 200 MB, the transaction log can be reduced only to a minimum of 400 MB and only in increments of 200 MB. To be able to reduce a transaction log file to a smaller size, create a small transaction log and let it grow automatically, instead of creating a large transaction log file all at once.
In SQL Server 2005, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to reduce a transaction log file to the requested size (subject to rounding). You should backup the log file before shrinking the file to reduce the size of the logical log and mark as inactive the virtual logs that do not hold any part of the logical log. For more information, see Shrinking the Transaction Log.
Best Practices
Consider the following information when you plan to shrink a database or file:
- A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
- Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
- A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
- Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
Row Versioning-based Isolation Levels and Shrink Operations
It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For more information, see DBCC SHRINKDATABASE (Transact-SQL).
See Also
Concepts
Adding and Deleting Data and Transaction Log Files
Introduction to Transaction Logs
Physical Database Files and Filegroups
Understanding Files and Filegroups
Other Resources
Understanding and Managing Transaction Logs
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|