Post reply

Query to determine jobs that abended due to server re-boot

  • January 31, 2008 at 4:57 am

    #127655

    Hello

    We need to query the msdb to determine which jobs were running during a re-boot so that we can re-start those jobs after the re-boot.

    Not sure how to go about this. It looks like the run_status field is what I'm after but can't find a list of all the different possible statuses besides 1 and zero.

    Occasionally server gets rebooted and long running overnight batch jobs need re-starting.

    thanks!

    thanks

    SQL_EXPAT

  • January 31, 2008 at 6:08 am

    #773695

    Job_status values are listed under sysjobhistory system table in BOL. I'm not sure what the value would be if the job stopped because of a restart. You might also want to look at the sysjobactivity system table.

    Greg

  • January 31, 2008 at 6:52 am

    #773722

    hi Greg

    Thanks for the pointers. This works for me:

    select j.name, jh.message, jh.run_status , jh.run_date , jh.run_time , jh.run_duration

    from msdb..sysjobhistory jh

    join msdb..sysjobs j on jh.job_id = j.job_id

    where message like 'The job was stopped prior to completion by Shutdown%'

    cheers

    thanks

    SQL_EXPAT

  • January 31, 2008 at 10:16 am

    #773853

    Hi cranfield,

    Don't know if this would be of use, but you could make use of your code to autostart jobs again following a reboot. You can do this by setting SQL Server to scan for startup procs and set the proc, to carry out the check, to start jobs that were affected. You mark the proc for startup using sp_procoption. You can also get it to send you an email or log somewhere that the event has happened.

    Just a thought if your able to automate and don't require manual intervention.

  • February 1, 2008 at 4:25 am

    #774123

    that is a GREAT tip. I will work on it.

    thanks.

    -- cranfield

    thanks

    SQL_EXPAT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply