THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Resynchronizing a target server (MSX - TSX)

I often use SQL Server Agent master / target servers (MSX/TSX). I find it so convenient to create the job once and then just add whatever targetservers (TSX) should have this job. Especially when you later modify the job. Again, just modify it once. The usage of MSX in general, and how I use it, can easily become a series of blog posts in itself. But that is not the point here.

Sometimes a TSX goes out-of-sync with its master. I've never understood exactly under what circumstances, but it feels like it happened when you do things "too quickly". Like change a job, push it out, and before the push has finished, you change it again. Or something like that. A TSX going out-of-sync doesn't happen frequently. I've had it a handful of times. And every time, I have spent time searching etc. on how to fix it.

A couple of days ago, a client of mine had this case, and he had himself tracked down a possible way to fix this. We decided to go ahead with this, and it worked just fine. So, the purpose here is to document the (very easy) fix, for whenever this happens again. And for all of you out there who might benefit, of course. The error you see is something like:

[291] An unresolved problem exists with the download instructions (sysdownloadlist) for target server 'Y' at MSX 'X'

X here is obviously the master and Y the target. And the solution was quite simply (in the msdb database):

EXEC dbo.sp_resync_targetserver, N'Y'

Are you using MSX/TSX? Have you had sync issues? How did you handle them?

Published Sunday, August 11, 2013 12:59 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



David Korzennik said:

Hi Tibor

I have a large MSX/TSX setup. 176 Target servers.  All but one is working fine.  On this one server I was getting the unable to read or write to TSXCache.bin error. Found no solutions to that problem. I fixed the problem by going to MultiServer Admin, selecting the target server, finding the 1st download instruction that failed and deleting that.  I had to do that a few times.  Once the end of the list was reached for download instructions for the server, instructions flowed again.

September 6, 2013 3:16 AM

TiborKaraszi said:

Hi David,

Thanks for the feedback! That is a lot of target servers. I have max had about 50, or so. Good to know that it is more or less running fine and also to have yet another thing to try in case a TSX acts up on you. :-)

September 7, 2013 1:28 PM

Faran said:

Hi Tibor, David

My target server is not downloading the instructions from master. target's status is 'Normal, Suspected offline'. I can't figure out whats wrong.

I have registered the target on master server and forced the poll, which says Poll successful, but simply the changed are not applied on target.

Any advice?



November 29, 2013 7:05 AM

Adam S said:

Some background I have 3 environments running about 40 servers each and every so often we have a few servers going suspect or blocking. This little gem of a script helped me fix those 5-6 servers when they do decide to go off the deep end.

Thanks for the script and information.

February 20, 2014 1:25 PM

Sandeep said:

Hi Tibor,

Very nice info. I have tried your solution but still the target server status shown me as 'Normal / Blocked' and even i tried the method suggested by David here but had no luck. There are 11 instruction are waiting for download. My environment is not that big enough i have only 12 target servers.

Please help me to solve this issue.



July 24, 2014 5:01 AM

Gaurav Taneja said:

Sandeep follow below, it worked for me

1) Remove the target server nmame from msdb.dbo.systargetservers from msx server

2) Check the encrytion settings on target, set it off in registry(b careful) if not using certificates.

3) Readd\relist the target server in msx

July 31, 2014 8:40 PM

Samson J. Loo said:


What I ended up doing was going to the MSX server, right click on the SQL Server Agent >> Multi Server Administration >> Manage Target Server. Once the page loaded I clicked on the Download Instruction tab and filtered the results by Target Server.

I deleted all the records where it indicated (not downloaded) by highlighting the row and clicking the Delete button.

Lastly I went back to the job (under Multi-Server Jobs) that did not sync and removed it, saved the changed and shortly added it back. Had no issues after that.

February 19, 2015 5:08 PM

Samson J. Loo said:

We use MSX/TSX a lot here so I just want to point out that:

1) EXEC [msdb].[dbo].[sp_resync_targetserver] N'ServerName' <- this needs to be ran against the MSX server.

2) You will lose all the job history on the TSX server for all the jobs that are managed by the MSX.

February 19, 2015 5:13 PM

Patrick O'Regan said:

One lesser known thing to check is the databases 'Auto-Close' settings on the instance.  If you have one database which is lightly used and it closes, the database can change to suspect offline status.  I heard of one person this has happened to.  I'm using PBM to check the Auto-Close setting = False in our environment.

February 25, 2015 9:15 AM

Aruna said:

Very useful!

March 7, 2016 1:41 PM

Leave a Comment


This Blog


Privacy Statement