To shrink a databaseMicrosoft® SQL Server™ 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 also 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 sp_dboption system stored procedure, shrinking occurs whenever a significant amount of free space is available in the database. However, 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 property page within SQL Server Enterprise Manager to shrink the database.
You cannot shrink an entire database to be smaller than its size when created. 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 creation 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 within which a transaction log file can be shrunk. This depends on the initial size of the transaction log and the number of virtual log files used. For example, a large initial 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. 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.
Shrinking a transaction log file does not shrink the file immediately but instead causes the file to be marked for later shrinking. Each time the transaction log is subsequently backed up or truncated (for example, when the trunc. log on chkpt. database option is set to true), SQL Server will attempt to shrink the transaction log file as much as possible until it reaches the desired size specified by the user. If the active portion of the transaction log is at the end of the transaction log file, the file cannot be shrunk. However, as soon as the active portion of the transaction log moved to the beginning of the file, the transaction log file can be shrunk.
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
To set a database to shrink automatically
To shrink a database file