Collecting Baselines for High Undistributed Commands

In my previous post I shared some SQL scripts which would give you the number of undistributed commands by publication, by publication and article, and by distribution database. So you can measure undistributed commands at a very high granularity (articles), or at a very low granularity (the entire distribution database.) How granular you go depends entirely on where your pain is: you could of course filter out to only a few articles, or a particular publication.

Let’s say you choose to monitor at the publication level. The best thing to do is to have a SQL Agent job run immediately prior to your “Distribution Clean Up” job so that you get the count of undistributed commands. You could even choose to create a step in the clean up job yourself. The step should be something as simple as the one below:

--create table in tempdb first

INSERT INTO tempdb.dbo.undistributedcommands
,COUNT (1)
FROM MSrepl_commands cmds
INNER JOIN MSarticles a ON a.article_id = cmds.article_id
INNER JOIN MSrepl_transactions t ON cmds.xact_seqno = t.xact_seqno
INNER JOIN MSpublications p on p.publication_id = a.publication_id
GROUP BY p.publisher_db, p.publication

Leave the job to run for as long as you want to get a baseline for. I left it for 10 days. At this point, I got a good history of undistributed commands and where a DBA had to take action to keep replication working, and when it was under heavy load but still working OK, and when replication was under no heavy load.

After this you can then decide how you wish to monitor this, like either on a NOC wall or via a job and alert when thresholds are hit.

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: Logo

You are commenting using your 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