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

Hilary Cotters cleanup script

Hi,
Can someone point me to where I can find a copy of Hilary's replication celanup script?
Thanks,
Andrew
go to ava.co.uk and look in the technical resources section.
There is a version of it there. I'll try to post another one soon that has a
correction for non dbo objects.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:21679998-9A1D-4645-BD94-93EFC264C7BF@.microsoft.com...
> Hi,
> Can someone point me to where I can find a copy of Hilary's replication
celanup script?
> Thanks,
> Andrew

High Light Words in Query

I creating an SQL script that will query a database and return the results to an html page. The script searches for a keyword and then displays the results. What I want to do is when the result is returned, I want to highlight the word that was being searched in the result set. I would like the word to be bolded in the query. How can this be done?please, don't cross-post

see http://www.dbforums.com/t1100603.html