Recently when shrinking a SQL Server data file, I ran into the following error:
File ID 3 of database ID 15 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.Before I get any grief about how bad shrinking databases is, and a puppy being sacrificed every time shrink database is run, yes there was a legitimate reason to shrink the data file. The client was low on disk space, and a huge amount of data had been purged from the database file, that would not likely be growing to use all that free space for some time.
So I ran DBCC ShrinkFile and received the error message about:
cannot be shrunk as it is either being shrunk by another process or is empty.So I did some checking and confirmed that there was no other process doing a shrink, and I confirmed that the file was not empty. Therefore SQL Server has been caught in a lie. After doing some research I found many recommendations which included the following:
- Restarting the SQL Server instance. Would probably have worked, but that would have required scheduled down time.
- Running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Are you serious… no thank you.
- Turn off AUTOSHRINK. It was already off.
The suggestion that I finally found that seemed reasonable was that SQL Server thinks it is shrinking, but it was not. The fix is to just increase the size of the data file by a couple MB, then try shrinking. That’s it, all works well and I am back to shrinking databases.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!