some SQL Server MSX targets show as Normal, Blocked

Question

I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window Manage Target Servers, instead of all instances showing as Normal, they show as Normal, Blocked. I have failed to figure out why this happens. Defecting and re-enlisting usually cures this ill, but that means having to re-add that target in any appropriate jobs, so I would rather resolve the cause than endure the workaround.

Any advice? I would like to keep using SQL Server's tools for multi-server administration, but am open to other avenues.

Asked2012-03-27byOliver

Answer

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;

Answered2018-10-15byAndy Mallon