SQL Job statistics - last 7 days - How to...


To identify the SQL Jobs' statistics for last 7 days...
- How many times the JOB executed
- How many success
- How many failure
- Average Time taken
- Average retried count,.... Etc


SELECT
[Job Name],
[Job ID],
[JOB Owner],
CASE WHEN [Schedule] <=0 THEN 'Not Scheduled' ELSE 'Scheduled' END [Executed Status],
[Steps] [How many Steps],
[Total] [Totally Executed],
[Failure] [How many failure(s)],
[Total]-[Failure] [How many success],
[Average] [Average Time Taken(Secs)],
[Retries] [Average retried(Count)]
FROM
(
SELECT TOP 100
sj.name [Job Name]
, sj.job_id [Job ID]
, sl.name [JOB Owner]
, (SELECT COUNT(1) from msdb.dbo.sysjobschedules js1 WHERE js1.job_id = sj.job_id ) [Schedule]
, (SELECT COUNT(1) from msdb.dbo.sysjobsteps js2 WHERE js2.job_id = sj.job_id ) [Steps]
, (SELECT COUNT(1) from msdb.dbo.sysjobhistory jh1 WHERE jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh1.run_date) ), GETDATE()) < 7 ) [Failure] 
,(SELECT AVG(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from msdb.dbo.sysjobhistory jh2 WHERE jh2.job_id=sj.job_id and jh2.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh2.run_date) ), GETDATE()) < 7 ) [Average] 
,(SELECT CAST(AVG(retries_attempted+0.0) AS INT) from msdb.dbo.sysjobhistory jh2 WHERE jh2.job_id=sj.job_id and jh2.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh2.run_date) ), GETDATE()) < 7 ) [Retries] 
,COUNT(1) [Total]
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs sj ON ( jh.job_id = sj.job_id )
INNER JOIN sys.syslogins sl ON ( sl.[sid] = sj.owner_sid )
WHERE jh.step_id = 0 and DATEDIFF( DAY, CONVERT(DATETIME, CONVERT( VARCHAR, jh.run_date) ), GETDATE()) < 7 GROUP BY sj.job_id, sj.name, sl.name
ORDER BY [Average] DESC
) JOBInfo

No comments:

Post a Comment