Shrink File on Replication

Shrink File on Replication database  , that time blocking Occurring

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

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.