Quick one today:
A few weeks ago, I ran into an interesting error message:
DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim. DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After looking into it a bit further, these are the conclusions.
- SHRINKDATABASE runs on the entire database, unlike SHRINKFILE. So, when I issued the command, the command internally tried to SHRINK both data and log file. The shrink percentage parameter indicates the amount of “shrink” that it is attempting on the database (data & log file). If there is not enough free space inside the data or log file, then Sql Server throws this error out saying it could not proceed. If the expected free space is not available on both the files, the command errors out.
- SHRINKDATABASE cannot reduce the data and log file size beyond the initially configured number. This could also be a factor.
So, first we need to understand how much free space is available, so I could shrink based on that. For that use the following command:
-- -- How much free space is available -- DBCC SHRINKDATABASE (SampleDB, TABULAR) GO
This give output in the following format:
Once you know how much free space is available, then you could re-run the SHRINKDATABASE command with pertinent parameter values.
- Running SHRINKDATABASE command on production systems is not advisable. Careful analysis needs to happen before any steps are taken.
- Also, this (TABULAR) option is an undocumented feature, so it could change in future
Hope this helps,