SQL 2000 script to kill Agent job if it overuns

On a legacy SQL 2000 box that is awaiting upgrade

Does anyone have a script to kill / abort a job in SQL Agent 2000 if it over runs a set duration or overlaps with another agent job.

This is causing us a serious problem every few weeks a job ove开发者_开发问答runs then locks the reindex job and that freezes our 24 / 7 server

One solution would be to add a step at the beginning of the job with which the long-running job overlaps which checks whether the long-running job is still running, and if it is issue an sp_stop_job command.

You can get the current execution status of a job using sp_help_job, but its output is not very easy to manipulate in a query.

Various methods of coercing the output of sp_help_job into a temp table in SQL 2000 are discussed here - alternatively you can query msdb.sysjobhistory to get the execution status information.