Get latest running job from sysJobHistory in SQL Server 2005/2008

We might have several job running in our servers and we can find its stat from its log file but it would be interesting to query job history table. Basically all details about JOBS reside in MSDB database in SQL Server and you can query to SysJobHistory to get information about jobs. You can have so many details about JOB in SysJobHistory table like Job run date, time, job name, message about whether it was successful or not etc. I often need to know what JOBs has been ran in last 24 or 48 hours, whether it was successful or not etc. Let us see that small yet useful T-SQL.

select * from msdb..sysjobhistory

If you want to look at jobs why ran in last one or two days, you can query date like this:

select *

from msdb..sysjobhistory

where (  run_date = convert(varchar(8), getdate(), 112)

     and run_time < replace(convert(varchar(10), getdate(), 8), ':', '')

      )

   or (  run_date = convert(varchar(8), getdate() - 1, 112)

     and run_time >= replace(convert(varchar(10), getdate(), 8), ':', '')

      )

order by run_date, run_time

Actually date and time both are stored in separate INT column so that we have to convert it in proper format and have to compare it by using string functions.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com