INF: Issues With Shrinking SQL Server Databases

ID: Q141163

The information in this article applies to:

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 simply represented like this:


0      256    512    768    1024   1280   1536   1792   2048   2304
A......A......A......A......A......A......A......A......A......A......

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
A......A......A......A......A......A......A......A......A......A......
          ^             ^             ^      ^      ^
          T             T             T      T      T

If there are no system table entries beyond allocation unit 1792, we should be able to shrink this database by 1 MB to a total of 4 MB of allocated space. If there are system tables beyond that point, DBCC SHRINKDB should report this when run with just the database name as a parameter.

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
A......A......A......A......A......A......A......A......A......A......
          ^      ^             ^
          T      T             L

In this case, all the user data resides within the first 2 MB of database space. 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.

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
A......A......A......A......A......A......A......A......A......A......
                               ^                                   ^
                               L                                   T

In this case, even though there is only a single row of user data in the database, DBCC SHRINKDB cannot shink the database at all, even though most of the allocation units are completely empty of data. This is the worst case scenario.

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.

Additional query words: sql6 sizing dbcc shrinkdb

Keywords : kbusage SSrvAdmin SSrvBCP SSrvTran
Version : 6.0
Platform : WINDOWS
Issue type : kbhowto