October 11, 2004 at 12:08 pm
Hello,
Is there a way to make sure that the replication agents are running (merge Replication)? I've run into a situation where they sometimes are not running (i.e. after a reboot) and have to be manually re-started.
I was thinking that maybe I could find a script to start the agent(s) involved, but I haven't found anything yet.
Thanks in Advance,
Chris
October 11, 2004 at 5:49 pm
October 12, 2004 at 8:24 am
Greetings,
I have an ASP process that I use to do replication to a tablet and I needed to find out when the job was finished so I knew when I could disconnect. I wrote it up as a proc but here is the meat of it. Just pass in the jobname (@matchname) that you want to check on.
SELECT
job_id = J.job_id,
server = J.originating_server,
jobname = J.[name],
rundate = j.date_created,
runstatus = case H.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end,
message
FROM (msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobhistory H
ON j.job_id = H.job_id) --you could also create a target category and join to it
where J.[name] like '%' + @Match_Name + '%' and step_name ='(Job outcome)'
order by j.date_created
Not quite sure if thats what you want but...
regards,
Chris
October 12, 2004 at 8:36 am
I was looking for a way to see if the job was stopped and to restart it if it was. I could use a script and just start the job periodically, then if the job was already started, it would just generate an error. I just hate to throw those errors out constantly when the system is actually working.
October 12, 2004 at 9:51 am
Right! The only way I could find to determine if a job was running or not was to check the sysjobhistory table and check on its status. Its either 'In progress' or its not.
Spent alot of time going thru the SQL-DMO features and functionality and I could really find nothing to determine if a job was running, only that it existed. Think somebody here pointed me to the sysjobhistory table.
Chris
October 12, 2004 at 2:55 pm
One thing to add, hopefully this will help. If you check the value is sysjobservers.last_run_outcome you can determine if the job has failed or what it's last status was.
I was working on a scheduled job to run and check the status of jobs and attempt to restart them if they had failed. My goal was to try a restart three times then on the fourth time email the verbose outcome of the job to me. The idea was to notify me when the job failed after several restarts as well as send the verbose steps. I was using the information in this join (select sysjobs.job_id, sysjobs.name, sysjobservers.last_run_outcome,sysjobs.category_id,0 from sysjobs
inner join sysjobservers on sysjobs.job_id = sysjobservers.job_id) to build my list of failed jobs. From there my plan was to execute the commands in sysjobsteps.
If you get your script working please share it with us.
This and 25¢ will get you a cheap cup of coffee.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply