SQL replication inquiry?

Question

SQL replication inquiry?

yhandz_21

SSC-Addicted

Points: 446
More actions
March 21, 2017 at 12:17 am

#401818

Is there a sql script/s to check latency and last synchronization combined with number of pending commands.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

yhandz_21 - Tuesday, March 21, 2017 12:17 AM
Is there a sql script/s to check latency and last synchronization combined with number of pending commands let say 2 publishers and per publisher has 3 different subscriber?

I'm not aware of anything that comes up with your publisher, subscribers, replication type, subscription type and executes the necessary scripts. I would guess you could figure something out from the built in stored procedures for monitoring replication. And some of what you execute depends on the type of replication. You can find the different commands here:
Programmatically Monitor Replication

For example, You can run this at the distributor to get latency, last sync and the second stored procedure would have the pending commands for a transaction replication pull subscription:

exec sp_replmonitorhelpsubscription @publisher = 'PublisherInstance', @publisher_db = 'PublisherDBName', @publication = 'PublicationName', @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N'0'

exec sp_replmonitorsubscriptionpendingcmds @publisher = 'PublisherInstance', @publisher_db = 'PublisherDBName', @publication = 'PublicationName', @subscriber = 'SubscriberInstance', @Subscriber_db = 'SubscriptionDB', @subscription_type = 1 --0 = Push, 1 = Pull

Sue

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 2

I find that already is there anyway it can give also last synchronization and latency

Sue_H SSC Guru Points: 90860 More actions · Answer 3

yhandz_21 - Friday, March 31, 2017 2:09 AM
I find that already is there anyway it can give also last synchronization and latency

?

You do get last synchronization and latency when you execute sp_replmonitorhelpsubscription so I'm not sure what you are looking for since you have it in your screenshot as well.
The only other thing I can think of is to query MSreplication_monitordatainformation in the distribution database. It's the same information in replication Monitor and has the same information from sp_replmonitorhelpsubscription:
select * from MSreplication_monitordata where publication like 'YourPublication' order by time_stamp desc

last_distsync is the last synchronization and cur_latency is the Latency displayed in Replication Monitor. The current values will likely be in different rows but you can test with that table as it will have the information you are looking for.
If you are looking for a way to have it all displayed in a single script, you'll need to create a script to get what you want using those queries and stored procedures - that's what most people end up doing. In order to get the current information for all of those, you pretty much need to create a temp table and execute different stored procedures or queries and dump the information into a temp table and then select the data out from the temp table. If you are mostly interested in a script that has the same information you see in Replication Monitor, you would likely just query MSreplication_monitordatainformation but still use a temp table to get the set of data you want. The values displayed in Replication Monitor will be in different rows when you query MSreplication_monitordatainformation. That would also be the only way to get a single script to get last sync, latency and pending commands all in script.

Sue