April 18, 2008 at 4:01 am
Here is one senario if there is a backup and restore for a database is setup in job. if the job starts and is hung. is there any way that i can setup an alert for job hangup.
Please let me know . Thanks in advance.
Regards,
Helin Shah
April 18, 2008 at 4:15 am
Helin
It's not something I've done before. But one thing you could try is to monitor the sys.dm_exec_requests view and check that cpu_time and/or logical_reads are increasing all the time for the process in question.
John
April 18, 2008 at 4:28 am
You could set up your own job that constantly monitors relevant jobs and checks to see how long they have been running for. If there's a particular job that you expect to only run for 10 minutes, and you find it's been running for 30 minutes then you raise an alert.
Use xp_sqlagent_enum_jobs to return the status for a given job or set of jobs. Insert the results into a table and then look at the state column to determine whether it's running or not. Compare the current time to the job start time and you'll know whether it's been running too long. You can then send out an email alert or whatever.
exec xp_sqlagent_enum_jobs 1, sa -- returns all jobs owned by sa
/*
Status values:
0 - Not idle or suspended,
1 - Executing,
2 - Waiting For Thread,
3 - Between Retries,
4 - Idle,
5 - Suspended,
6 - WaitingForStepToFinish,
7 - PerformingCompletionActions
*/
Hope that helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply