[Replication] Replication status

Rough first attempt. Doesn't include everything you might need, but gives you a quick view as to how replication is doing.

The Replication Monitor has a nasty habit of not showing you broken replication - it's usually trying to reapply the command rather than actually being in a failed status. I've watched the replication monitor show everything as fine, but when you go look at a subscription you see that it's between failed retry attempts to reapply a row. If your network is slow you can see the subscriptions flash the error icon then go back to showing everything as being okay.

I'll come back to this and see about adding more information to it. Thanks to Hilary Cotter for pointing out the source table (MSDistribution_Status) in an article - but any mistakes in the code are mine.

use distribution
go
SELECT SUM(UndelivCmdsInDistDB),
  MSdistribution_agents.NAME,
  MSdistribution_agents.publication,
  subscriber_id,
  subscriber_db FROM MSDistribution_Status
INNER JOIN MSdistribution_agents
ON MSDistribution_Status.agent_id = MSdistribution_agents.id
WHERE UndelivCmdsInDistDB > 0  --show only those that are backed up
AND subscriber_id > 0 --negative subscriber IDs are for those that
   --always have a snapshot ready
GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db
ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
 subscriber_id, subscriber_db