Asked by:

Initial snapshot not ready, can I list these subscriptions?

Question

I am creating a procedure that monitors my replication subscriptions and sends out emails if something is wrong. We have ~150 (transactional replication) subscriptions so manual monitoring is a pain.

I found a useful procedure that helps, sp_replmonitorhelpsubscription. My problem is that I can't filter out subscriptions with the following status: "The initial snapshot for publication
'xxx' is not yet available."

Any way I can list these subscriptions with sql or some nice procedures?

All replies

I query msrepl_errors and msdistribution_history for my statuses. This way I can filter out transient error messages - like network errors, and then detect if a subscriber has not sync'd within a certain time period.

I don't see any reference back to the subscription in msrepl_errors. There is an ID column, does that point somewhere?

I found one potential way, using the sp_helpsubscriptions. There is a flag the called "subscription status" and if it's set to 0 this means the subscription is not replicating, potentially waiting for snapshot.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.