November 27, 2006 at 4:39 am
Hi There.
Does anybody know how I can create a script that will tell me if there is a large backlog of undistributed commands in my trans replication config. My subscriber database is on a remote server (with a poor connection.. out of my hands!!). I know I can view this in replication monitor but I want to be automatically flagged when there is a large backlog of transactions to be distributed.
I am thinking that the best way to do this will be to create a job that checks every hour or so and sends me an email if a certain threshold is overrun. I guess my question is, where are the undistributed commands held (distribution database??) if my subscriber is temporarily unavailable and is there some way that I can do a count to ensure that they are not backing up out of control.
Would appreciate any help or links anybody could provide on this one!!
Thanks
November 28, 2006 at 8:13 am
If you are using SQL Server 2005. Replication monitor does that for you.
* Noel
November 28, 2006 at 8:26 am
Hi Noel.
Yes, I am using SQL 2005. I know that I could see the backlog by clicking on the subscription properties and checking manually. What I need is to be notified (via email?) automatically that there is a large backlog for whatever reason. I have since discovered that the best way to do this is probably via the alerts module where I will get the transactional replication latency alert to notify me if there is a backlog from the distribution database to the subscriber. Do you know if there are any better ways of achieving this or if there are any know issues with alerts in terms of reliability etc?
Thanks!
Garry.
November 28, 2006 at 1:03 pm
Hi GArry,
I don't know the direct answer to your question, but I can offer a way for you to perhaps find what you seek on your own.
If you start a profiler trace, you can watch/record the database commands that are issued when you click on the subscription properties. Armed with this trace, you might be able to figure out where the numbers come from.
hth jg
February 9, 2010 at 7:20 pm
Someone told me to try this but I haven't been able to prove it yet, due to a problem I am having, but it might help you:
select
cast(count(*) as varchar(20)) + ' transactions are pending replication. The oldest transaction pending replication is ' + cast(min(entry_time) as varchar(20)) + ' minutes old',
count(*) as NumOfTrans,
min(entry_time) as minEntry,
max(entry_time) as maxEntry,
datediff(minute, min(entry_time), max(entry_time)) as minuteDiffFirstToLast,
datediff(minute, min(entry_time), getdate()) as minuteDiffFirstToNow
from
distribution.dbo.msrepl_transactions
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply