Monitoring Replication Status/Restarting

Occasionally, our replication between our offices stops, almost always due
to a general network failure/pesky WAN link.
Is there a table or sp that I can periodically query to detect if
replication is still running, or if replication has stopped and why?
I would ideally like to call this every 1, 5, 10 minutes directly from our
front-end program, so I can display an error/attention indicator if it has
stopped.
Part two, is if it has failed, and I am able to tell that it is a General
Network failure type error, can I exec something to jump start it?
Sorry if this is a FAQ. I looked around a bit but didn't find anything...
You could use tis type of code to see if the merge agent is running:
http://www.replicationanswers.com/Do...unningJobs.txt
As a simple solution to your problem, you could schedule the merge agent to
run every 1 minute rather than continuously.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Query distribution.dbo.MSdistribution_agents.
You can also use the replication alerts to notify you on failure.
You can't do much about general network failures programmatically. Generally
these are transient, when they last longer you have to run tracert to find
out exactly where the network failure is and hunt down your network admins
or call your ISP, or their ISP.
I normally take my 3rd job step and loop it around to the first job step on
failure or have the jobs restart every 5 minutes and run them continuously,.
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
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:11ph8lnhs9dpg6a@.corp.supernews.com...
> Occasionally, our replication between our offices stops, almost always due
> to a general network failure/pesky WAN link.
> Is there a table or sp that I can periodically query to detect if
> replication is still running, or if replication has stopped and why?
> I would ideally like to call this every 1, 5, 10 minutes directly from our
> front-end program, so I can display an error/attention indicator if it has
> stopped.
> Part two, is if it has failed, and I am able to tell that it is a General
> Network failure type error, can I exec something to jump start it?
> Sorry if this is a FAQ. I looked around a bit but didn't find anything...
>
|||Thanks.
To set it up to loop back to step 1, do I just edit the Agent properties,
then edit the Job Step "On Failure Action" to Goto Step:1?
Or, just go to the Schedules tab and add another Schedule to just restart
every 5 minutes.
Is one way better than another? I kind of like the delay in there, so it
will wait a bit to restart if down, so hopefully the network has fixed
itself by the next time it retries.
Any downsides? Does it mess anything to keep restarting, say even if the
service is already up, or in the middle of a replication, to have it just
start up again? I would assume it would not, but I am just maybe being
overparaoid here...
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:unY4anM$FHA.140@.TK2MSFTNGP12.phx.gbl...
> Query distribution.dbo.MSdistribution_agents.
> You can also use the replication alerts to notify you on failure.
> You can't do much about general network failures programmatically.
> Generally these are transient, when they last longer you have to run
> tracert to find out exactly where the network failure is and hunt down
> your network admins or call your ISP, or their ISP.
> I normally take my 3rd job step and loop it around to the first job step
> on failure or have the jobs restart every 5 minutes and run them
> continuously,.
> --
> 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
> "Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
> news:11ph8lnhs9dpg6a@.corp.supernews.com...
>
|||The overhead with having it loop is negligible. I prefer to schedule it as
typically I have many subscribers and I want to avoid having too many agents
running concurrently.
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
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:11s8mvrf7e6rq82@.corp.supernews.com...
> Thanks.
> To set it up to loop back to step 1, do I just edit the Agent properties,
> then edit the Job Step "On Failure Action" to Goto Step:1?
> Or, just go to the Schedules tab and add another Schedule to just restart
> every 5 minutes.
> Is one way better than another? I kind of like the delay in there, so it
> will wait a bit to restart if down, so hopefully the network has fixed
> itself by the next time it retries.
> Any downsides? Does it mess anything to keep restarting, say even if the
> service is already up, or in the middle of a replication, to have it just
> start up again? I would assume it would not, but I am just maybe being
> overparaoid here...
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:unY4anM$FHA.140@.TK2MSFTNGP12.phx.gbl...
>