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.
Here is the query which will return the desired result:
[sourcecode language=”sql”]USE MSDB
DECLARE @Today AS VARCHAR(8)
SET @Today = CONVERT(VARCHAR(8),GETDATE(),112)
SELECT * FROM (
CASE WHEN JobHist.run_status =1
WHEN JobHist.run_status =0
END AS JobRunStatus,
JobHist.run_duration AS RunDuration,
JobStep.command,ROW_NUMBER() OVER(PARTITION BY SysJob.name,JobStep.step_id ORDER BY run_time DESC) AS NumberOfExecution
dbo.sysjobhistory AS jobHist
dbo.sysjobs AS SysJob
JobHist.job_id = SysJob.job_id
dbo.sysjobsteps AS JobStep
(JobStep.job_id = SysJob.job_id)
JobHist.run_date = @Today
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
Reference: Ritesh Shah
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