How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

  • Article

I ran across an undocumented option that was very helpful in resolving an issue last week.  

WARNING: As always any undocumented syntax can change and may not be supported. This should be used under the guidance of Microsoft SQL Server Support.

The customer was running a dbcc shrinkdatabase and getting the following error for several files.   However, issuing a dbcc shrinkfile with a target value completes successfully.

DBCC SHRINKDATABASE: File ID 1 of database ID 8 was skipped because the file does not have enough free space to reclaim.

Sample: dbcc shrinkdatabase(pubs, 99)

  • The shrinkdatabase command is based on a percentage where as shrinkfile is based on a target.  
  • Shrinkdatabase uses the percentage to calculate a target for each file but can't override the minimum file size.
  • ShrinkFile allows shrinking below the minimum file size.

The sample shrinkdatabase command results in a loop over each of the files in the database.  Using the current free space of the file and the percentage the shrink target is calculated for each file.   If the calculated target is less than the minimum size of the file the error is produced and the shrink attempt aborted for that file.

When a successful shrink completes the following output is reported.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
8 1 280 280 208 208
8 2 70 70 64 64

This output can be obtained with the undocumented 'Tabular' option without invoking the shrink actions. (report only)

Sample: dbcc shrinkdatabase(pubs. tabular)

Using the information you can determine the expected behavior of dbcc shrinkdatabase and your targets.  

MimimumSize The current minimum size established for the file.  See ALTER DATABASE for details.
UsedPages The number of pages used in the file (allocated)
EstimatedPages The estimated size that might be obtained during a shrink

In the customers case the minimum and maximum values where established the same when the file was completed.  Shrinkdatabase won't shrink the file but shrinkfile could be used to reduce the minimum value and complete the shrink.

Bob Dorr
SQL Server Senior Escalation Engineer