Solved

check job status last 24 hours

Posted on 2014-02-17
7
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 37
Can I skip a node in XML? 9 31
Convert VBA UDF to SQl SERVER UDF 4 48
Need help with another query 10 35
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

735 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