SHRINK DATABASE options 3

[bperry]

Last week, a suggestion in one of the posts referred to the DBCC SHRINKDATABASE and SHRINKFILE commands. (I believe it was thread183-269676) That got me interested in finding out more about those commands. I learned that there are a couple of little-known options that can be used, and I have a little diagram to illustrate.

The three options involved are <no option>, NOTRUNCATE, and TRUNCATEONLY. Imagine that before shrinking, your SQL data file looks like this below, where xxx represents the location of data and objects within the file:

[tt]Before shrinking:
-------------------------------
| xxx xxx xxx |
-------------------------------
[/tt]

Here is the effect of the three possibilities:

[tt]Shrink, no option:
------------
|xxxxxxxxx |
------------
[/tt]

[tt]Shrink, NOTRUNCATE:
-------------------------------
|xxxxxxxxx |
-------------------------------
[/tt]

[tt]Shrink, TRUNCATEONLY:
--------------------------
| xxx xxx xxx|
--------------------------
[/tt]


The DBCC SHRINKFILE command actually has another additional option:

[tt]Shrink, EMPTYFILE:
---------------------------------
| |
---------------------------------
[/tt]

This one is used when you want to move all the objects out to another file, in preparation for deleting this file from the database. Of course, this assumes your database has other data files where these objects can be moved to.

---------------
that's it. Not too exciting.
I hope those little graphics look okay when I post.