The Shrink Operation Is Blocked

Recently I was  shrinking temp DB database files, generally it wont take much time shrink the database however this time it was taking a long time. When checked the SQL server logs found below message.

DBCC SHRINKFILE for file <ID> is waiting for the snapshot transaction with timestamp <number>and other snapshot transactions linked to timestamp <number> or with timestamps older than <number> to finish.

After searching on net , got below article (in quotes) directly copied from TechNet. Highlighted step help me.

“The Shrink Operation Is Blocked

It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For example, if the error log contains the following error message:

Copy Code

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot 
transaction with timestamp 15 and other snapshot transactions linked to 
timestamp 15 or with timestamps older than 109 to finish.

This means that the shrink operation is blocked by snapshot transactions that have timestamps older than 109, which is the last transaction that the shrink operation completed. It also indicates that the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num, or first_snapshot_sequence_num columns in the view contains a number that is less than the last transaction completed by a shrink operation (109), the shrink operation will wait for those transactions to finish.

To resolve the problem, you can do one of the following tasks:

• Terminate the transaction that is blocking the shrink operation.
• Terminate the shrink operation. If the shrink operation is terminated, any completed work is retained.
• Do nothing and allow the shrink operation to wait until the blocking transaction completes.”

However, after terminating shrinking option, when i again did the shrink operation i got an below error. Recycling the SQL services helped me to solved the issue. After recycling SQL services shrinking operation started working properly

Thanks and Regards |Abhishek Joshi |www.AbhishekJoshi.com