sp_FailedJobs

If your job description has DBA anything in the title then you likely are in a position to care if any SQL Agent jobs are in a failed state. Along with a cup of joe, checking a monitoring tool or the SQL Agent Activity Monitor is a regular part of a healthy DBA breakfast. To cut out inefficiencies, I like to keep as much of my routine in SSMS as possible and a quick shortcut for what’s failed across my servers fits that bill.

Waking up to failed jobs is never fun but c'est la vie

sp_FailedJobs to the rescue. This handy dandy stored proc will give you the listing for all SQL Agent jobs that are currently in a failed state for all your follow-up needs. And if you’re hip to the trick that SSMS allows you to query multiple servers at the same time, it’s all that more powerful a tool for staying on top of your organizations data health. It’s a healthy part of your DBA morning routine. Enjoy!

[thrive_lead_lock id=’660′]

USE [master]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.sp_FailedJobs') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
     DROP PROC dbo.sp_FailedJobs   
GO
/*=========================================================================================================
	Procedure:  sp_FailedJobs 
 ==========================================================================================================
 DESCRIPTION: Quick review of SQL Agent jobs that have failed on most recent run.  
  
For more insight visit:  https://jamesbond.consulting/sp_FailedJobs

PROVIDED BY:
James Bond Consulting (JBC)
Written by: James Bond
(c) 2019, https://jamesbond.consulting. All rights reserved.
email: [email protected]

You may alter this script (the "Software") for your own in-house purposes. You may also republish or blog 
code including without limitation the rights to use, copy, modify, merge, distribute, sublicense, and/or 
sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject 
to the following conditions:

§ The above copyright notice and this permission notice shall be included in all copies or substantial 
portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

=============================================================================================================*/ 
CREATE PROCEDURE [dbo].[sp_FailedJobs] 
@show_disabled BIT = 0
    
AS  
  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
 
;WITH jobs AS (
SELECT 
	 j.job_id
	,j.[name]
    ,js.step_name
    ,jh.sql_severity
    ,jh.[message]
    ,jh.run_date
    ,jh.run_time
    ,jsc.next_run_date
    ,jsc.next_run_time
	,jh.run_status
	,js.last_run_outcome
	,ROW_NUMBER() OVER (PARTITION BY j.[name], js.step_name ORDER BY run_date DESC,run_time DESC) as RowN
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
INNER JOIN msdb.dbo.sysjobschedules jsc
   ON j.job_id = jsc.job_id )

SELECT 
	 j.[name] AS job_name
    ,step_name
    ,sql_severity
    ,[message]
	, CASE WHEN CONVERT(CHAR,run_time) != '0' THEN CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 
		+((run_time - (run_time/10000) * 10000)/100) * 60 
		+ (run_time - (run_time/100) * 100), 
		CONVERT(DATETIME,RTRIM(run_date),113)),100) ELSE CONVERT(CHAR,run_time) END AS run_time
	, CASE WHEN CONVERT(CHAR,next_run_time) != '0' THEN CONVERT(VARCHAR,DATEADD(S,(next_run_time/10000)*60*60 
		+((next_run_time - (next_run_time/10000) * 10000)/100) * 60 
		+ (next_run_time - (next_run_time/100) * 100), 
		CONVERT(DATETIME,RTRIM(next_run_date),113)),100) ELSE CONVERT(CHAR,next_run_time) END AS next_run_time
	,CASE WHEN [enabled] = 1 THEN 'Enabled' 
		ELSE 'Disabled' END as [enabled]
FROM jobs j  
INNER JOIN msdb.dbo.sysjobs sj 
	ON j.job_id = sj.job_id 
WHERE RowN = 1 
	AND (j.run_status = 0 OR j.last_run_outcome != 1) --only show where last status is failed or one of the steps failed
	AND (@show_disabled = 1 OR [enabled] = 1)
ORDER BY 
	j.[name],run_date DESC,run_time DESC

GO

[/thrive_lead_lock]