Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

check job status last 24 hours

Posted on 2014-02-17
7
Medium Priority
?
303 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
ID: 39864271
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
ID: 39864701
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
ID: 39864834
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

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

Expert Comment

by:Carl Tawn
ID: 39864959
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
ID: 39865058
Good one!  I was not familiar with that function.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39865248
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 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