Skip to main content

Script to get transactional replication errors for given subscriptions - SQL Server

Recently, when I was working with one replication issue which had an error while distributing replicated pending commands to the subscriber. I got the error details from the replication monitor. But here I am sharing one script which will give the same information as replication monitor for a particular subscriber. Please find the following script to get transactional replication errors for giving  subscriber and executes againt distribution database.
USE distribution 

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = '<PublisherDB>' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = '<SubscriberDB>' 
SET @PublicationName = '<Publication>' 

EXEC sp_helpsubscriptionerrors 

Replace needed variable's value in above script and run on subscriber side which will give you information on error date, error text and sequence number, etc. We can set up an alert when found any error within the specified time for subscriber. This is just for script to fetch error details for giving subscriber, further post will be have same transactional replication details for all subscribes, so stay tuned for it. Hope you liked this post.