Stop replicating large transaction in SQL Server

Question

I have a question about trying to stop a large transaction at the publisher being distributed to the subscribers. Let's say someone accidentally updates every record in a table with 50 million records and then upon realising their mistake, sets all of the records back. In this instance, the changes would get distributed to two subscribers in a transactional replication set up. On the system it says it will 2 days to replicate to the subscribers, but what's the best way to overcome this?

I have seen it's possible and in fact very easy to skip a command using the transaction's xact_seqno,sp_helpsubscriptionerrors and sp_setsubscriptionxactseqno. However, what would happen if this was used on a transaction which was actively being distributed? Does anything need to be stopped?

If this is not the best way to overcome the issue, what would be?

Answer 1

I haven't tested this in particular, however: Stopping a transaction in progress, in itself, will not necessarily cause problems and it will rollback - any sort of transaction, including replicated transactions on on a Subscriber from a Publisher. This is because of the ACID properties that always apply to transactions, specifically, the A being Atomicity - either everything in a transaction occurs, or nothing occurs. So a stopped or failed transaction will be totally rolled-back.

I don't know of any other way to stop the transaction being replicated, however use sp_setsubscriptionxactseqno with caution, not only do you need to be sure you get the LSN correct but whatever happens it'll mean you're Publisher and Subscriber are no longer truly in-sync. In practise this might not matter, but it should be a consideration.

If you haven't already have a look at Technet / MSDN (more or less the same article depends whether you prefer Tnet of devNet), if you haven't already, and this blog post on MSDN, that'll help. N.B. sp_setsubscriptionxactseqno needs to be run on the Subscriber (and each one on which you want it to skip).

The alternative, and with no other information, what I'd recommend is just let it run. It might not be ideal, but it's the safest and least work. Is the system that mission-critical and time-dependant that the 2 days will cause significant problems?

Finally, as general advice if you're not sure what to do - fob the decision off to your manager or other higher ups. Present the options, work involved and risks/impact (including the option of just doing nothing), and blame (subtly and office-politically-appropriate) the person who made the initial 'accident' (unless that's you, then maybe you don't want to tell the higher ups).

Answer 2

No impact to whatsover transaction active or not.since this will not affect how a transaction works ,this only skips that transaction and will cause data integrity problems ..

log reader agent reads all the records updated in the publisher and updates them in distributor DB,Finally marks them as committed...

Now distribution agent then applies all commands which have higher timestamp than transaction_timestamp column in msreplication_subscriptions table

select publisher_database_id, xact_id, xact_seqno, entry_time from msrepl_transactions order by publisher_database_id

So basically we are talking about ,how to make subscriber start from another command or even skip those..You can use below command at subscriber database to skip that transaction..

sp_setsubscriptionxactseqno [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'  
        , [ @xact_seqno = ] xact_seqno   

Answer 3

How important is the your data integrity and what is your ability to recover ? What batch size did you set your replication with ? You can stop the schedulers and just let the current xact to finish so you don't enter a rollback on the subscribers. You can access the publisher's tables and dump the transactions, but it will also remove any other changes that were logged. You may have to realign the data afterwards. The replication itself should have split the updates into smaller transaction numbers (xact_seqno). And then you can remove the records from the queues. Make sure there is no backlog into the queues them selves. 50M could be pushing the limitations of the storage you gave the distribution queue tables, so once you start clearing by xact_secno, verify that the there are none that are still being written. You can check what commands are there and see if it's from the same massive update, or new activity. And, be ready to lose all other data replications from other tables or from this while you are doing it (depending on how you set up the transactions). And have a realignment plan for the subscribers once you're done before you restart the replication.

Answer 4

Depending on the size of the table, dropping/re-adding that article might be faster. Since the snapshot uses bulk-copy to transfer the rows to the subscriber, it should be pretty fast.