February 27, 2009 at 7:03 am
What is the DBCC Command to perform the following Database Task?
Right Click Database, select All Tasks, select Shrink Database, the Shrink Database Dialog appears. The following are selected/checked:
Maximum free space in files after shrinking 25%
"Move pages to beginning of file before shrinking".
Would the DBCC Command be the following?
DBCC ShrinkDatabase (bb_bb60, 25%)
What option or command is used to "Move pages to the beginning of file before shrinking"?
We have SQL Server 2000 running on Windows 2003.
Thanks, Kevin
February 27, 2009 at 7:17 am
the command you have written would be correct except the % sign is not required.
move pages to the front is the default action, you have to tell it NOT to by using the truncateonly option.
HOWEVER, this action would fragment your database and also shrink your log. Unless you are severely short on space or have just done a large data archive this is an unnecessary process. The database and log will likely only have to grow again.
---------------------------------------------------------------------
February 27, 2009 at 8:50 am
Are you saying that the "move pages to the front" option would fragment your database and also shrink your log? Or the "truncateonly" option would fragment your database and also shrink your log?
Thanks, Kevin
February 27, 2009 at 9:02 am
kevinsql7 (2/27/2009)
Are you saying that the "move pages to the front" option would fragment your database and also shrink your log? Or the "truncateonly" option would fragment your database and also shrink your log?Thanks, Kevin
the move pages to the front option would fragment your databases. Shrink does not care which pages get moved in which order, it just shuffles everything up to the front, hence fragmentation.
Using shrink database means ALL files in the database are shrunk, this includes the log. USe the shrink files option
do a search on shrinking in BOL
---------------------------------------------------------------------
February 27, 2009 at 9:13 am
Be careful even with the truncate_only. Databases tend to grow, it's in their nature, and shrinking one will just result in it growing again (unless it's a static database). Lots of shrink/grow cycles cause external (file-level) fragmentation as well as wasting resources. You can bet that the grow will occur at the worst possible time (when the server's very busy)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply