Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Shrinking a Database and Transaction Log Backups problem

Recently we purged millions of rows from a database as it was determined that we did not need to retain it.  Since we were low on free disk space, we decided to shrink the primary data file (MDF).  Usually I wouldn't recommend shrinking the database, but this was a situation where it met the exception to the rule. 

Since the amount of space to be reclaimed was around 50GB, I decided to run the shrink in smaller batches (1 GB each pass).  After about an hour of shrinking the database down, I started to receive the following error:

File ID 1 of database ID 12 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.

After much troubleshooting and googling, I threw in the towel and opened a case with Microsoft. 

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."

It was determined that a transaction log backup had run at the same time that the shrink was occurring which is what caused this latch problem. 

There is no way to fix this issue except to restart the SQL Server service, since "such type of latches are in-memory structures" and "there are no DMVs in SQL Server 2005 which would allow us to view unreleased latches."

My recommendation to avoid this issue is to disable your transaction log backups while you are shrinking the database.  Don't forget to re-enable it when you are done!

Print | posted on Thursday, November 08, 2007 2:31 PM | Filed Under [ SQL Server - Database Administration ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET