Solved

check job status last 24 hours

Posted on 2014-02-17
7
273 Views
Last Modified: 2014-02-20
Hello,

I want to modify this query for checking the jobs status last 24 hours :

set nocount on; select distinct replace (j.name, ' ', '')  +'|'+ case h.run_status when 0 then 'Failed' when 1 then 'Successful'  when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.step_id = 1 and h.run_date =  (select max(hi.run_date) from msdb..sysjobhistory hi where h.job_id = hi.job_id) and h.run_time = (select max(hj.run_time) from msdb..sysjobhistory hj where h.job_id = hj.job_id) order by 1

Open in new window


Thanks
0
Comment
Question by:bibi92
7 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Try:
set nocount on; 
select distinct replace (j.name, ' ', '')  +'|'+ case h.run_status when 0 then 'Failed' when 1 then 'Successful'  when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus 
from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.step_id = 1 and h.run_date >= dateadd(hh, -24, getdate())
order by 1

Open in new window

0
 

Author Comment

by:bibi92
Comment Utility
Hello,

The following error is returned :

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Can you post exactly what you are running and what SQL version you are running it on? The query is correct, so should work ok.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
run_date is an integer not a datetime data type.
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
I guess SSMS must be a bit more forgiving then :)

Try this instead:
select distinct replace (j.name, ' ', '')  +'|'+ case h.run_status when 0 then 'Failed' when 1 then 'Successful'  when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus 
from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.step_id = 1 and msdb.dbo.agent_datetime(h.run_date, h.run_time) >= dateadd(hh, -24, getdate())
order by 1

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Good one!  I was not familiar with that function.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
You never want to modify a table column data type when you can modify static data instead, as changing the table column will force scans; if a seek would have been available, performance can be hurt drastically.

Therefore, we should convert the current datetime to int formats (or varchar, which SQL will implicitly CAST to int for us)  for comparison to int columns "run_date" and "run_time", like so:

select distinct
    replace (j.name, ' ', '')  +'|'+ case h.run_status
        when 0 then 'Failed'
        when 1 then 'Successful'
        when 3 then 'Cancelled'
        when 4 then 'In Progress' end as JobStatus
from msdb..sysjobhistory h, msdb..sysjobs j
where
    j.job_id = h.job_id and
    h.step_id = 1 and
    (h.run_date = convert(char(8), getdate(), 112) or --today
     (h.run_date = convert(char(8), dateadd(day, -1, getdate()), 112) and --yesterday...
      h.run_time >=  replace(convert(varchar(8), getdate(), 108), ':', ''))) --...from current time forward
order by 1

Currently there are no indexes on run_date and run_time for sysjobhistory, but this is still the proper technique to follow to allow the best performance for queries in the future.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now