Detect SQL Server replication issues

Below SQL code could be used to detect replication issues.

* Replication agent failures
* Subscription to a transactional publication expired

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ‘distribution’)
BEGIN
DECLARE @sql varchar(max)

SET @sql = ‘
SELECT *
INTO #temp
FROM OPENROWSET (”SQLOLEDB”,”Server=(local);TRUSTED_CONNECTION=YES;”,”set fmtonly off exec distribution.sys.sp_replmonitorhelppublication @publisher = NULL”) AS tbl
WHERE publication_id IS NOT NULL

SELECT COUNT(1) FROM #temp WHERE status IN (5, 6) OR warning = 1

DROP TABLE #temp’
EXEC (@sql)
END

Advertisements

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