Q:

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 overuns then locks the reindex job and that freezes our 24 / 7 server

A:

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.

