Solved

check job status last 24 hours

Posted on 2014-02-17
7
294 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 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