When using a multi-server environment with SQL Server Agent from time to time a target server (TSX) will stop downloading instructions. Upon further investigation at the master server (MSX) it may be discovered that the target may have a Blocked status.
Force Poll
As a first attempt issuing a Force Poll command from the SQL Server Management Studio GUI interface may resolve the issue. This is accessed by right clicking the SQL Server Agent (MSX) node on the master server and choosing Multi Server Administration then Manage Target Servers. From this window highlight the server with Unread Instructions and click the Force Poll button.
Resynchronize the Target Server
While there are couple different methods for resolving this issue if a force poll does not work often a quick fix is to use the sp_resync_targetserver system stored procedure on the master server. This procedure will delete and recreate the multi-server jobs for the server.
-- On the Master Server
USE msdb;
GO
EXEC dbo.sp_resync_targetserver N'<TargetServerName>';
It is important to note that this method will cause any currently running multi-server jobs to be aborted and the job history will be lost as well. These side effects are present in the other method as well.
Defect the Target Server
Should the methods above fail to resolve the issue defecting the server and re-enlisting it is the next course of action.
First we will want to save a list of current jobs by executing the following on the target server. This will produce a set of commands that may be used to re-add the jobs. Save the output of this script so that it may be used later.
-- On the Target Server
USE msdb;
GO
SELECT
'EXEC msdb.dbo.sp_add_jobserver
@job_name = N''' +
name + ''',
@server_name = N''' + @@SERVERNAME + ''';' AS cmd
FROM
dbo.sysjobs
WHERE
originating_server_id = 1;
Next we need to attempt to cleanly defect the server by executing sp_msx_defect on the target server.
-- On the Target Server
USE msdb;
GO
EXEC dbo.sp_msx_defect;
Should the server not defect cleanly you may force a defection using sp_msx_defect on the target server.
-- On the Target Server
USE msdb;
GO
EXEC dbo.sp_msx_defect @forced_defection = 1;
Following the forced defection sp_delete_targetserver must be run on the master server.
-- On the Master Server
USE msdb;
GO
EXEC dbo.sp_delete_targetserver
@server_name = N'<TargetServerName>',
@post_defection = 0;
Once defection of the server has been completed either cleanly or forced the target server needs to be re-enlisted using sp_msx_enlist.
-- On the Target Server
USE msdb;
GO
EXEC dbo.sp_msx_enlist N'<MasterServerName>';
Once the target server has been successfully re-enlisted the jobs will need to be recreated by running the saved output of the earlier command on the master server. The sp_add_jobserver commands will be similar to the one below.
-- On the Master Server
EXEC msdb.dbo.sp_add_jobserver
@job_name = N'<Name of the Job>',
@server_name = N'<TargetServerName>';
There are comments.
