Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Monitor Replication Using Scripts Expand / Collapse
Author
Message
Posted Wednesday, November 19, 2008 1:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
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
Post #605381
Posted Thursday, November 20, 2008 8:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:09 PM
Points: 362, Visits: 898
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.


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #605862
Posted Thursday, November 20, 2008 8:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
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
Post #605865
Posted Thursday, November 20, 2008 8:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:09 PM
Points: 362, Visits: 898
OK. There are a lot of tabs in replication monitor. What specific information are you looking for?

Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #605870
Posted Thursday, November 20, 2008 8:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
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
Post #605872
Posted Thursday, November 20, 2008 9:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:09 PM
Points: 362, Visits: 898
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?


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #605925
Posted Thursday, November 20, 2008 3:15 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 1:09 PM
Points: 468, Visits: 291
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/

HTH!


Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Post #606141
Posted Thursday, November 20, 2008 3:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 1:09 PM
Points: 468, Visits: 291
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
Post #606155
Posted Wednesday, December 9, 2009 10:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 7, 2014 6:03 PM
Points: 538, Visits: 1,929
would you happen to have a script to get details of an error happening?
Post #831602
Posted Wednesday, January 27, 2010 6:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 7, 2014 6:03 PM
Points: 538, Visits: 1,929
is there any way to add the latency column in your script?
Post #854853
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse