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

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

SQL Server MSDB size checklist

Maintaining the SQL Server msdb size should be a regular DBA task. The exact tasks to complete , depend on how you use SQL server

 A typical question I receive is:

 “The MSDB database has grown till 10 GB and now I'm experiencing no more space on disk C:.”

Note: There are different situations where you won’t want to delete history. Plan ahead – and only apply these procedures in accordance with system policies

Exceute SP_DELETE_BACKUPHISTORY 

A system stored procedure . Maintains the size of backup and restore history tables.

It takes one argument : @oldest_date

Execute SP_PURGE_JOBHISTORY

A system stored procedure. The option of deleting all job history records  or the history of one job

Execute SP_MAINTPLAN_DELETE_LOG

Clears up the Maintenance Plan History . This undocumented store  procedure is the equivalent of the “Delete” button on the Maintenance GUI

Possible scenarios include:

 1: User wants to delete all logs

2: User wants to delete all logs older than X date

3: User wants to delete all logs for a given plan

4: User wants to delete all logs for a specific subplan

5: User wants to delete all logs for a given plan older than X date

6: User wants to delete all logs for a specific subplan older than X date

 Related Posts

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

List failed SQL server Jobs with Powershell

SQL Server Agent Job Steps

SQL Server agent fixed roles

SQL Agent Jobs – Schedule in seconds