SQL Server Performance

DBCC SHRINKDATABASE bug?

Discussion in 'SQL Server 2005 General DBA Questions' started by merrillaldrich, Aug 24, 2007.

  1. merrillaldrich New Member

    Hi all -
    I notice this morning that if I use DBCC SHRINKDATABASE on a database having multiple data files, only the first data file shrinks while the others issue this message:
    DBCC SHRINKDATABASE: File ID 3 of database ID 20 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 4 of database ID 20 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 5 of database ID 20 was skipped because the file does not have enough free space to reclaim.
    The interesting thing is that the files definitely DO have free space to reclaim, and if I manually run DBCC SHRINKFILE against each, they all do shrink. It's just a pain to locate the names of all the files and manually script separate shrink statements (I am restoring lots of databases and shrinking the restored copies).
    Is this a known bug?
  2. satya Moderator

    I don;t think so and I presume you are on upto date Service packs in this case, as I don't see that problem at my end.
    For each file, the Microsoft SQL Server 2005 Database Engine calculates a target size. This is the size to which the file is to be shrunk. When DBCC SHRINKDATABASE is specified with target_percent, the Database Engine calculates target size to be the target_percent amount of space free in the file after shrinking. For example, if you specify a target_percent of 25 for shrinking mydb, the Database Engine calculates the target size for the data file to be 8 MB (6 MB of data plus 2 MB of free space). Therefore, the Database Engine moves any data from the last 2 MB of the data file to any free space in the first 8 MB of the data file and then shrinks the file.
  3. thomas New Member

    I don't know if it's a bug, but for what it's worth, I never use shrinkdatabase. I'd rather know what's going on a file-by-file basis, and therefore I use shrinkfile exclusively and sparingly.

Share This Page