Find SQL Server agent 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:

[sourcecode language=”sql”]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[/sourcecode]

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Find SQL Server agent job ran on specific date with its status”

Comments are closed.