Typically a DBA manages multiple SQL server instances. It is an important part of DBA’s job to make sure all the jobs are running as intended and scheduled on all servers. With many servers to manage this job is very tedious and time consuming if you have to manually check the jobs on each and every server. It is a management nightmare and every DBA’s Achilles-heel.

This article shows how to generate a comprehensive Job Status Report about all the jobs in a multi-server environment. You can use this report to check the status of all the jobs that are running on all servers with ease and make sure important production jobs run on schedule and have not slipped through cracks.

Job Status

As a DBA or DBA manager you might have to manage the whole SQL server environment or all database environments. There might me multiple instance for you to manage and needless to say there are many SQL Server agent jobs running on all those servers. You might think that all the jobs are running smoothly without any problems. You might have set up an important job that needs to be run once a week. When you happen to check the job, you find that the job has been disabled and has not run for a while. You wish you knew about this before. This is one of the many reasons why you need to the check the job status on all servers periodically.

There could be 10, 25, 50 or even more than 100 instances in you environment. It is needless to say how many jobs are there on each of the servers. For SQL server agent jobs you can set up alerts to e-mail or page whenever jobs fail. But this will send you e-mail alerts only when the jobs fail. You would be able to know whether a job has been disabled or if the schedule has been disabled or not. How do you make sure every backup job has run every single day? What if someone has put the job on hold without your knowledge, especially if there are many DBAs in your team managing the environment? This article shows you how to create such a comprehensive “Job Status Report” that reports about the status of all the jobs on multiple servers that meet the following criteria:

1. Failed jobs
2. Disabled jobs
3. Jobs with no schedule
4. Jobs that have not been run in the past 30 days

Armed with this intelligence you will be able better manage your SQL server environment.

There are multiple ways of monitoring and generating a comprehensive job status reports. Either you can have your own scripts or a third part tool such as Idera’s SQL admin toolset. Especially if you are hard pressed for budget to buy expensive tools, this article gives you the scripts to generate such a good report on your own. It also gives you further information about how you can create more sophisticated report either using Access or SQL server reporting services or SharePoint.

If you are managing a multi-server environment then it is probably a good idea to have one central server to monitor multiple target servers as shown in Fig. 1.

Figure 1

Basically the system stored procedure msdb.dbo.sp_help_job gives you all the information about the jobs on a server.

We will be building ours scripts based on the above stored procedure.

First you should designate one server as the master server where you can create a central database for storing the information from all the servers. Below I present the series of scripts to be run on the master server. First let us assume you have a database for running these scripts; you can call it anything you like, but you need a database. I call mine MONDB.

These scripts work for SQL Server 7.0, 2000 and 2005. The process is explained in the following steps.

Step 1

First you have to create linked server connections on the master server for all your target servers that you want to monitor. For more information on how to create linked servers in SQL 2000, please check the topic “How to set up a linked server (Enterprise Manager)” in SQL Server Books Online or on the web. For help on setting up linked servers in SQL Server 2005 check the topic “Linking Servers” in books online. The following scripts will use the information from sysservers table which contains all the registered link servers in the master database. Please make sure that you can connect to all the linked servers before you proceed.

Step 2

This script creates a table called Job_Status on the master server. You have to substitute your database name for <yourdb>. This script is called 01_create_table_job_status.sql.

/************************Begin Script************************************/Use <yourdb>
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Job_Status]
GO
CREATE TABLE [dbo].[Job_Status] (
            [job_id] [uniqueidentifier] NULL ,
            [originating_server] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [name] [sysname] NOT NULL ,
            [enabled] [tinyint] NULL ,
            [description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [start_step_id] [int] NULL ,
            [category] [sysname] NOT NULL ,
            [owner] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [notify_level_eventlog] [int] NULL ,
            [notify_level_email] [int] NULL ,
            [notify_level_netsend] [int] NULL ,
            [notify_level_page] [int] NULL ,
            [notify_email_operator] [sysname] NOT NULL ,
            [notify_netsend_operator] [sysname] NOT NULL ,
            [notify_page_operator] [sysname] NOT NULL ,
            [delete_level] [int] NULL ,
            [date_created] [datetime] NULL ,
            [date_modified] [datetime] NULL ,
            [version_number] [int] NULL ,
            [last_run_date] [int] NULL ,
            [last_run_time] [int] NULL ,
            [last_run_outcome] [int] NULL ,
            [next_run_date] [int] NULL ,
            [next_run_time] [int] NULL ,
            [next_run_schedule_id] [int] NULL ,
            [current_execution_status] [int] NULL ,
            [current_execution_step] [sysname] NOT NULL ,
            [current_retry_attempt] [int] NULL ,
            [has_step] [int] NULL ,
            [has_schedule] [int] NULL ,
            [has_target] [int] NULL ,
            [type] [int] NULL
) ON [PRIMARY]
GO
/************************End Script************************************/

Step 3

Create the stored procedure called usp_mon_job_status_of_all_servers on the master server. You have to substitute your database name for <yourdb>. The script is called 02_usp_mon_job_status_of_all_servers.sql.

/************************Begin Script************************************/Use <yourdb>
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_mon_job_status_of_all_servers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_mon_job_status_of_all_servers]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE   procedure usp_mon_job_status_of_all_servers
as
begin
declare @sql nvarchar(4000)
declare @return_code int
declare @last_backup_date datetime
declare @server_name sysname
declare servers_cursor cursor for
select srvname from master.dbo.sysservers
where srvproduct = 'SQL Server'
order by srvname
delete from job_status
open servers_cursor
fetch servers_cursor into @server_name
while @@fetch_status = 0
begin
/*
            print '---------------------------------------------------------------------------'
            print 'Server name: ' + @server_name
            print '---------------------------------------------------------------------------'
            print ''
*/            set @sql = ''
            set @sql = 'insert into job_status SELECT * from openquery(['  + @server_name + '], ''set fmtonly off;exec msdb.dbo.sp_help_job'')'
--          print @sql
            print @server_name
            exec sp_executesql @sql
            fetch servers_cursor into @server_name
end
close servers_cursor
deallocate servers_cursor
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/************************End Script************************************/

Step 4

Create the stored procedure called usp_help_job_status on the master server. You have to substitute your database name for <yourdb>. The script is called 03_usp_help_job_status.sql

/************************Begin Script************************************/Use <yourdb>
Go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_help_job_status]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_help_job_status]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  proc  usp_help_job_status
as
begin
declare @sql nvarchar(4000)
declare @return_code int
declare @last_backup_date datetime
declare @server_name sysname
create table #job_status_report
(originating_server nvarchar(60) null,
name sysname null,
owner sysname null,
enabled tinyint null,
has_schedule int,
last_run_outcome int null,
last_run_date char(13) null)
set nocount on
insert into #job_status_report
select
originating_server, name, owner, enabled, has_schedule, last_run_outcome, last_run_date = convert(char(13), last_run_date)
from mondb.dbo.job_status
where enabled = 0
or last_run_date < convert (int, replicate ('0', 4 - datalength(rtrim(convert(char(4), datepart(yyyy, getdate() - 30))))) + rtrim(convert(char(4), datepart(yyyy, getdate() - 30))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(mm, getdate() - 30))))) + rtrim(convert(char(2), datepart(mm, getdate() - 30))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(dd, getdate() - 30))))) + rtrim(convert(char(2), datepart(dd, getdate() - 30))))
or last_run_date is null
or has_schedule = 0
or last_run_outcome = 0
order by originating_server
-- Put a * next to the jobs that have not been run in the past 30 days
update #job_status_report
set last_run_date = rtrim(last_run_date) + ' *'
where convert(int, last_run_date) < convert (int, replicate ('0', 4 - datalength(rtrim(convert(char(4), datepart(yyyy, getdate() - 30))))) + rtrim(convert(char(4), datepart(yyyy, getdate() - 30))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(mm, getdate() - 30))))) + rtrim(convert(char(2), datepart(mm, getdate() - 30))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(dd, getdate() - 30))))) + rtrim(convert(char(2), datepart(dd, getdate() - 30))))
declare servers_cursor cursor for
select distinct originating_server
from #job_status_report
set nocount off
/*
select distinct originating_server
from mondb.dbo.job_status
where enabled = 0
or last_run_date < convert (int, replicate ('0', 4 - datalength(rtrim(convert(char(4), datepart(yyyy, getdate() - 15))))) + rtrim(convert(char(4), datepart(yyyy, getdate() - 15))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(mm, getdate() - 15))))) + rtrim(convert(char(2), datepart(mm, getdate() - 15))) +
replicate ('0', 2 - datalength(rtrim(convert(char(2), datepart(dd, getdate() - 15))))) + rtrim(convert(char(2), datepart(dd, getdate() - 15))))
or has_schedule = 0
or last_run_outcome = 0
order by originating_server
*/print ''
print '--------------------------------------------------------------------------------------'
print 'Job status report, for jobs that did not meet the following criteria, for all servers:'
print '1. Failed Jobs'
print '2. Disabled Jobs'
print '3. Jobs that have no schedule'
print '4. Jobs that have not been run in the past 30 days'
print '--------------------------------------------------------------------------------------'
print ''
open servers_cursor
fetch servers_cursor into @server_name
while @@fetch_status = 0
begin
            print '==========================================================================='
            print 'Server Name: ' + @server_name
            print '==========================================================================='
            print ''
            select job_name = convert(varchar(50), name), owner = convert(varchar(15), owner),
            enabled = case (enabled)
            when 0 then 'No *'
            else 'Yes'
            end,
            has_schedule = case (has_schedule)
            when 0 then 'No *'
            else 'Yes'
            end,
            last_run_outcome = case (last_run_outcome)
            when 0 then 'Failed *'
            when 1 then 'Succeeded'
            else 'Unknown *'
            end,
            last_run_date
            from #job_status_report
            where originating_server = @server_name
            order by owner, enabled, has_schedule, last_run_outcome, last_run_date
            fetch servers_cursor into @server_name
end
close servers_cursor
deallocate servers_cursor
drop table #job_status_report
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/************************End Script************************************/

Step 5

Create a job that executes the procedure created in Step 3 on the master server. You can also create a schedule that runs this job daily on the master server. For more information on how to create jobs, job steps and schedules, please refer to the topic “Implementing Jobs” on SQL Server Books Online (BOL).

That’s it. You are all set. Now every day you just have to connect to the master server using Query Analyzer or SQL Server Management Studio, open a query window and run the proc usp_help_job_status as shown below.

-- Replace your database name for yourdb

Use <yourdb>
Go
Exec usp_help_job_status

And it will show you a report similar to the following:

--------------------------------------------------------------------------------------

Job status report, for jobs that did not meet the following criteria, for all servers:

1. Failed Jobs

2. Disabled Jobs

3. Jobs that have no schedule

4. Jobs that have not been run in the past 30 days

--------------------------------------------------------------------------------------

=========================================================================

Server Name: SQL2005

=========================================================================

job_name owner enabled has_schedule last_run_outcome last_run_date

-------------------------------------------------- --------------- ------- ------------ ---------------- -------------

Backup All DBs.Backup All DBs sa No * Yes Succeeded 20080514 *

Testjob sa Yes No * Failed * 20081106 *

DBCC Check DB.Subplan_1 sa Yes Yes Failed * 20090801

(3 row(s) affected)

=========================================================================Server Name: SQL2000

=========================================================================

job_name owner enabled has_schedule last_run_outcome last_run_date

-------------------------------------------------- --------------- ------- ------------ ---------------- -------------

Full backup Corp\User1 Yes No * Succeeded 20090322 *

Optimizations Job for DB Maintenance Plan 'Weekly sa No * Yes Failed * 20060108 *

Transaction Log Backup Job for DB Maintenance Plan sa No * Yes Failed * 20090111 *

Mon_Disk_Space sa No * Yes Succeeded 20060726 *

Truncate and Shrink DB1 Log sa Yes No * Succeeded 20080118 *

DBCC Updateusage DB1 sa Yes Yes Succeeded 20090107 *

(12 row(s) affected)

You can go a little further by creating report using this stored procedure in Access for a nice formatted report or create a report using SQL Server Reporting Services server so that other can view the report or even make the report from the Reporting Server available on your SharePoint portal.

You can contact the author at siva@cubeinfosolutions.com. Siva Mahalingam is the President of Cube Info Solutions. Cube Info Solutions provides database consulting and other IT Solutions. For more info please visit www.cubeinfosoultions.com.