7

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.

migrated from serverfault.com Mar 27 '12 at 19:45

This question came from our site for system and network administrators.

  • 1
    Got it. The action to take is to execute sp_resync_targetserver, from the MSX. Works like a charm. Thanks, BOL! – Oliver Mar 27 '12 at 18:14
2

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;

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.