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



23 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
    2. the adding a few kb to the data file allowed it to continue. Thanks. AngieK, saying "wrong" when its not is not helpful, hopefully you have matured in the last seven years

      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
  10. Tried this solution of increasing the data file by 4MB and it worked like magic, for me. Thank you for this.

    ReplyDelete
  11. Yes, Its worked for me as well...Thanks for your support

    ReplyDelete
  12. Thank you, point 2 worked for me :)

    ReplyDelete
  13. Thank you Saurabh - positive result also. Keep safe.

    ReplyDelete
  14. thank you so much . after increasing the Initial size in datafile 4 MB now we are able to shrink. thanks for your solution

    ReplyDelete
  15. I keep seeing suggestions to increase the file size - but how do I do this?

    ReplyDelete
    Replies
    1. Right click database in SSMS, click properties , in files menu you can increase file size

      Delete
  16. Thanks much @Saurabh,

    I tried shrinking the file after increasing the file size by 1 GB and it worked.

    ReplyDelete