SQL Server - Get datetime values with agent_datetime function and msdb..sysjobhistory

05 July,2012 by Jack Vamvas

Question:  How can I programmatically return  SQL Agent jobs which failed in the last 24 hrs.   I was running the  following code and it didn’t return the results as expected ?

This job is part of some automated DBA tasks 

 SELECT * 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4)
DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24

 

Answer: The code above will return failed jobs via the  WHERE run_status NOT IN (1,4)

, but not for the timeframe you expect. The reason: The code doesn’t account for sjh.run_date being an INT.     The CONVERT is not having the desired result.

Use the msdb.dbo.agent_datetime function. The msdb.dbo.agent_datetime function converts run_date &  run_time into datetime values.  This function  allows for much cleaner code , and makes you wonder why the datatype was never a datetime originally. 

This will return the desired result:

SELECT * 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4) AND
DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time), GETDATE()) <= 24

 Related Posts

 List failed SQL server Jobs with Powershell

SQL Server Agent Job Steps

SQL Server agent fixed roles

SQL Agent Jobs – Schedule in seconds



Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Get datetime values with agent_datetime function and msdb..sysjobhistory


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer