//
you're reading...
SQL, Technical support guide

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.

 

image

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

image

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

Advertisements

About Abhishek Joshi

Abhishek Joshi having 8 years exp. in IT Industry and 5 exp in System Managment. Between Oct 2009 to Oct 2011 Abhishek was the only MVP in India for SCCM 2007 technology. Abhishek has started his online carrier from Blogcastrepository.com in Dec 2006 and post that actively participated on Technet forum.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Visitors Location

RSS Abhishek Joshi’s TechNet Discussions

  • An error has occurred; the feed is probably down. Try again later.

Twitter Updates

  • RT @CMOMaharashtra: Government Of Maharashtra decides that all State roads will be toll tax free till 11th November midnight. 1 year ago
  • RT @nitin_gadkari: It has been decided to suspend Toll across all National Highways till midnight of 11th November to facilitate smooth tra… 1 year ago

Top Clicks

  • None

Blog Stats

  • 65,670 hits
Advertisements
%d bloggers like this: