Wednesday, September 16, 2009

Query to findout JOBs information for a period of time

I hope this requirement is very common in DBA work life, as DBA needs to findout how the JOBs are performing currently and for a long time. I too faced the requirement. I have searched most of the pages from google and I couldn't found the solution. Finally I written the following query spending almost 3 hours.

Hopefully it will useful for all SQL Server DBAs.


Syntax :
Select b.Name JobName, a.step_name,a.run_date,run_time,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End run_time,
run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Duration], a.message
from sysjobs b inner join sysjobhistory a on a.job_id = b.job_id
where b.name like '%Reindex%' AND step_name not like 'Notification'
and step_name not like '%Job Outcome%' order by b.Name


For the above query you can add filter conditions as per your requirements. By getting the result of above query you can have an idea of how the jobs are involved and making the analysis you can increase the performance of the server.

No comments: