Monitoring SQL Agent Jobs

In my last post I showed how to reliably get your SQL Agent jobs to only bother you if they fail. But how can you be confident that they are in fact running? Well, what I did is I set up a report in Reporting Services that reads the job history tables on a list of servers and shows me what jobs have run lately and whether they succeeded. I have the report sent to me every morning. Successful jobs are green and failed jobs are red. I get a clear consolidated picture of what’s been going on.

Here’s the query the report uses:

if exists (	select	1 
		from	dbo.sysobjects 
		where	id = object_id(N'dbo.rpt_ITSQLAgentJobs') 
			and objectproperty(id, N'IsProcedure') = 1  )
	drop procedure dbo.rpt_ITSQLAgentJobs
go
 
set quoted_identifier on
go
set ansi_nulls on
go
 
create proc dbo.rpt_ITSQLAgentJobs
(
	@From   datetime
	, @To   datetime
)
as
 
/*
----------------------------------------------------------------------
Version:    1.0
Date:       13/05/2009
----------------------------------------------------------------------
*/
 
set nocount on
 
declare @FromInt int
	, @ToInt int
 
-- In sysjobhistory Run Date is an integer in the form YYYYMMDD.
set @FromInt = cast((convert(varchar(8),@From,112)) as int)
set @ToInt = cast((convert(varchar(8),@To,112)) as int)
 
-- I tried a union but the servers aren't all on the same collation so that doesn't work.
-- The temp table approach gets round that.
 
----------------------------------------------------------------------
-- server1
----------------------------------------------------------------------
  
-- Create the temp table from the first server's results.
select	sjh.run_date
	, sjh.run_time
	, sjh.server
	, sj.name as job_name
	, sjh.step_name
	, sjh.message
	, sjh.run_status
into	#t
from	server1.msdb.dbo.sysjobhistory sjh with (nolock)
	inner join server1.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id
where	sjh.step_id = 0 --(Job outcome)
	and sjh.run_date >= @FromInt
	and sjh.run_date <= @ToInt

---------------------------------------------------------------------- 
-- server2 
---------------------------------------------------------------------- 

begin try 

	insert	#t 
	select	sjh.run_date 
		, sjh.run_time 
		, sjh.server collate Latin1_General_CI_AS 
		, sj.name as job_name 
		, sjh.step_name 
		, sjh.message 
		, sjh.run_status 
	from	server2.dbo.sysjobhistory sjh with (nolock) 
		inner join server2.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id 
	where	sjh.step_id = 0 --(Job outcome) 
		and sjh.run_date >= @FromInt
		and sjh.run_date <= @ToInt
 
end try
 
begin catch
 
	insert	#t
		(run_date
		, run_time
		, [server]
		, job_name
		, step_name
		, [message]
		, run_status)
	select	99999999
		, 0
		, 'server2'
		, ''
		, ''
		, error_message()
		, 0
 
end catch
 
-- Repeat the server2 bit for every other server you want to monitor
 
--Return results
select	*
	, case run_status
		when 0 then 'Failed'
		when 1 then 'Succeeded'
		when 2 then 'Retry'
		when 3 then 'Canceled'
		when 4 then 'In progress'
		else 'Unknown (' + cast(run_status as varchar(3)) + ')'
	  end as status_desc
from	#t
order by run_date desc
	, run_time desc
	, [server]
 
go
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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