SQL Server – SQL Server Agent – Job Status

Background

Quickly trying to get status of our SQL Server Agent Jobs.

Script

dbo.sp_listSQLServerAgentJobStatus

Stored Procedure


use master
go

if object_id('dbo.sp_listSQLServerAgentJobStatus') is null
begin


	exec('create procedure dbo.sp_listSQLServerAgentJobStatus as ')
end
go

alter procedure [dbo].[sp_listSQLServerAgentJobStatus]
(
	   @jobCategory    sysname = null
	,  @jobSubCategory sysname = null
	,  @jobName		   sysname = null	
) 
as
begin
		
	set nocount on;
	SET XACT_ABORT ON;
	set transaction isolation level read uncommitted;
  
	 declare @tblJobAggr TABLE
	 (
		  [id]				int not null identity(1,1)
  		, [job_id]			uniqueidentifier  not null
		, [step_id]			int not null
		, [instance_id]		int not null

		, primary key
  			(
				  [job_id]
				, [step_id]
			)
	 )

	declare @tblJobStep TABLE
	(
		   [id]        int not null identity(1,1)
		,  [stepname]  sysname
		,  [primary]   bit
		,  [secondary] bit
	 )

	 declare @CHAR_WILDCARD		     char(1)

	 declare @jobCategoryLogShipping sysname

	 declare @jobSubCategoryWildcard sysname

	 declare @jobNameWildcard		 sysname

	 declare @tblResult TABLE
	 (

		  [id] int not null identity(1,1)

		, [sqlInstance] sysname null

		, [JobName]		sysname not null

		, [Category]	sysname not null

		, [stepID]		int    not null

		, [StepName]	varchar(4000) not null

		, [runStatus]	varchar(4000) null

		, [runStart]			datetime    null

		, [runDurationAsHHMMSS] varchar(4000) null

		, [sqlMessageID]	    varchar(4000) null	

		, [sqlSeverity]		    int	

		, [message]				varchar(8000) null

		, [outputFileName]		varchar(8000) null

	)

	set @CHAR_WILDCARD = '%'

	set @jobCategoryLogShipping = 'Log Shipping'

	 if (@jobSubCategory is not null)
	 begin

		 set @jobSubCategoryWildcard = 
				@CHAR_WILDCARD 
					+ @jobSubCategory 
					+ @CHAR_WILDCARD

	end

	if (@jobName is not null)
	begin

		set @jobNameWildcard = 
					  @CHAR_WILDCARD
					+ @jobName 
					+ @CHAR_WILDCARD

	end
						
	insert into @tblJobStep
	(
		   [stepname]
		,  [primary] 
		,  [secondary]
	)
	select 
		  'Log shipping backup log job step.'
		, 1
		, 0
	union
	select 
		  'Log shipping copy job step.'
		, 0
		, 1
	union
	select 
		  'Log shipping restore log job step.'
		, 0
		, 1


	 ; with cteInstance
	 (
 		  [job_id]
		, [step_id]
		, [instance_id]
	)
	 as
	 (
 		SELECT 
				  job_id
					= DBSysJobHistory.job_id

				, [step_id]
					= DBSysJobHistory.step_id

				, [instance_id]
					= MAX(DBSysJobHistory.instance_id) --as instance_id

		FROM msdb.dbo.sysjobhistory DBSysJobHistory

		GROUP BY
				  DBSysJobHistory.job_id
				, DBSysJobHistory.step_id
	)
	insert into @tblJobAggr
	select *
	from   cteInstance

	 /*
		http://stackoverflow.com/questions/10577676/how-to-obtain-failed-jobs-from-sql-server-agent-through-script
	 */
 
	insert into @tblResult
	 (

		  [sqlInstance]
		, [JobName]		
		, [Category]	
		, [stepID]		
		, [StepName]
		, [runStatus]
		, [runStart]		
		, [runDurationAsHHMMSS] 
		, [sqlMessageID]	
		, [sqlSeverity]		
		, [message]				
		, [outputFileName]

	)

	 SELECT  

			 [sqlInstance]
				=  DBSysJobHistory.[server]

			, [JobName]
				= SysJobs.[name]

			, [Category]
				=SYSJOBCAT.[name]

			, [stepID]
				= DBSysJobHistory.step_id

			, [StepName]
				= SysJobSteps.step_name

			/*
			, [runStatusID]
				= DBSysJobHistory.run_status
			*/

			, [runStatus]
				= case
						when DBSysJobHistory.run_status = 0 then 'Failed'
						when DBSysJobHistory.run_status = 1 then 'Succeeded'
						when DBSysJobHistory.run_status = 2 then 'Retry'
						when DBSysJobHistory.run_status = 3 then 'Canceled'
						else 'Indeterminate'
				  end

			--,DBSysJobHistory.run_date

			--,DBSysJobHistory.run_time

			,[runStart]
					=
						convert
						(
							  varchar(30)
							, [msdb].dbo.agent_datetime
								( 
									  DBSysJobHistory.run_date
									, DBSysJobHistory.run_time
								)
							, 101

						)

			,[runDurationAsHHMMSS]
				= DBSysJobHistory.run_duration

			, [sqlMessageID]
				= DBSysJobHistory.sql_message_id

			,[sqlSeverity]
			   = DBSysJobHistory.sql_severity

			,[message]
				= DBSysJobHistory.[message]

			,[outputFileName]
				= SysJobSteps.output_file_name

		FROM msdb.dbo.sysjobhistory DBSysJobHistory

		JOIN msdb.dbo.sysjobs SysJobs
		   ON (DBSysJobHistory.job_id = SysJobs.job_id)

		JOIN msdb.dbo.sysjobsteps SysJobSteps
		   ON (
					( DBSysJobHistory.job_id = SysJobSteps.job_id )
				AND ( DBSysJobHistory.step_id = SysJobSteps.step_id )
			  )

		LEFT OUTER JOIN msdb.dbo.syscategories SYSJOBCAT
			ON SysJobs.category_id = SYSJOBCAT.category_id

		INNER JOIN @tblJobAggr  AS Instance 
			ON DBSysJobHistory.instance_id = Instance.instance_id

		/*
			If Category filtering is enabled
		*/
		if ( @jobCategory is not null)
		begin

			delete
			from  @tblResult
			where ( [Category] != @jobCategory )

		end

		/*
			If Sub Category filtering is enabled
		*/
		if ( @jobSubCategory is not null)
		begin

			delete
			from  @tblResult
			where ( [stepName] not like @jobSubCategoryWildcard )

		end

		/*
			If Sub Category filtering is enabled
		*/
		if ( @jobName is not null)
		begin

			delete
			from  @tblResult
			where ( [jobName] not like @jobNameWildcard )

		end

		select 
			  [RowNumber]
				= ROW_NUMBER() 
						OVER
						(	
							ORDER BY
									   [SQLInstance]
									,  [JobName]
									,  StepID
						)

			, [sqlInstance]
			, [JobName]		
			, [Category]	
			, [stepID]		
			, [StepName]
			, [runStatus]
			, [runStart]		
			, [runDurationAsHHMMSS] 
			, [sqlMessageID]	
			, [sqlSeverity]		
			, [message]				
			, [outputFileName]

		from   @tblResult

		Order by
				   [SQLInstance]
				,  [JobName]
				,  StepID
		
end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_listSQLServerAgentJobStatus]'
go

grant execute on [dbo].[sp_listSQLServerAgentJobStatus] to [public]
go



Sample

Sample – Log Shipping

Code

use master
go

declare @jobCategory sysname
declare @primary     bit

set @jobCategory = 'Log Shipping'
set @primary     = null

exec [dbo].[sp_listSQLServerAgentJobStatus] 
      @jobCategory = @jobCategory
	, @primary     = @primary

Output

logshipping-20160923-1256pm

 

Source Control

GitHub

Here is the URL for the file

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s