As a DBA, managing multiple SQL Server instances can be a challenging task. It is crucial to ensure that all jobs are running as intended and scheduled on all servers. However, manually checking the jobs on each server can be tedious and time-consuming. This article will guide you on how to generate a comprehensive Job Status Report for all jobs in a multi-server environment.

Having a Job Status Report allows you to easily check the status of all jobs running on multiple servers. This report helps you ensure that important production jobs run on schedule and have not slipped through the cracks. It provides valuable insights into the status of jobs, such as failed jobs, disabled jobs, jobs with no schedule, and jobs that have not been run in the past 30 days.

There are multiple ways to monitor and generate a comprehensive job status report. You can use your own scripts or third-party tools like Idera’s SQL admin toolset. In this article, we will focus on creating the report using scripts.

To begin, designate one server as the master server where you can create a central database for storing information from all the servers. You will need to create linked server connections on the master server for all the target servers you want to monitor. This allows you to gather information about the jobs on each server using the system stored procedure msdb.dbo.sp_help_job.

Next, create a table called Job_Status on the master server to store the job information. You can use the provided script to create this table.

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]

After creating the table, you can create a stored procedure called usp_mon_job_status_of_all_servers on the master server. This procedure retrieves job information from all the linked servers and inserts it into the Job_Status table. You can use the provided script to create this stored procedure.

Once the stored procedure is created, you can create another stored procedure called usp_help_job_status on the master server. This procedure generates the Job Status Report by querying the Job_Status table. It filters the jobs based on specific criteria, such as failed jobs, disabled jobs, jobs with no schedule, and jobs that have not been run in the past 30 days. You can use the provided script to create this stored procedure.

Finally, you can create a job that executes the usp_help_job_status procedure on the master server. You can also schedule this job to run daily. This will automatically generate the Job Status Report for you. You can run the procedure manually as well by connecting to the master server using Query Analyzer or SQL Server Management Studio.

With the Job Status Report, you can easily monitor the status of all jobs in your multi-server environment. It provides a clear overview of failed jobs, disabled jobs, jobs with no schedule, and jobs that have not been run in the past 30 days. This information helps you proactively manage your SQL Server environment and ensure the smooth execution of critical jobs.

Additionally, you can further enhance the report by creating formatted reports in Access or using SQL Server Reporting Services. These options allow you to share the report with others or make it available on your SharePoint portal.

Managing SQL Server jobs in a multi-server environment can be a daunting task. However, with the Job Status Report, you can efficiently monitor and track the status of all jobs across multiple servers. This helps you ensure the smooth operation of your SQL Server environment and avoid any potential issues.

For more information or assistance, you can contact the author at siva@cubeinfosolutions.com. Cube Info Solutions provides database consulting and other IT solutions.

Disclaimer: The scripts provided in this article work for SQL Server 7.0, 2000, and 2005. Please ensure compatibility with your specific SQL Server version before implementing.