May 17, 2005 at 7:37 am
Can anyone please advise me how I check whether there are any transactions waiting to be replicated from the subscriber to the publisher when using updatable subscriptions? Also, how can I tell if both the publiisher and subscriber tables are identical? I am new to replication and it has stopped working and I want to uninstall replication. How do you uninstall replication, as when I looked at disabling from EM it said you have to manually uninstall at the subcriber end!
thanks in advance
May 18, 2005 at 2:16 am
If the subscriber is an immediate updating one, basically there can't be any commands waiting as changes on the subscriber exist in a distributed transaction with the publisher, so I assume you are using queues? In this case you can use sp_replqueuemonitor.
To check the data is identical, you can run a validation in the replication setup. This won't highlight the problematic rows, so if there is an issue and you want to investigate it at such a level, have a look at Redgate's DataCompare tool which'll do just this. Alternatively you could write a function which uses binary checksums to achieve the same end.
Disabling publishing is fine at the publisher side. If any subscription info is left hanging on a subscriber, and the subscriber database is not used as a publisher in its own right, sp_removedbreplication can be used to remove the replication system tables.
HTH,
Paul Ibison, SQL MVP
Paul Ibison
Paul.Ibison@replicationanswers.com
May 18, 2005 at 8:41 am
Hello Paul,
I am using updatable subscription with immediate updating. So are you suggesting that there can never be any outstanding transactions awaiting replication from the subscriber to the publisher?
If that is the case all well and good if not how do I check for uncommited transactions?
T
May 18, 2005 at 9:08 am
This is correct. There can be commands waiting to be delivered from the distribution database to the subscriber - in which case sp_browsereplcmds can be used - but there can't be the reverse.
If a subscriber does a data change, it'll either commit or rollback when entering the distributed transaction. If a user starts a subscriber change in a transaction and doesn't commit, then DBCC OPENTRAN can be used to get the SPID and DBCC INPUTBUFFER used to see what the command was.
HTH,
Paul Ibison, SQL MVP
Paul Ibison
Paul.Ibison@replicationanswers.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply