Database cannot be shrunk as it is either being shrunk by another process or is empty.

Hi, Today we faced below error on one of the servers while shrinking data file on user request.

Error :
/*************************************************************************************************/

File ID 1 of database ID 6 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
/*************************************************************************************************/

Initially, we thought auto shrink is True, but it was false.

According to Microsoft, the problem was "DBCC SHRINKFILE was unable to run on the primary data file of the user database "DatabaseName" because a background operation which had obtained a latch for the required shrink operation previously was not released."
There is no way to fix this issue except to restart the SQL Server service, since "such type of latches are in-memory structures. But there is a workaround for same

1.  DBCC DROPCLEANBUFFERS
   DBCC FREEPROCCACHE

But this can cause performance issue as it will free the cache

2. Increase the size of datafile by 1-4 MB only and then start datafile shrink operation. It seems like adding a few MB to the data file resets an internal counter or switch that tells it it's not in the middle of a shrink now.


Reference: 
Frequently Used Queries In SQL Server
http://www.sqlservercentral.com/Forums/Topic515417-146-1.aspx



12 comments:
Write comments
  1. great suggestion Saurabh ... right on

    ReplyDelete
    Replies
    1. I Tried the 2nd point and it worked , Thanks

      Delete
  2. Wrong, please stop spreading misinformation.

    ReplyDelete
    Replies
    1. Thats problem with a few techies, please come out of your books.

      Delete
  3. Thank you sir. I increased the Primary Data File by 4MB and was able to shrink without error.

    ReplyDelete
  4. Yes! this worked for one of our 3rd party Production databases that was stuck for a long time. The vendor didn't want to service pack it. I am now able to recover about 200GB. Thanks!

    ReplyDelete
  5. Nice.......it made my day :) Thanks a lot

    ReplyDelete
  6. Yes, increasing 4 MB in primary file, working for me.
    Thanks,

    ReplyDelete
  7. I cannot shrunk my db even after clearing the cache like you said.. I still see the same error

    "File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty."

    ReplyDelete
  8. Yes! I agree with this approach! Technically it should work

    ReplyDelete
  9. Added 4MB to the file and it now allows me to shrink the file.

    ReplyDelete

Please do not enter spam links

Meet US

Services

More Services