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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment