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!