Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Replication status check

Posted by Simon Cho on 08/16/2011

select
h.agent_id
, a.name
, rm.publication as pub_name
, rm.publisher_db as pub_db
, a.publisher_database_id as pub_db_id
, a.subscriber_db as sub_db
, comments
, h.time
, isnull(rm.cur_latency, 0) as cur_latency
, pub_srv.name as pub_srv
, sub_srv.name as sub_srv
from
distribution_svg011.dbo.msdistribution_agents a with(nolock)
join distribution_svg011.dbo.MSreplication_monitordata rm with(nolock) on rm.agent_id=a.id
join master.sys.servers pub_srv with(nolock) on a.publisher_id = pub_srv.server_id
join master.sys.servers sub_srv with(nolock) on a.subscriber_id= sub_srv.server_id
cross apply
(
select top 1 *
From distribution_SVG011.dbo.msdistribution_history with(nolock)
where agent_id=a.id
order by timestamp desc
) h
order by pub_db, pub_name

Advertisements

One Response to “Replication status check”

  1. simonsql said

    Usually, token based check it best.
    But, this case you can really quick check current status.

    Simon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: