SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Alert Procedure for Long-Running Job

By Janine Bocciardi,

For jobs that run periodically and should take only a short time to run, a DBA may want to know when the job has been running for an excessive time. In this case, just checking to see IF the job is running won't do; the ability to make sure that it hasn't been running for a long period is needed. Matching the job id to a process id in sysprocesses requires some re-arranging of the job id to do the match. This script creates a stored procedure that will accept a job name, maximum run time allowed, and email address to notify. It will then use the job name to re-string the job number and check sysprocesses (based on the process id being part of the program name) to determine the amount of time the job has been runing, then alert if that time is over the "time allowed" parameter.

Total article views: 3685 | Views in the last 30 days: 12