| Author |
Message |
PDinCA
Joined: 25 Jul 2005
Posts: 469
Location: Costa Mesa, CA, USA
|
 Posted: Mon Sep 17, 2012 8:50 pm Post subject: Custom Alert: Need query for Job Executing |
 |
|
The "Job Duration Unusual" Alert is fine when the job actually stops, but there are times when a Job runs interminably.
I need a Custom Metric that tells me when an active Job has been running for more than my (threshold of pain) max "exceptional duration".
For now, I need that threshold to be in hours. If I can make it data-driven by Jobname, that would be ideal! If the query can poll the same target server as the Job is executing on and retrieve a configuration value from one of my existing configuration tables, I can then set up my "Job Still Running" Alert:
When Job <name> has been running for more than <configured hours for Job <name>> Alert me with High Importance.
Can this be done, and if so, could Red Gate share a sample query for "retrieve currently executing Jobs" that I can filter?
This could then be added to the list on the http://sqlmonitormetrics.red-gate.com site for others to use.
In hopes, with thanks, Stephen |
|
| Back to top |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Wed Sep 19, 2012 1:55 pm Post subject: |
|
|
Hi Stephen,
You can try plugging this query into a custom metric against the MSDB database. It will report the number of seconds a job named MyJob has been running. You should then be able to set an alert when the number of seconds has been exceeded. I hope this helps!
| Code: |
/* Get the JobID for MyJob */
DECLARE @JobId UNIQUEIDENTIFIER
SELECT @JobId= job_id FROM msdb..sysjobs WHERE name='MyJob'
/* Define a temp table to hold xp_sqlagent_enum_jobs
This xp will tell us which jobs are running */
DECLARE @ExecutionStatus table
(
JobID uniqueidentifier primary key, -- Job ID
LastRunDate int, LastRunTime int, -- Last run date and time
NextRunDate int, NextRunTime int, -- Next run date and time
NextRunScheduleID int, -- an internal schedule id
RequestedToRun int, RequestSource int, RequestSourceID varchar(128),
running int, -- 0 or 1, 1 means the job is executing
CurrentStep int, -- which step is running
CurrentRetryAttempt int, -- retry attempt
JobState int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
)
INSERT @ExecutionStatus
EXEC xp_sqlagent_enum_jobs 1, '', @JobId
/* Get the number of seconds MyJob has been running, if it is running */
SELECT DATEDIFF(ss,a.start_execution_date,GETDATE()) AS RunningSeconds FROM msdb..sysjobactivity a INNER JOIN @ExecutionStatus s ON a.job_id=s.JobId WHERE s.running=1 |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
PDinCA
Joined: 25 Jul 2005
Posts: 469
Location: Costa Mesa, CA, USA
|
| Posted: Wed Sep 19, 2012 5:48 pm Post subject: |
|
|
Helps IMMENSELY.
Many thanks, Brian.
UPDATE
Sadly, the join criterion on just job_id gets me the list of every execution, so I revised the query, using my own replacement for DATEDIFF() for the hours calc as I want to be precise, not just have it fire after 3hrs 1min if it starts at, say, 12:59... (Hate DATEDIFF!)
Using SELECT TOP 1 ... ORDER BY a.start_execution_date DESC gets me the currently executing Job details.
| Code: |
/* Get the JobID for MyJob */
DECLARE @JobId uniqueidentifier
SELECT @JobId = job_id
FROM msdb..sysjobs
WHERE name = '_Quarter Hourly Update Aggregation'
/* Define a temp table to hold xp_sqlagent_enum_jobs
This xp will tell us which jobs are running */
DECLARE @ExecutionStatus TABLE
( JobID uniqueidentifier PRIMARY KEY -- Job ID
, LastRunDate int
, LastRunTime int -- Last run date and time
, NextRunDate int
, NextRunTime int -- Next run date and time
, NextRunScheduleID int -- an internal schedule id
, RequestedToRun int
, RequestSource int
, RequestSourceID varchar(128)
, running int -- 0 or 1, 1 means the job is executing
, CurrentStep int -- which step is running
, CurrentRetryAttempt int -- retry attempt
, JobState int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
)
INSERT @ExecutionStatus
EXEC xp_sqlagent_enum_jobs 1
, ''
, @JobId
/* Get the number of hours the has been running, if it is running */
SELECT TOP 1
DBA.dbo.udf_GetElapsedHours(a.start_execution_date, GETDATE()) AS RunningHours
FROM msdb..sysjobactivity a
INNER JOIN @ExecutionStatus s
ON s.JobId = a.job_id
WHERE s.running = 1
ORDER BY a.start_execution_date DESC
|
Thanks, though, for your original query - wouldn't be anywhere without it. 
Last edited by PDinCA on Wed Sep 19, 2012 8:22 pm; edited 2 times in total |
|
| Back to top |
|
|
PDinCA
Joined: 25 Jul 2005
Posts: 469
Location: Costa Mesa, CA, USA
|
| Posted: Wed Sep 19, 2012 7:18 pm Post subject: |
|
|
As there's no Uservoice (that I can see), could I register this post as an Enhancement Request?
As it is, I can use the above for ONE Job per Custom Alert, which is fine if that's all one cared about, up to a handful perhaps would be OK, but in environments with many servers and many, many Jobs, I can see this kind of thing being unwieldy in a hurry.
Would RG please consider adding a standard alert "Job Still Running"? It should be reusable by being able to select the job of interest and state the job duration that when exceeded would raise the alert. Alert thresholds could use the standard Low/Medium/High measures for "Duration in excess of the Alert Duration", perhaps.
Thanks in hopes. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Thu Sep 20, 2012 9:09 am Post subject: |
|
|
Hi Stephen,
I'm glad I could help. I'm surprised that you can't just get this information from sysjobhistory, but that table only updates after the job finishes. So you have to use an undocumented xp to get the running jobs and then check the job activity table.
Sql Monitor has a UserVoice site here in case you want to add suggestions:
http://sqlmonitor.uservoice.com/forums/91743-suggestions
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
PDinCA
Joined: 25 Jul 2005
Posts: 469
Location: Costa Mesa, CA, USA
|
|
| Back to top |
|
|
|
