January 4, 2008 at 7:29 am
I’m trying to write a script that will detect long running agent jobs.
Having looked at this article:
http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id’s don’t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
January 4, 2008 at 7:50 am
I've used this method since 2K. Updated recently to use database mail and format a nice HTML table 🙂 Just need to change the parameters for sp_send_dbmail.
I sometimes have problems posting scripts w/ the WYSWYG editor on SSC, if you have any problems parsing the script, feel free to e-mail me at tbollhofer2@gmail.com and I'll send it to you.
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' ' +
N'
' +
N' ' +
N' ' +
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' ' ;
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 @sqlscribeViewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply