INF: Issues With Shrinking SQL Server Databases

• Microsoft SQL Server, version 6.0

SUMMARY

SQL Server 6.0 has added the new command DBCC SHRINKDB which allows administrators to truncate some or all of the unused portion of a database at the end of its allocated space thus "shrinking" the allocated database size. It is important to note that this command does no data compaction and does not move data within the database to shrink the database to its least possible size. It simply starts from the end of the database and goes backwards until it finds the first place where any data or log resides, and determines it can truncate everything after that point. The data object can be a user table or a system table. If the object is a system table (except for SYSLOGS which is the transaction log), there is little the user can do to move the object and other options besides DBCC SHRINKDB.

MORE INFORMATION

As described in the Administrator's Companion, a SQL Server database consists of 1/2 MB allocation units of 256 2KB pages each, and are identified by the first page number in their unit; i.e., the first allocation unit is 0, the second is 256, the third is 512, etc. DBCC SHRINKDB will only shrink a databases on a 1 MB boundary (i.e., it won't

leave a 1/2 MB.)   The allocation units for a database of 5 MB might be

0      256    512    768    1024   1280   1536   1792   2048   2304

In the simplest case there is no separate data and log fragments, so table data could reside anywhere within the database. Suppose we have only one user table "T" with 2 MB of data in it that takes up space on allocation units 256, 768, 1280, 1536, and 1792:

0      256    512    768    1024   1280   1536   1792   2048   2304

          ^             ^             ^      ^      ^
          T             T             T      T      T

Now suppose we have a 5 MB database with the first 2 MB allocated to data and and the last 3 MB allocated to the log. Again, assume a single user table "T" which takes up less than 1 MB of space on allocation units 256 and 512. However, because the log fragment (L) starts beyond the end of the data fragment, the database can never be shrunk smaller then 3 MB because there must always be at least some space allocated to the log fragment.

0      256    512    768    1024   1280   1536   1792   2048   2304

          ^      ^             ^
          T      T             L

Now suppose we have a 5 MB database which has been allocated as 2 MB data, 2 MB log, then another 1 MB of data. Suppose table "T" currenly only has 1 row in it, but it just so happens that through a combination of database activity over time that the one row resides on a page in allocation unit 2304.

0      256    512    768    1024   1280   1536   1792   2048   2304

                               ^                                   ^
                               L                                   T

For situations where DBCC SHIRNKDB does not effectively shrink the database enough, Transfer Manager can be used to copy all the data to a new, smaller database. Or the data can be bulk-copied out, the tables (or just the blocking table) dropped, the database shrunk, and the table(s) recreated and data bulk-copied back into the database.