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
ReplyDeleteI Tried the 2nd point and it worked , Thanks
DeleteWrong, please stop spreading misinformation.
ReplyDeleteThats problem with a few techies, please come out of your books.
DeleteThank you sir. I increased the Primary Data File by 4MB and was able to shrink without error.
ReplyDeleteYes! 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!
ReplyDeleteNice.......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.
ReplyDeleteGood to hear
Delete