Viewing Undistributed Command in Replication

It’s been very nearly a year since I have worked or indeed posted a blog about replication. But today I was sat in front of the Replication Monitor with some failing publications. Despite my years worth of activity in between, I picked things back up pretty quick. I needed to see the command that was failing, so that meant using sp_browsereplcmds. And I put together a script that will help me to do this. Generally the transaction sequence number can be picked up from the error message in the details window for the subscriptions. Mercifully the replication topology is quite small, so I am able not use the “@xact_seqno_END” parameter. Your mileage may vary.

There’s a query that returns an article; this is here in case you have the article that is causing the issue.

This query is especially useful when the error message is not entirely clear. Usually the error messages in replication follow a familiar template, and so when I see some that I have not seen before this is a helpful query to run to uncover the exact command that is causing the error.


USE distribution;

DECLARE @PublisherDB SYSNAME
	,@PublisherDBID INT
	,@SeqNo NCHAR(22)
	,@CommandID INT

SET @PublisherDB = N'myPublisher'
SET @SeqNo = N'0x000921630000191D000C00000001'
SET @CommandID = 1

SELECT @PublisherDBID = id
FROM dbo.MSpublisher_databases
WHERE publisher_db = @PublisherDB

SELECT *
FROM MSarticles
WHERE article LIKE '%articleName%'

CREATE TABLE #browsereplcmds (
	xact_seqno VARBINARY(16) NULL
	,originator_srvname SYSNAME NULL
	,originator_db SYSNAME NULL
	,article_id INT NULL
	,type INT NULL
	,partial_command BIT NULL
	,hashkey INT NULL
	,originator_publication_id INT NULL
	,originator_db_version INT NULL
	,originator_lsn VARBINARY(16) NULL
	,command NVARCHAR(1024) NULL
	,command_id INT
	)

INSERT INTO #browsereplcmds
EXEC sp_browsereplcmds @xact_seqno_START = @SeqNo
	,@publisher_database_id = @PublisherDBID;

SELECT *
FROM #browsereplcmds

DROP TABLE #browsereplcmds

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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