Solved

check job status last 24 hours

Posted on 2014-02-17
7
281 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access sql to sql server express 10 32
Sql Stored Procedure 65 26
SSRS 2013 - Creating a summarized report 19 32
T-SQL: "HAVING CASE" Clause 1 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

785 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