Detecting long running SQL Agent jobs

Typically, you would like SQL Agent jobs to send error notification if it failed for some reason. If there’s no email, the job “should” be running ok, right?

 

Actually, one of the SQL Agent job that I’ve observed actually seems to be stucked half way through in one of the step that executes multiple SSIS packages. One of the package are extracting data from another SQL server via link server. For some reason, the job will not fail even if there’s some connection issue with the link server. The job does not fail in this case, thus no email notification will be sent.

 

Therefore, I have to check which SQL Agent job has run for too long. Fortunately, this has been done by many others. The trick is to make use of some SQL server built-in stored proc to query for the job status and its start time. Compare the start time with current time, coupled with the execution status yields you the long running jobs. The following are the reference that I used to built my own long running job detector.

 

http://www.databasejournal.com/features/mssql/article.php/3500276

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

 

To test out the stroed proc, I’ve created a job that executes the sql statement “waitfor delay '1:00:00'” to simulate a job that runs forever.