Sql-server – Query job history for TSX servers from MSX server using TSQL

sql serversql-server-agentt-sql

I am attempting to construct TSQL queries to substitute various GUI tools provided by SQL Server Management Studio. One of these tools is the Job Activity Monitor, accessible through the Object Explorer window.

Our current database environment includes a cluster with 3 nodes(one primary and two read-only replicas), as well as a single, independent server standing next to the cluster.

For brevity, I'll call the nodes N1, N2 and N3, and the independent server S1.

Recently, we configured Multi-Server Job Administration for our servers, using S1 as the MSX (master) server, and N1, N2 and N3 as TSX (target) servers.

This means that SQL Agent Jobs that operate on the cluster-nodes are created and managed from S1.

TSX servers report their state, outcome etc to the MSX server, which can be
accessed using said Job Activity Monitor. From the Job Activity Monitor, the job-history of each enlisted server (TSX) can be accessed by selecting View history:

Which takes us to this window:

This is the information I am trying to recreate, using a TSQL query on the MSX server. I want to see the outcome and history of the jobs by all enlisted servers, similar to what is displayed by the GUI window.

I've tried digging through the job-related tables and views of the msdb database, but with no luck. The msdb.dbo.sysjobhistory table on the MSX server contains no history from enlisted servers, and I can't seem to find any good documentation on how else I would go about gathering it. Is this data even accessible through TSQL? Any relevant resource is greatly appreciated.

Best Answer

Whenever you want to see what SSMS is running, you can always fire up a Profiler/Extended Events trace and filter on your login.

Doing this from MSX master and viewing a Multi-Server's job history gets you this query:

SELECT null as instance_id, 
     sj.job_id,
     job_name = sj.name,
     null as step_id,
     null as step_name,
     null as sql_message_id,
     null as sql_severity,
     sjh.last_outcome_message as message,
     sjh.last_run_outcome as run_status,
     sjh.last_run_date as run_date,
     sjh.last_run_time as run_time,
    sjh.last_run_duration as run_duration,
     null as operator_emailed,
     null as operator_netsentname,
     null as operator_paged,
     null as retries_attempted,
     sts.server_name as server
  FROM msdb.dbo.sysjobservers                sjh
  JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id)
  JOIN msdb.dbo.sysjobs_view     sj  ON(sj.job_id = sjh.job_id)
  WHERE sj.name = 'MULTI_SERVER_JOB_NAME'

Related Solutions

Sql-server – Differences between CMS and MSX/TSX

The MSX/TSX is indeed related to the SQL Agent, allowing you to schedule jobs on a master server and have those jobs pushed out to slave servers.

CMS is an extension on the registered servers concept and allows you register servers into the MSDB database of a single instance through the SSMS GUI. Once the servers are registered in the CMS you can logically group them by business unit, version, etc. Once you have them registered any DBA/user with permission to the instance can connect to the CMS and see the same grouping.

CMS does not allow you to schedule agent jobs like MSX does, but you are able to run a script through SSMS on servers grouped in the same folder in CMS. For example, if you highligh a subfolder in CMS and hit New Query it will connect to all the instances registered in that folder and run the script on each one. Below is an example script you could run; CMS will return the instance name and the user running the script as columns by default. You can also use CMS for grouping Policy Based Management evaluations and policies.

select @@VERSION as Verison

Sql-server – some SQL Server MSX targets show as Normal, Blocked

When using a centralized MSX (Multi-Server SQL Agent jobs), there are some shortcomings in the SSMS GUI for administering those multi-server jobs.

When a target server has a problem downloading instructions for a job, those errors will be listed in msdb.dbo.sysdownloadlist. However, in the SSMS GUI, this only manifests as the job showing as having a "Blocked" status.

You can use EXEC sp_help_targetserver @server_name = 'MyServer'; to see the instructions, including errors & unsent instructions.

Microsoft recommends re-syncing the entire server when you see that it is blocked. This is done by executing the command EXEC sp_resync_targetserver @server_name = 'MyServer'; Doing this will delete & re-create all jobs on the target server. This can be disruptive (you can imagine what might go wrong if you drop & recreate all your jobs at the same time a job should run). Additionally, it will result in losing job history on the target.

Instead, you may choose to manually repair the specific job(s) that are a problem:

DECLARE @server_name sysname = 'MyServer';
DECLARE @job_name    sysname = 'The Job That Broke';

-- Get the Job_ID
DECLARE @job_id uniqueidentifier;
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = @job_name;

--Delete existing download instructions for this server/job
DELETE dl
FROM msdb.dbo.sysdownloadlist   AS dl
WHERE dl.target_server = @server_name
AND dl.object_id = @job_id;

--Post new instructions to delete & re-add job
EXECUTE msdb.dbo.sp_post_msx_operation 
                @operation = 'DELETE', 
                @object_type = 'JOB', 
                @job_id = @job_id, 
                @specific_target_server  = @server_name;
EXECUTE msdb.dbo.sp_post_msx_operation 
                @operation = 'INSERT', 
                @object_type = 'JOB', 
                @job_id = @job_id, 
                @specific_target_server  = @server_name;

Best Answer

Related Solutions

Sql-server – Differences between CMS and MSX/TSX

Sql-server – some SQL Server MSX targets show as Normal, Blocked

Related Question