SQL Server Performance Forum – Threads Archive

Auto-restart Trans. Rep. via Job/TSQL?

Greetings, The Setup: I have 2 sql 2000 machines using trans. Rep.(srv2 has a pull sub. to srv1 over a non-dedicated line…*sighs*) The Issue: As expected our Rep. connection goes down from time to time, and I manually have to restart it. To remedy this (as I am a lazy monkey) I thought I could add a recursive step to the job that just keeps trying to start sync-ing until the connection is functioning properly. This seemed to work well, but then I thought it’d be nifty to receive and alert notifying me of the jobs status both when it’s down and when it’s back up…this is where I loose my cookies. First attempts were made via MS Outlook and notifications worked fine for down status, but never for up. While troubleshooting that I started having problems with Outlook and SQL Mail that just discouraged me to all monkey. Sometimes Outlook would be working fine, then out of the blue stop sending messages. So I know I need to do more home work on using SQL Mail with Outlook, bit the alerts I can put aside for a bit. The Question: I would like to ask how others deal with this issue. In addition, to any advice. I believe there has to be a better method then making recursive steps in a job…but I’m no DBA. Thanks in Advance! Justin

Looking into this too. I guess one could have a seperate job that checks the replication jobs and do something like the following: – Start logreader if it stopped
– Set the publication ‘active’ if it became ‘inactive’
– Run the snapshot job once (optional)
– Run the distribution job once

That’s true, I never thought of a second job to monitor it. Thanks. Last night, between beers and cartoons, I thought of creating a small .net app to execute in the case that rep. failed. My thinking is that not only can this app. shoot out alerts via email, but also run a stored procedure to start replication and check if it start. I haven’t worked out the T-SQL syntax due to the fact that I’ve never started rep. via code. However, do you suppose an approach like this has an immediate drawbacks that I’m not seeing? Thanks again Justin

IF you need alerting via email then try to fix errors on SQLMail part of it, that helps in first hand atleast when it is failed. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You is right. Alerts are second to my replication problem, so yes I will tackle sqlmail at some point.

]]>