Wednesday, 18 November 2015

SQL Server Remove Command from Replication

I managed to create a script on a replicated database that when run on the live server errored - oops! I've had this before and it stops any other transactions from being replicated so I needed to get rid of the failing command and deal with the script another way.

If you go into the distribution database there is a table called MSrepl_commands. This has all the commands that have been and need to be executed on subscribers. You can literally delete whichever commands you don't want!

If you go into the Replication Monitor and navigate your way through to where the error is, it kindly tells us what the error is and also which command it is. The transaction sequence number relates to
MSrepl_commands.xact_seqno and the command id to MSrepl_commands.command_id.

With this, you can create a DELETE statement to get rid of the command and within a minute it had sorted itself out. Obviously I had to rectify the problem with the original script!

This bit of SQL will do it...

FROM MSrepl_commands
WHERE xact_seqno = 0x0002434800000DC8000100000000
AND command_id = 3

Be extra cautious here as deleting the wrong thing could have a catastrophic impact!!!