Yesterday, one of my developers reported that he was experiencing a problem that he suspected was due to replication between our primary and backup servers. It turns out that he was correct.
Upon my investigation, I found that a number of subscriptions were missing. I have no proof of what happened, but I suspect that the subscriptions were removed during the installation of the most recent release.
So anyhow, I have a custom report designed to monitor the primary server. I want to add a new check that will look at each published database and determine if there is a corresponding subscription. If it cannot find a subscription, then it should be noted in my report the next morning. That way in the future when a subscription is removed, I will know within 24 hours and can pinpoint who may be involved.
I am hoping that someone has enough knowledge of the sprocs and system tables related to replication that they could help me come up with a t-sql solution.
Check each database for publication
If database has a publication, check to see if a subscription exists.
If no subscription exists, log database.
Perhaps someone knows of an article where this has been discussed?