SQL Server Multi-Server Jobs issue

bukkybu 346 Reputation points
2021-06-23T19:48:01.58+00:00

I have some multi-server jobs set up with both master (MyServer\MasterInstance) and target (MyServer\TargetInstance) on the same server but different instances. Both instances have the registry setting MsxEncryptChannelOptions set to 1. The target server status was showing Normal | Blocked with 11 Unread Instructions. Forcing Poll gave the error "This operation requires server to be a registered server". I confirmed that the target server is registered. I successfully ran sp_resync_targetserver for the target server.

SP_POST_MSX_OPERATION: 1 DELETE download instruction(s) posted.
SP_POST_MSX_OPERATION: 10 INSERT download instruction(s) posted.

I Forced Poll again which showed successful. It successfully ran the delete instruction but nothing else.

Next, I ran 'Delete from msdb.dbo.systargetservers' on the targer server and Forced Defection successfully. I added the target server back successfully with Normal status. I Forced Poll again successfully with status still showing Normal, but no instructions were executed, and the jobs were not created on the target. The next day, the target is back to showing Normal | Blocked again.

So, what I know is that the instances are successfully communicating when the target server is resynced or when it is dropped and readded. In both cases, the delete instruction executes successfully, but none of the other instructions execute despite showing normal status and successful polling. The job error log on the target shows "An unresolved problem exists with the download instructions (sysdownloadlist) for target server 'MyServer\TargetInstance' at MSX 'MyServer\MasterInstance'".

EDIT: I ran select * from sysdownloadlist and found the following error:
The job step(s) could not be read (see the SQLServerAgent errorlog [at the target server] for details), or the job doesn't have any steps.
It turns out one of the jobs on the master did not have a job step in it. Once the step was there, the target eventually showed Normal status and the jobs synced successfully.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,639 questions
0 comments No comments
{count} votes

Accepted answer
  1. bukkybu 346 Reputation points
    2021-06-23T19:48:33.947+00:00

    **EDIT: I ran select from sysdownloadlist and found the following error:
    The job step(s) could not be read (see the SQLServerAgent errorlog [at the target server] for details), or the job doesn't have any steps.
    It turns out one of the jobs on the master did not have a job step in it. Once the step was there, the target eventually showed Normal status and the jobs synced successfully.*

    0 comments No comments

0 additional answers

Sort by: Most helpful