SQL Server Replication Health Check

Here are some scripts I use to check on replication.

Undelivered commands in Distribution database

--Undelivered Commands in Distribution Database
Select		da.publication, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication

With Article Details

--Undelivered Commands in Distribution Database - Article Details
Select		da.publication, msa.article, msa.article_id, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB, GETDATE() QueryTime
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication, msa.article,msa.article_id
ORDER BY UndelivCmdsInDistDB DESC

Hot Articles
This script will show you what articles have the most commands at the moment.

--Hot Articles
--Command count for all articles
SELECT COUNT(1), c.article_id, a.article
FROM distribution.dbo.MSrepl_commands c JOIN distribution.dbo.MSarticles a on c.article_id = a.article_id
GROUP BY c.article_id, a.article
ORDER BY 1 DESC

Big Ticket Batches
This script will show you your large batches waiting to be distributed.

--Big Ticket Batches
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [xact_seqno], count(1)
FROM [distribution].[dbo].[MSrepl_commands]
GROUP BY [xact_seqno]
HAVING count(1) > 1
ORDER BY count(1) DESC

Inspect Specific Batch
You can use the following procedure to inspect a batch. You can get a batch number from the Big Ticket Batches query above.

--Plug in the xact_seqno for the batch into this Stored Procedure
EXEC distribution.dbo.sp_browsereplcmds
	@xact_seqno_start = '0x000223DE000147D8001B',
	@xact_seqno_end ='0x000223DE000147D8001B'
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