Find SQL Server job ran on specific date with its status

Wednesday, July 25, 2012 |


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

0 comments: