January 24, 2010 at 11:38 am
One of our sql agent job is getting hung somtime and keep on running and taking up all the server resources , for temporary mitigation befor the code is fixed we want to monitor this job and if it is taking > xx minutes we get alert.
could you pls advice the way to achieve this?
January 24, 2010 at 4:39 pm
Hi there,
The only time i needed to do something similar i've include in the job steps some monitorization actions, to determine how long the step was executing, and in the event of the step is taking longer then expected i finished the job.
I dont't know of any feature that allows this to be done in an automatic manner.
José Cruz
January 25, 2010 at 9:41 am
sqlquery-101401 (1/24/2010)
One of our sql agent job is getting hung somtime and keep on running and taking up all the server resources , for temporary mitigation befor the code is fixed we want to monitor this job and if it is taking > xx minutes we get alert.could you pls advice the way to achieve this?
You could use the script below to identiify long running jobs based on history; with a few modifications you could use this to achieve an alert notification based on X minutes....
DECLARE @sql nvarchar(MAX)
SET @sql='SELECT @@ServerName AS ServerName,
A.name AS JobName
FROM
(SELECT job_id,
name ,
SUBSTRING(CONVERT(VARCHAR(8),next_run_date),5,2)+''/''+SUBSTRING(CONVERT(VARCHAR(8),next_run_date),7,2)+''/''+SUBSTRING(CONVERT(VARCHAR(8),next_run_date),1,4)+ '' ''+ SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),next_run_time),6),1,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),next_run_time),6),3,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),next_run_time),6),5,2) next_run
FROM
(SELECT j.job_id,
j.name,
s.next_run_date,
s.next_run_time
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobschedules S
ON J.job_id = S.job_id
WHERE J.enabled = 1
AND S.next_run_date <> 0
AND J.category_id NOT IN (10,11,12,13,14,15,16,17,18,19,20)
)
B
)
A
JOIN
(SELECT job_id ,
CONVERT(INT,AVG(run_duration)+(stdev(run_duration)*2)+300)checkrun,
MAX(SUBSTRING(CONVERT(VARCHAR(8),run_date),5,2)+''/''+SUBSTRING(CONVERT(VARCHAR(8),run_date),7,2)+''/''+SUBSTRING(CONVERT(VARCHAR(8),run_date),1,4)+ '' ''+ SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),run_time),6),1,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),run_time),6),3,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(VARCHAR(6),run_time),6),5,2)) last_run
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
GROUP BY job_id
)
X
ON X.job_id = A.job_id
WHERE DATEADD(SECOND,X.checkrun,A.next_run) < GETDATE()
AND A.next_run > X.last_run
ORDER BY name'
EXEC sp_executesql @sql
IF @@RowCount > 0
BEGIN
DECLARE @TableHTML nvarchar(MAX)
SET @tableHTML =
N'<STYLE TYPE="text/css">TD{font-family: calibri; font-size: 10pt;}</STYLE>' +
N'<b><font face="calibri" size="2">MSSQL Long Running Jobs Report</font></b>
' +
N'<table border="1" cellpadding="2" cellspacing="2" border="1">' +
N'<tr><th><font face="calibri" size="2">Server Name</font></th><th><font face="calibri" size="2">Job Name</font></th>' +
CAST ( ( SELECT td = @@ServerName,'',
td = A.Name,''
FROM
(SELECT job_id,
name ,
SUBSTRING(CONVERT(VARCHAR(8),next_run_date),5,2)+'/'+SUBSTRING(CONVERT(VARCHAR(8),next_run_date),7,2)+'/'+SUBSTRING(CONVERT(VARCHAR(8),next_run_date),1,4)+ ' '+ SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),next_run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),next_run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),next_run_time),6),5,2) next_run
FROM
(SELECT j.job_id,
j.name,
s.next_run_date,
s.next_run_time
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobschedules S
ON J.job_id = S.job_id
WHERE J.enabled = 1
AND S.next_run_date <> 0
AND J.category_id NOT IN (10,11,12,13,14,15,16,17,18,19,20)
)
B
)
A
JOIN
(SELECT job_id ,
CONVERT(INT,AVG(run_duration)+(stdev(run_duration)*2)+300)checkrun,
MAX(SUBSTRING(CONVERT(VARCHAR(8),run_date),5,2)+'/'+SUBSTRING(CONVERT(VARCHAR(8),run_date),7,2)+'/'+SUBSTRING(CONVERT(VARCHAR(8),run_date),1,4)+ ' '+ SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(VARCHAR(6),run_time),6),5,2)) last_run
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
GROUP BY job_id
)
X
ON X.job_id = A.job_id
WHERE DATEADD(SECOND,X.checkrun,A.next_run) < GETDATE()
AND A.next_run > X.last_run
ORDER BY name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='thomas_bollhofer@symantec.com',@subject='MSSQL Long Running Jobs Notification',@body = @tableHTML,@body_format='HTML',@importance='High'--,@profile_name='DBA'
END
Tommy
Follow @sqlscribeJanuary 25, 2010 at 2:37 pm
Thanks for the script. When i cut and paste the first SQL
(SELECT @@ServerName AS ServerName, ...
...
...
WHERE DATEADD(SECOND,X.checkrun,A.next_run) < GETDATE()
AND A.next_run > X.last_run
ORDER BY name
)
it gives the following error:
Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'B'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ''.
Did i miss something obvious?
January 25, 2010 at 6:19 pm
gk-411903 (1/25/2010)
Thanks for the script. When i cut and paste the first SQL(SELECT @@ServerName AS ServerName, ...
...
...
WHERE DATEADD(SECOND,X.checkrun,A.next_run) < GETDATE()
AND A.next_run > X.last_run
ORDER BY name
)
it gives the following error:
Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'B'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ''.
Did i miss something obvious?
It most likely got cut off in the WYSWYG editor 🙂 PM me offline w/ your e-mail and I'll send you a copy.
Tommy
Follow @sqlscribeJanuary 26, 2010 at 10:09 am
I am also looking for correct script , could you please post it?
January 26, 2010 at 10:32 am
sqlquery-101401 (1/26/2010)
I am also looking for correct script , could you please post it?
PM me offline w/ your e-mail and I'll send it - the WYSWYG editor on SSC appears to be stripping out portions of the script.
Tommy
Follow @sqlscribeJanuary 27, 2010 at 7:39 pm
Still looking for script , sent PM and Email couple of times, appreciate if you can get me the correct one...
Thanks!!
January 28, 2010 at 7:38 am
sqlquery-101401 (1/27/2010)
Still looking for script , sent PM and Email couple of times, appreciate if you can get me the correct one...Thanks!!
Apologies - I haven't seen a PM from you - I just sent you one w/ my email address. Shoot me an email this morning and I'll send the script your way.
Thanks -
Tommy
Follow @sqlscribeViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply