Checking the Job Status on Multiple Servers

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.

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.

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.

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

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:

- The Source of this article can be found @ sqlservercentral.com.