Shrinking a Database

Creating and Maintaining Databases

Creating and Maintaining Databases

Shrinking a Database

Microsoft® SQL Server™ 2000 allows each file within a database to be shrunk to remove unused pages. Both data and transaction log files can be shrunk. The database files can be shrunk manually, either as a group or individually. The database can be set to shrink automatically at given intervals. This activity occurs in the background and does not affect any user activity within the database.

When the database is set to shrink automatically using the ALTER DATABASE AUTO_SHRINK option (or the sp_dboption system stored procedure), shrinking occurs when a significant amount of free space is available in the database. However, if the percentage of free space to be removed cannot be configured, as much free space as possible is removed. To configure the amount of free space to be removed, such as only 50 percent of the current free space in the database, use the Properties dialog box in SQL Server Enterprise Manager to shrink the database.

You cannot shrink an entire database to be smaller than its original size. Therefore, if a database was created with a size of 10 megabytes (MB) and grew to 100 MB, the smallest the database could be shrunk to, assuming all the data in the database has been deleted, is 10 MB.

However, you can shrink the individual database files smaller than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, rather than attempting to shrink the entire database.

There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log determines the possible reduction in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. In addition, the log file is shrunk in increments equal to the size of the virtual log file. For example, a transaction log file of 1 gigabyte (GB) may comprise five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least one virtual log file. Because each virtual log file in this example is 200 MB, the transaction log can shrink only to a minimum of 200 MB and can shrink only in increments of 200 MB. To allow a transaction log file to shrink to a smaller size, create a smaller transaction log and allow it to grow automatically, rather than creating a large transaction log file.

In SQL Server 2000, a DBCC SHRINKDATABASE or DBCC SHRINKFILE operation attempts to shrink a transaction log file to the requested size (subject to rounding) immediately. You should truncate the log file prior to shrinking the file to reduce the size of the logical log and mark as inactive virtual logs that do not hold any part of the logical log. For more information, see Shrinking the Transaction Log.

Note  It is not possible to shrink the database or transaction log while the database or transaction log is being backed up. Conversely, it is not possible to create a database or transaction log backup while the database or transaction log is being shrunk.

To shrink a database

Transact-SQL