Hilary: Replication Latency (Part 2)

Hilary,
I know you have helped me with a replication latency script in the past. But
what i want is more of the following .
I have trans replication set up in continuous mode.
If I stop the log reader agent and the distribution agent while my publisher
is receiving some changes, I want to find out
a) how many commands/transactions are in the publisher that have not made it
to the distribution db ( Log Reader Agent Latency )
b) how many commands/transactions are in the distributor that have not made
it to the subscribingdbs ( Distribution Agent Latency )
c) How long are these command/trans are in the publisher since they came in
and not made it to the distribution db and also how long have they been
sitting in the distribution db since they came in and not made it to the
subscribing db
So primarily no. of outstanding cmds/trans and time is what im looking at ..
Is this something that you may have a handy script already ?
Thanks
answers inline
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23izmyu4FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> I know you have helped me with a replication latency script in the past.
But
> what i want is more of the following .
> I have trans replication set up in continuous mode.
> If I stop the log reader agent and the distribution agent while my
publisher
> is receiving some changes, I want to find out
> a) how many commands/transactions are in the publisher that have not made
it
> to the distribution db ( Log Reader Agent Latency )
you can tell the number of transactions by issuing by sp_repltrans - this
will give you the number of transactions - but not the number of commands

> b) how many commands/transactions are in the distributor that have not
made
> it to the subscribingdbs ( Distribution Agent Latency )
select * from distribution.MSdistribution_status
Look at the undistributed commands column
> c) How long are these command/trans are in the publisher since they came
in
> and not made it to the distribution db and also how long have they been
> sitting in the distribution db since they came in and not made it to the
> subscribing db
run this in your distribution database
select time, entry_time from
SubscriberServerName.SubscriberDatabaseName.dbo.MS replicationX_subscriptions
,
msrepl_transactions
where transaction_timestamp=xact_seqno
You may want to run this in your subscription database as well to get time
in seconds as opposed to minutes
alter table MSreplication_subscriptions
alter column time datetime

> So primarily no. of outstanding cmds/trans and time is what im looking at
...
> Is this something that you may have a handy script already ?
> Thanks
>
sql