CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

  • Comments 3

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

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
Page 1 of 1 (3 items)