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 4

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 1 and 1 and type the answer here:
  • Post
  • My peers are starting to tease me about becoming a dbcc shrink* expert. (Ha, Ha I said.)  Then,

  • http://sqlbits.wordpress.com/2009/08/24/dbcc-shrinkdb-understanding-math-behind-it/

  • Thanks for your post, it very helpful for me.

  • 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 !!!!

Page 1 of 1 (4 items)