So, a few weeks back I posted a code snippet for how to quickly find replication errors in MSSQL. At the time, I didn’t have the opportunity to address an issue in the script to properly call the system stored procedure sp_browsereplcmds. This resulted in the user having to manually copy and past a value from the first result to get the final answer.
Well the issue cropped up again; this time I was able to spend a little extra time to refine the code snippet and fix that script issue.
Here’s the updated version:
USE distribution GO DECLARE @comandID INT DECLARE @publisherID INT DECLARE @articleID INT DECLARE @error_seqno VARBINARY(16) DECLARE @command_seqno VARBINARY(16) DECLARE @seqno NCHAR(22) SELECT TOP 1 @error_seqno = xact_seqno , @comandID = command_id FROM msrepl_errors WHERE error_code > '' ORDER BY id DESC SELECT TOP 1 @publisherID = publisher_database_id , @articleID = article_id , @command_seqno = xact_seqno FROM msrepl_commands (NOLOCK) WHERE command_id = @comandID AND xact_seqno = @error_seqno SET @seqno = CONVERT(NVARCHAR(MAX), @command_seqno, 1)
EXEC sp_browsereplcmds @article_id = @articleID , @command_id = @comandID , @xact_seqno_start = @seqno , @xact_seqno_end = @seqno , @publisher_database_id = @publisherID GO
As an added bonus, I also stumbled across this fine script on StackOverflow for checking the overall health of your replications (my script will report the most recent error, even if your replication is not currently experiencing an error). https://stackoverflow.com/questions/220340/how-do-i-check-sql-replication-status-via-t-sql