Jaa

How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

  • Artikkeli

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

Comments

  • Anonymous
    June 18, 2008
    My peers are starting to tease me about becoming a dbcc shrink* expert. (Ha, Ha I said.)  Then,
  • Anonymous
    August 24, 2009
    http://sqlbits.wordpress.com/2009/08/24/dbcc-shrinkdb-understanding-math-behind-it/
  • Anonymous
    April 29, 2012
    Thanks for your post, it very helpful for me.
  • Anonymous
    July 11, 2015
    dbcc file header ('logical name') would give you the initial size of the file when it was first created . Not the initial size that the database grew to because of transaction logs and Data Inputs on the database . Also the three stages that the shrink goes through is applicable since SQL 2005 , Including DBCC spacereclaim , dbcc file compact ,dbcc lob compact which was not valid in SQL 2000 if you are unable to shrink we can take the shrink back to 2000 . using traceflag 2548 its undocumented . Try it at your own risks !!!!