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 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 work around 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.




Refrerence: http://www.sqlservercentral.com/Forums/Topic515417-146-1.aspx

8 comments:

  1. great suggestion Saurabh ... right on

    ReplyDelete
  2. Wrong, please stop spreading misinformation.

    ReplyDelete
    Replies
    1. Hi AngieK

      If you feel this is wrong , I appreciate if you tell me what's wrong with solution. There is a possibility you have some other issue which cause this solution doesn't work for you. I also appreciate to come with reason when you say its "misinformation"

      This is tested solution and worked for me and many of my DBA friends

      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