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.

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:

Failed jobs

Disabled jobs

Jobs with no schedule

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.

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.

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.

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.