Find SQL Server job ran on specific date with its status

It is very mandatory for SQL Server DBA to keep an eye on the SQL Server Job and take an immediate action if job gets failed. There are several ways of doing this. I will be showing one query which will return all jobs ran on the specific date along with its status like whether it was failed or succeed.

In order to gain information regarding JOBs, we have to query following list of system tables in MSDB database because all SQL Server jobs are stored in MSDB database only.

1.)     Sysjobhistory

2.)    Sysjobs

3.)    sysjobsteps

Here is the query which will return the desired result:

USE MSDB

GO

DECLARE @Today AS VARCHAR(8)

SET @Today = CONVERT(VARCHAR(8),GETDATE(),112)

SELECT * FROM (

      SELECT

           

            SysJob.name,

            CASE WHEN  JobHist.run_status =1

                  THEN 'Success'

                  WHEN JobHist.run_status =0

                  THEN 'Fail'

            END AS JobRunStatus,

            JobHist.run_date,

            JobHist.run_time,

            JobHist.run_duration AS RunDuration,

            JobStep.step_id,

            JobStep.command,ROW_NUMBER() OVER(PARTITION BY SysJob.name,JobStep.step_id ORDER BY run_time DESC) AS NumberOfExecution

      FROM

            dbo.sysjobhistory AS jobHist

      INNER JOIN

            dbo.sysjobs AS SysJob

      ON

            JobHist.job_id = SysJob.job_id

      INNER JOIN

            dbo.sysjobsteps AS JobStep

      ON

            (JobStep.job_id = SysJob.job_id)

      WHERE

            JobHist.run_date = @Today

)

AS T

WHERE

      NumberOfExecution=1

As a DBA, I keep this script handy as I never know when I will need this. We already have monitoring over each and every jobs and failure of any jobs will be notified to me via email though this has become time saving for me so many times.

One tip I would like to give especially when I am talking about JOB is, keep regular backup policy for MSDB database too as all the JOBs are being stored here and while restoring SQL Server from any disaster, we need to create all the JOBs again and MSDB database backup become life savior in that case.

I have explained the importance of backup in one of my past article, if you are interested to read, click here.

Happy Scripting!!!!

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

Ask me any SQL Server related question at my “ASK Profile”