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
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
great suggestion Saurabh ... right on
ReplyDeleteThanks !!
DeleteWrong, please stop spreading misinformation.
ReplyDeleteHi AngieK
DeleteIf 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
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
DeleteThank you sir. I increased the Primary Data File by 4MB and was able to shrink without error.
ReplyDeleteGr8 !!
Deletegood solution
DeleteYes! 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!
ReplyDeleteCool
DeleteNice.......it made my day :) Thanks a lot
ReplyDeleteYes, increasing 4 MB in primary file, working for me.
ReplyDeleteThanks,
I cannot shrunk my db even after clearing the cache like you said.. I still see the same error
ReplyDelete"File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty."
Yes! I agree with this approach! Technically it should work
ReplyDeleteAdded 4MB to the file and it now allows me to shrink the file.
ReplyDeleteTried this solution of increasing the data file by 4MB and it worked like magic, for me. Thank you for this.
ReplyDeleteYes, Its worked for me as well...Thanks for your support
ReplyDeleteThank you, point 2 worked for me :)
ReplyDeleteThank you Saurabh - positive result also. Keep safe.
ReplyDeletethank you so much . after increasing the Initial size in datafile 4 MB now we are able to shrink. thanks for your solution
ReplyDeleteI keep seeing suggestions to increase the file size - but how do I do this?
ReplyDeleteRight click database in SSMS, click properties , in files menu you can increase file size
DeleteThanks much @Saurabh,
ReplyDeleteI tried shrinking the file after increasing the file size by 1 GB and it worked.