Find Transaction Holding up Replication

First run the following statement on the subscriber database

Use subscriber
go
select * from subscriber..MSreplication_subscriptions
go

The above syntax gives useful subscription information including the transaction_timestamp, which can be used to identify what is being run into replication at the given time.

Use the transaction_timestamp value from the output and use it, to run the following sql against the distribution database as below:

Use distribution
go
sp_browsereplcmds @xact_seqno_start = '0x00000000000000000068000000000000'

The SQL above can return the details of a transaction which will include all commands held in that transaction_timestamp.

This is useful if replication is failing as it shows which command is at the top of the command list waiting to go into the subscriber. E.g.

{CALL “sp_MSins_SCOTTA_MOHSIN” (399999, 1, 2)}

To further troubleshoot, run the command above (without the { } and CALL), into the subscriber database, to see details of the error.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: