Blog: Making Sense of Replication Errors in Replication Monitor

This post is participating in T-SQL Tuesday #66 which focuses on monitoring for the month of May. This month’s gracious host is Catherine Wilhelmsen (blog|@cathrinew). For our part in this blog event, I am going to focus on how to interpret and understand the errors from SQL Server’s built-in monitoring tools Replication Monitor.

If you’ve ever had to deal with errors in Replication Monitor, your first experiences with it probably involved confusion. Sometimes the error messages are too generic and do not help you find a resolution.

Replication Monitor also tells you the command that failed. This clears up everything, right?

The command reported by Replication Monitor will look something like this:

(Transaction sequence number: 0x0001C7A40000AAE700C700000000, Command ID: 1)

Helpful!! Right? Well, actually, it is very helpful.

Replication Monitor

What to do with it?

There is a system stored procedure in the distribution database that can tell you exactly to what this jumble of letters and numbers refers. The procedure sp_browsereplcmds takes the values above as parameters and outputs the command that caused the errors.

First let’s understand what the values mean.

Transaction sequence number:

This is the identifier for the transaction which contains one or more commands.

A single transaction on the publisher may affect multiple records. To ensure exact replay of the transaction on subscribers, the transaction is broken up into multiple commands that each update a single record. A transaction that updates 100 rows on the publisher will result in 100 commands that each update a single record on the subscriber. They are tracked as a transaction sequence so that if any one of the commands fails, the whole set is rolled back. This prevents half of the transaction from committing.

Command ID:

The commands each have a unique ID within the scope of the transaction sequence. The commands start at 1 and increment by 1 for each new command. This ensures that they are updated in the same order as they were on the publisher.

sp_browsereplcmds:

I won’t go into full details here as that would be very boring. For the full syntax, see the command in SQL Server Books Online.

For this scenario, we are concerned with 4 of the parameters.

@xact_seqno_start and @xact_seqno_end allow you to specify a range of sequence numbers to return multiple transactions. To find the specific command in our error message, we are going to supply the transaction sequence number from Replication Monitor for both parameters. This will limit the output to only the transaction sequence we want.

At this point, we could simply run the procedure with these 2 parameters. It would output all commands associated with the transaction sequence, and we can easily scroll down to the correct command ID. But that’s too easy.

A third parameter allows us to specify the command ID to return: @command_id. This will limit the output to the exact command we want.

At this point, we can’t run the procedure. If we specify the command ID, we must also specify transaction sequence number (which we already did) and the publisher database ID. This isn’t the database ID of the database on the source server. This is the ID associated with the publisher database in the table dbo.MSpublisher_databases in the distribution database. Query for this ID and then you will be ready to run the procedure.

Example usage:

Using the error above, we will run the command.

(Transaction sequence number: 0x0001C7A40000AAE700C700000000, Command ID: 1)

Use distribution;

Declare @PublisherDB sysname,
      @PublisherDBID int,
      @SeqNo nchar(22),
      @CommandID int

-- Set publisher database name and values from Replication Monitor
Set @PublisherDB = N'MyDatabase'
Set @SeqNo = N'0x0001C7A40000AAE700C700000000'
Set @CommandID = 1
 
-- Find the publisher database ID
Select @PublisherDBID = id
From dbo.MSpublisher_databases
Where publisher_db = @PublisherDB
 
-- Get the command
Exec sp_browsereplcmds
      @xact_seqno_start = @SeqNo,
      @xact_seqno_end = @SeqNo,
      @command_id = @CommandID,
      @publisher_database_id=@PublisherDBID;

Re-blogged from Making Sense of Replication Errors in Replication Monitor.