Monitoring Jobs on Multiple Servers by Using Multi Server Query

By: Kun Lee   |   Updated: 2012-07-03   |   Comments (3)   |   Related: > SQL Server Agent

Problem

There are often SQL Agent jobs that need to run on multiple servers in a coordinated effort, such as refreshing Development and Test environments from a Production server.  This requires that many jobs run on different servers and it requires many different steps.  Some of these steps maybe kicking off jobs to delete replication settings, making sure all jobs that need to run are finished, starting jobs that restore databases, etc...  Because there are so many jobs and many of these jobs maybe running on different servers it is hard to get an overall status for the entire process.  Each job might send an email when it is done, but since there are so many jobs involved it may be difficult to get the overall picture from a bunch of emails.  In this tip, I will walk through a process I setup to monitor multiple jobs from multiple servers from one central location.

Solution

There are many different ways to get this type of data, but for this tip I am going to utilize Multi Server Query. For those who are new to Multi Server Query, please see my previous article Execute SQL Server query on multiple servers at the same time for more detail.

Setup a Multi Server Group

Before you do anything, you need to setup the server group for servers that you want to query. For this tip, I am going to just setup Local Server Groups, but I highly recommend using Central Management Servers, so that other team members can also share the same groups.

How to Execute a Multi Server Query

First, right click on the group and click "New Query" as shown below.

This should create a connection to your group of servers as shown below. In our example "_MSSQLTIP_SERVER".

Once you are connected, you can copy and paste the script or open the script file from the connection box that you just created and configure it to query the jobs you want to track. 

Here is the code that is used for this process.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @JobList TABLE (Servername varchar(128), Jobname varchar(500), Stage TINYINT);
DECLARE @Stage TINYINT


/*
== Stage Level == 
1 - Part 1 : Put Maintenance Mode Check
2 - Part 2 : Drop Replication Check
3 - Part 3 : Start Restore 
4 - Part 4 : Bring Replication Back and full refresh
5 - Part 5 : Bring all online
*/


SET @Stage = 1


INSERT INTO @JobList
SELECT 'MyServer1\SQLA', 'Test_OSQLFailed', 1
UNION ALL
SELECT 'MyServer1\SQLA', 'Test job', 1
UNION ALL
SELECT 'MyServer2\SQLB', 'Test job', 2
UNION ALL
SELECT 'MyServer2\SQLB', 'syspolicy_purge_history', 2
UNION ALL
SELECT 'MyServer3\SQLC', 'Expired subscription clean up', 1
UNION ALL
SELECT 'MyServer3\SQLC', 'Test job', 1


--Checking for SQL Server version
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
BEGIN
  ---This is for SQL 2k5 and SQL2k8 servers
  SET NOCOUNT ON
  SELECT j.name AS job_name,
  CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
  CASE jh.run_status WHEN 0 THEN 'Error Failed'
  WHEN 1 THEN 'Succeeded'
  WHEN 2 THEN 'Retry'
  WHEN 3 THEN 'Cancelled'
  WHEN 4 THEN 'In Progress' ELSE
  'Status Unknown' END AS 'last_run_status',
  ja.run_requested_date as last_run_date,
  CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))
  +(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) 
  AS run_duration,
  ja.next_scheduled_run_date
  FROM
  (msdb.dbo.sysjobactivity ja 
  LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
  join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
  join @JobList jl on jl.Servername = @@servername and jl.Jobname = j.name and jl.Stage = @Stage
  WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) 
  ORDER BY job_name,job_status
END
ELSE
BEGIN
  --This is for SQL2k servers
  SET NOCOUNT ON
  DECLARE @SQL VARCHAR(8000)


  --Getting information from sp_help_job to a temp table
  SET @SQL='
  SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,
  CASE last_run_outcome WHEN 0 THEN ''Error Failed''
  WHEN 1 THEN ''Succeeded''
  WHEN 2 THEN ''Retry''
  WHEN 3 THEN ''Cancelled'' 
  WHEN 4 THEN ''In Progress'' ELSE
  ''Status Unknown'' END AS last_run_status,
  CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
  CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,
  CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,
  CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
  last_run_date AS lrd, last_run_time AS lrt
  INTO ##jobdetails
  FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'
  exec (@SQL)


  --Merging run date & time format, adding run duration and adding step description
  SELECT distinct
  jd.job_name,jd.job_status,jd.last_run_status,
  CONVERT(DATETIME,RTRIM(jd.last_run_date)) 
  +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) 
  / 216e4 AS last_run_date,
  CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 
  + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
  CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 
  + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date
  FROM (##jobdetails jd 
  LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time
  JOIN @JobList jl on jl.Jobname = jd.job_name and jl.Servername = @@servername and jl.Stage = @Stage) 
  WHERE step_id=0 or step_id is null
  ORDER BY jd.job_name,jd.job_status


  --dropping the temp table
  DROP TABLE ##jobdetails
END

Let me explain how this code is used. 

For this sample code, I defined stages 1 through 5.  This is used as a grouping for the various parts of my process.  I have multiple jobs that run under each stage, so this helps me narrow down what is returned in the query.  Other than that, the stage has no real meaning.

The key part of this process is the jobs that I am inserting into my temp table @JobList. 

There are three values that are used:

1. SQL Server Instance Name - this is the SQL instance that I want to check for the SQL Agent Job status
2. Job Name - this is the name of the SQL Agent Job that I want to check
3. Stage - this is an arbitrary grouping of the jobs.  You can see how I classified them in the Stage Level comments below

Another key part of this code is to set the @Stage variable.  This is basically setting a filter which determines what data is returned in my query.  In this case I am setting the @Stage = 1 which will only return the three jobs that have a Stage = 1 as shown below.  This example is pretty simple, but if you have 10 or 20+ jobs that run for one process, you can see how this filtering could be very useful.

So when the code is run, as you can see below, it only returns the three jobs that have a Stage = 1.

Notes About the SQL Code

I recommend downloading the script here just in case you have issues with copy the code.

• This code has been tested on SQL 2000 to SQL 2008 R2.
• Some of the code I've had for a long time and for SQL 2000 I did not write this originally. This is a very smart way to pull information for SQL 2000, so that it works on other versions.
• The script has limitations that if a job has only one step and it is still running, the last_run_status will return as "Status Unknown", which you can see in my above example.

The code uses a combination of OPENROWSET and "SET FMTONLY OFF" to make sure this works for both SQL Server 2000 and SQL 2005 / SQL 2008. For more details, I highly recommend reading Using SQL Servers OPENROWSET to break the rules. The rest of the code is just formatting the output to be more readable.

There you have it. Obviously, there are so many other ways to use this script and like someone that I know always says, "The sky's the limit" when it comes to using Multi Server Query.

Next Steps

• Download the script
• Enhance the query to pull job status information more accurately by using xp_sqlagent_enum_jobs
• Enhance the query to include the steps if there are multiple steps that run
• Enhance the query to include the job log information

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips