Post reply

Detecting Long Running Jobs

  • January 28, 2004 at 5:28 pm

    #106871

    Howdy,

    I have a couple of issues where an overnight process has kept running longer than expected. This has

    Created issues such as blocking and has prevented the database from being used. Normally the process should

    Run for no longer than 1 - 2 minutes however on this occasion it ran for 5 hours. The cause is unknown.

    Does anyone know of any stored procedures etc that can show long running jobs and status so an alert can be setup?

     

    Thanks

  • January 29, 2004 at 11:20 am

    #492222

    If you are referring to jobs running via the SQL Server Agent, you could build a job running every X minutes that would use the output from msdb..sp_help_job, which shows which jobs are currently running, but you'd have to associate the jobs with SPIDs or keep track of them in a table to figure out which ones were running a long time.

    It might be easier to just query master..sysprocesses.  Again, if you're looking for SQL Agent jobs, it could be something like this:

    select program_name, login_time, last_batch

    from sysprocesses

    where program_name like 'SQLAgent - %(Job %'

    Then you could decide for yourself "how old is too old" for values in login_time and last_batch.  You could even do this on a per-job basis, with some jobs allowed to run longer than others.

    Then you could just define a user-defined message (sp_addmessage) and raise it using WITH_LOG so that the Agent could respond to it as an Alert.

    Maybe there's an easier way that somebody could offer, but this wouldn't be that hard, really.

    Good luck!

    Chris

Viewing 2 posts - 1 through 1 (of 1 total)

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