Error shrinking database

  • While trying to shrink a database using the command "dbcc shrinkdatabase ([db_name])", I get the following message

    "DBCC SHRINKDATABASE: File ID 1 of database ID 13 was skipped because the file does not have enough free space to reclaim." That seems to be case with few other DBs too. I was web searching and came across a link which suggested to execute "DBCC SHRINKDATABASE ([db_name], TABULAR)" and I get the following table:

    Dbid   Field     CurrentSize   MinimumSize    UsedPages   EstimatedPages

    13        1             12800             12800                  448               448

    13        2            6400               6400                    6400             6400

    There is space for the db files to expand. I was looking into options of shrinkdatabase command to see if it could skip a shrink if there is no space to be shrunk.  Do you know of any such options? I am a bit new to these shrinkdatabase options.

    PS. We have to do the shrinks on these databases because a bunch of tables get created and then dropped as part of scheduled jobs.

    Thanks a lot,  Ram

     

    • This topic was modified 2 months ago by  Ram.
    • This topic was modified 2 months ago by  Ram.
  • You should still never shrink a database (at the db level), only specific db files (at the file/file_id level).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I agree with Scott and I have a question for you Ram too - how often do these jobs run?

    This is relevant because if you run the jobs nightly (for example) to create new tables, populate them, then move the data out of those tables, drop the tables, and finally shrink the database, it is just going to grow again the next day.  I would MUCH rather keep the disk space consumed by the instance so I know I have enough free space for the next run.

    Now, if these jobs are run once per year, and the database is a read-only database, then I may shrink things, but like Scott, I only shrink the db files and not the whole database. I very rarely shrink a live system because it grew to that size at one point for a good reason, so it is likely to just grow all over again.

    On my dev/test instances, I shrink files once I do the restore so I am not wasting disk.  Dev/Test has a LOT fewer inserts so the autogrow can be set lower and I waste a lot less disk with that.  But, like Scott said, I am shrinking the file not the database.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Thanks a lot for your time Scott and Brian.  Your point that of not shrinking the db/files unnecessarily is noted.

    But I am unable to understand how this error has occurred in our system when the same commands were working fine till last week. The processes have been running there for over a year.

  • My understanding of the error - the particular database file has no room to shrink.  If there is 0 bytes free in that database file (or near 0), then it will not be able to shrink and that is what the error is telling you.

    If you look at the results in the table you provided it says there is no free space.  Here is what you posted:

    Dbid   Field     CurrentSize   MinimumSize    UsedPages   EstimatedPages

    13        1             12800             12800                  448               448

    13        2            6400               6400                    6400             6400

     

    Current Size is EQUAL to the Minimum Size, therefore there is nothing you can shrink.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • If you have very volatile tables like that, you should put them in a separate filegroup and then shrink only the files associated with that filegroup.

    You especially do not want to shrink the log file unnecessarily!

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Using SHRINKDATABASE attempts to shrink all of the files associated with the database - to their initial size.  This is very important to consider for transaction log files.  If the initial size of the file is 1MB - the shrink will cause the log file to shrink to that size if it can.  Upon completion that file will immediately hit an auto growth as soon as there is any activity in that database.

    Based on the results you posted - you either have a database in full recovery that has not run a log backup, or there is an open transaction preventing the transaction log from being truncated.

    It sounds like you have a process in place to automatically shrink the database on a schedule.  That is an incredibly bad practice - as it fragments the indexes and forces the transaction log to regrow to support new activity.  You really should stop that practice, grow the files out to their expected size and leave it alone.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • The error message is certainly misleading, but when you look at the TABULAR result, the reason for the error is clear. There simply isn't any free space to reclaim. You would expect SQL to simply let you know it's not going to shrink the database BUT....

    The reality is the SHRINKDATABASE actually needs the free space otherwise it can't do a shrink. If there is no free space SQL would first need to EXPAND the database to provide an area to start moving pages/extents, just to release that space again.

    What the error message is essentially saying is "There's no free space for me to use for the shrink process"

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply