SQL Server Multi Administration Job not appearing on target server inquiry

Question

SQL Server Multi Administration Job not appearing on target server inquiry

yhandz_21

SSC-Addicted

Points: 446
More actions
March 31, 2017 at 2:07 am

#335947

Hi,
Can anyone help me on this inquiry that it seems there was no error on job creation but not appearing on secondary server

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

They get blocked sometimes for different reasons - you don't get an error but you will see the Normal|Blocked status.
On the master, you could try executing: sp_resync_targetserver 'YourTargetServer'
It will delete any multiserver jobs on the target and then try to download again.

Sue

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 2

Sue_H - Saturday, April 1, 2017 2:25 PM
They get blocked sometimes for different reasons - you don't get an error but you will see the Normal|Blocked status.
On the master, you could try executing: sp_resync_targetserver 'YourTargetServer'
It will delete any multiserver jobs on the target and then try to download again.
Sue

yes thanks for that it worked after sp_resync_targetserver 'YourTargetServer' but just want to ask is there other way that I don't do that resync everytime to happen or can I create an automation for that as well if it needs to be resync everytime. we want f there will be a new server/s that the jobs will be created to the target/s.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

yhandz_21 - Sunday, April 2, 2017 8:34 PM
yes thanks for that it worked after sp_resync_targetserver 'YourTargetServer' but just want to ask is there other way that I don't do that resync everytime to happen or can I create an automation for that as well if it needs to be resync everytime. we want f there will be a new server/s that the jobs will be created to the target/s.

You can check the logs on the target server and see if you can find any reason but I've never seen that be consistent and it just seems to happen for some unknown reasons at times.

I've never automated checking that but I would guess you could probably write a script that could check the status column in sysdownloadlist or possibly execute sp_help_targetserver and have the results insert into a temp table, and select the values from that. I can't remember offhand but I think status 5 is the one for normal/blocked.

Sue

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 4

Sue_H - Monday, April 3, 2017 6:37 AM
yhandz_21 - Sunday, April 2, 2017 8:34 PM
yes thanks for that it worked after sp_resync_targetserver 'YourTargetServer' but just want to ask is there other way that I don't do that resync everytime to happen or can I create an automation for that as well if it needs to be resync everytime. we want f there will be a new server/s that the jobs will be created to the target/s.
You can check the logs on the target server and see if you can find any reason but I've never seen that be consistent and it just seems to happen for some unknown reasons at times.
I've never automated checking that but I would guess you could probably write a script that could check the status column in sysdownloadlist or possibly execute sp_help_targetserver and have the results insert into a temp table, and select the values from that. I can't remember offhand but I think status 5 is the one for normal/blocked.
Sue

Thanks it worked but I've encountered a new problem so I added the new server. It was added successfully but job is not create on the new target server so I type again sp_resync_targetserver 'AWSWNDVDB97\OLTP14EETST01' -this time the new target server but I got this on the results:
SP_POST_MSX_OPERATION Warning: The specified @specific_target_server ('AWSWNDVDB97\OLTP14EETST01') is not involved in a multiserver job.
SP_POST_MSX_OPERATION Warning: The specified @specific_target_server ('AWSWNDVDB97\OLTP14EETST01') is not involved in a multiserver job.

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 5

yhandz_21 - Tuesday, April 18, 2017 10:03 PM
Sue_H - Monday, April 3, 2017 6:37 AM
yhandz_21 - Sunday, April 2, 2017 8:34 PM
yes thanks for that it worked after sp_resync_targetserver 'YourTargetServer' but just want to ask is there other way that I don't do that resync everytime to happen or can I create an automation for that as well if it needs to be resync everytime. we want f there will be a new server/s that the jobs will be created to the target/s.
You can check the logs on the target server and see if you can find any reason but I've never seen that be consistent and it just seems to happen for some unknown reasons at times.
I've never automated checking that but I would guess you could probably write a script that could check the status column in sysdownloadlist or possibly execute sp_help_targetserver and have the results insert into a temp table, and select the values from that. I can't remember offhand but I think status 5 is the one for normal/blocked.
Sue
Thanks it worked but I've encountered a new problem so I added the new server. It was added successfully but job is not create on the new target server so I type again sp_resync_targetserver 'AWSWNDVDB97\OLTP14EETST01' -this time the new target server but I got this on the results:
SP_POST_MSX_OPERATION Warning: The specified @specific_target_server ('AWSWNDVDB97\OLTP14EETST01') is not involved in a multiserver job.
SP_POST_MSX_OPERATION Warning: The specified @specific_target_server ('AWSWNDVDB97\OLTP14EETST01') is not involved in a multiserver job.

Another problem I defected the 2 target server the 98(working earlier) 97(was not) when I try to add both again it has no issue but jobs didn't appear on target and when i ran the resync got an error: Could not find stored procedure 'sp_resync_targetserver'.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

For the first error with "not involved in a multiserver job" that sounds like it may not have a download list for that server. Check the properties for one of the jobs, go to the targets page, make sure Multiple Servers is selected and that the specific server is selected. Also make sure the server is setup as a target server - select from msdb.dbo.systargetservers.
I'd probably also query msdb.dbo.sysdownloadlist just to see what's in there and if it gives you any clues on what the issues are.

For the second error, make sure you execute the stored procedure when you are in the msdb database. That's the only time I can think of that you'd get that error.

Sue