How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

Published Jan 15 2019 10:52 AM 228 Views
Microsoft
First published on MSDN on Mar 24, 2008

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


%3CLINGO-SUB%20id%3D%22lingo-sub-315466%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20DBCC%20ShrinkDatabase%20%2F%20ShrinkFile%20(Tabular)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315466%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Mar%2024%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EI%20ran%20across%20an%20undocumented%20option%20that%20was%20very%20helpful%20in%20resolving%20an%20issue%20last%20week.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CSTRONG%3E%20WARNING%3A%20As%20always%20any%20undocumented%20syntax%20can%20change%20and%20may%20not%20be%20supported.%26nbsp%3B%20This%20should%20be%20used%20under%20the%20guidance%20of%20Microsoft%20SQL%20Server%20Support.%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EThe%20customer%20was%20running%20a%20%3CEM%3E%20dbcc%20shrinkdatabase%20%3C%2FEM%3E%20and%20getting%20the%20following%20error%20for%20several%20files.%26nbsp%3B%26nbsp%3B%20However%2C%20issuing%20a%20dbcc%20shrinkfile%20with%20a%20target%20value%20completes%20successfully.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%3CEM%3E%20DBCC%20SHRINKDATABASE%3A%20File%20ID%201%20of%20database%20ID%208%20was%20skipped%20because%20the%20file%20does%20not%20have%20enough%20free%20space%20to%20reclaim.%20%3C%2FEM%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%3CSTRONG%3ESample%3A%20%3C%2FSTRONG%3E%20dbcc%20shrinkdatabase(pubs%2C%2099)%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EThe%20shrinkdatabase%20command%20is%20based%20on%20a%20percentage%20where%20as%20shrinkfile%20is%20based%20on%20a%20target.%3C%2FLI%3E%0A%20%20%20%3CLI%3EShrinkdatabase%20uses%20the%20percentage%20to%20calculate%20a%20target%20for%20each%20file%20but%20can't%20override%20the%20minimum%20file%20size.%3C%2FLI%3E%0A%20%20%20%3CLI%3EShrinkFile%20allows%20shrinking%20below%20the%20minimum%20file%20size.%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%0A%20%20%3CP%3EThe%20sample%20shrinkdatabase%20command%20results%20in%20a%20loop%20over%20each%20of%20the%20files%20in%20the%20database.%26nbsp%3B%20Using%20the%20current%20free%20space%20of%20the%20file%20and%20the%20percentage%20the%20shrink%20target%20is%20calculated%20for%20each%20file.%26nbsp%3B%26nbsp%3B%20If%20the%20calculated%20target%20is%20less%20than%20the%20minimum%20size%20of%20the%20file%20the%20error%20is%20produced%20and%20the%20shrink%20attempt%20aborted%20for%20that%20file.%3C%2FP%3E%0A%20%20%3CP%3EWhen%20a%20successful%20shrink%20completes%20the%20following%20output%20is%20reported.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3EDbId%26nbsp%3B%26nbsp%3B%20FileId%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CurrentSize%20MinimumSize%20UsedPages%26nbsp%3B%26nbsp%3B%20EstimatedPages%20%3CBR%20%2F%3E%20------%20-----------%20-----------%20-----------%20-----------%20--------------%20%3CBR%20%2F%3E%208%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20280%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20280%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20208%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20208%20%3CBR%20%2F%3E%208%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2070%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2070%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2064%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2064%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EThis%20output%20can%20be%20obtained%20with%20the%20undocumented%20'Tabular'%20option%20without%20invoking%20the%20shrink%20actions.%20(report%20only)%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3ESample%3A%20dbcc%20shrinkdatabase(pubs.%20tabular)%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EUsing%20the%20information%20you%20can%20determine%20the%20expected%20behavior%20of%20dbcc%20shrinkdatabase%20and%20your%20targets.%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3EMimimumSize%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3EThe%20current%20minimum%20size%20established%20for%20the%20file.%26nbsp%3B%20See%20ALTER%20DATABASE%20for%20details.%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3EUsedPages%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3EThe%20number%20of%20pages%20used%20in%20the%20file%20(allocated)%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3EEstimatedPages%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3EThe%20estimated%20size%20that%20might%20be%20obtained%20during%20a%20shrink%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3EIn%20the%20customers%20case%20the%20minimum%20and%20maximum%20values%20where%20established%20the%20same%20when%20the%20file%20was%20completed.%26nbsp%3B%20Shrinkdatabase%20won't%20shrink%20the%20file%20but%20shrinkfile%20could%20be%20used%20to%20reduce%20the%20minimum%20value%20and%20complete%20the%20shrink.%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20%3CBR%20%2F%3E%20SQL%20Server%20Senior%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-315466%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Mar%2024%2C%202008%20I%20ran%20across%20an%20undocumented%20option%20that%20was%20very%20helpful%20in%20resolving%20an%20issue%20last%20week.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315466%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EStorage%20Engine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 10:53 AM
Updated by: