Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Monitoring Jobs on Multiple Servers by Using Multi Server Query

MSSQLTips author Kun Lee By:   |   Read Comments (2)   |   Related Tips: More > 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.

setup local server groups

How to Execute a Multi Server Query

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

run a multi server query in SQL Server

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

validate connection for multi server query

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.

walk through of sql server code

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

sample output for multi server query

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


Last Update: 7/3/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, March 19, 2013 - 12:23:48 PM - Pavel Read The Tip

usefull

 


Tuesday, December 31, 2013 - 12:44:56 PM - Pam Ozer Read The Tip

This is a great help!! Thanks for posting this.  I have 50 instances I am trying to get data from and this works perfectly



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.