November 19, 2008 at 1:34 pm
Does anyone have any scripts that can give the data that replication monitor gives us. You help here would be greatly appreciated. Thanks.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 20, 2008 at 8:28 am
Here are a good set to start with. Each links to their description in BOL. Run them on your distributor:
sp_replmonitorhelppublisher: Shows high level summary of publishers. Run with no parameters to show all publishers.
sp_replmonitorhelppublication: Shows detailed information about publication status. Run with a single NULL parameter to show all publications.
sp_replmonitorhelpsubscription: Shows detailed information about subscription status.
To show subscriber status for transactional publications:
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 0
To show subscriber status for snapshot publications:
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 1
To show subscriber status for merge publications:
sp_replmonitorhelpsubscription @publisher = NULL, @publication_type = 2
There are more ways to filter what each procedure returns based on the parameters - just read the BOL entries for each.
November 20, 2008 at 8:33 am
I knew of those SPs but they dint help me giving details like how replication monitor gives. i wanted to get a script that will give almost near info of wat replication monitor gives. any help is much appreciated.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 20, 2008 at 8:36 am
November 20, 2008 at 8:39 am
I am looking for the following details
1. publication
2. subsciptions for each publication
3. status of all the agents associated with them
4. latency
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 20, 2008 at 9:36 am
Forgive my ignorance, but...
1. publication
What do you need to know that sp_replmonitorhelppublisher and sp_replmonitorhelppublication don't provide?
2. subsciptions for each publication
3. status of all the agents associated with them
What do you need that sp_replmonitorhelppublication doesn't provide?
4. latency
What do you need that the latency column returned by sp_replmonitorhelpsubscription doesn't provide?
November 20, 2008 at 3:15 pm
Hi there! I have a script that will answer your latency question. You can find that here:
http://sqlfool.com/2008/11/checking-replication-latency-with-t-sql/[/url]
HTH!
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 20, 2008 at 3:41 pm
Maybe this can help you, too...
Select p.name As 'publication'
, p.description
, s.srvname As 'subscriber'
, s.dest_db As 'suscriber_db'
, Count(a.artid) As 'articles'
, Case
When p.status = 0 Then 'Inactive'
When p.status = 1 Then 'Active'
End As 'publication_status'
, Case
When s.status = 0 Then 'Inactive'
When s.status = 1 Then 'Subscribed'
When s.status = 2 Then 'Active'
End As 'subscription_status'
From syspublications As p
Join sysarticles As a
On p.pubid = a.pubid
Join syssubscriptions As s
On a.artid = s.artid
Group By p.name
, p.description
, s.srvname
, s.dest_db
, Case
When p.status = 0 Then 'Inactive'
When p.status = 1 Then 'Active'
End
, Case
When s.status = 0 Then 'Inactive'
When s.status = 1 Then 'Subscribed'
When s.status = 2 Then 'Active'
End;
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 9, 2009 at 10:23 am
would you happen to have a script to get details of an error happening?
January 27, 2010 at 6:22 pm
is there any way to add the latency column in your script?
August 22, 2012 at 9:37 pm
..Did you check the link that Michelle Ufford provided ?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply