3

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.

enter image description here

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:

enter image description here

Which takes us to this window: enter image description here

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.

4

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'
  • That's actually a really good idea! I find the profiler quite confusing, but I'll look further into extended events for this use-case. Thank you. – krystah Jul 7 '17 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.