Shrinking a Database

Each file within a database can be reduced to remove unused pages. 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 thetarget_size of a DBCC SHRINKFILE statement, only 1 GB will be freed.

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.

You can manually shrink a database or files within a database using the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement. 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.

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.