Finding MSX Target Servers in a Blocked Status

3

September 18, 2012 by Mike Hillwig

I’m a big fan of using SQL Server Multi-Administration (MSX) tool. It’s a great way to link the SQL Server Agents across multiple servers. Working in a hosting environment, I use it a ton, and I’ve learned quite a bit. It’s certainly more than enough to do a blog series. The MSX component works really well–most of the time.

Every once in a while, a server will get blocked, meaning the target can’t receive updates from the master. What I find to be the most common cause is when you attempt to push a job that runs against a certain database that doesn’t exist on the target. And that’s when you find the server is blocked.

Sure, enough, the details of your linked servers are can be find via the MSDB database. This is in a procedure called sp_help_targetserver. What I’ve done is pumped the output of that procedure into a table and then looked for anything with status=5. If anything is found, we have a blocked server.


create table #tmp_sp_help_targetserver
(server_id int null,
server_name sysname null,
location nvarchar(200) null,
time_zone_adjustment int null,
enlist_date datetime null,
last_poll_date datetime null,
status int null,
unread_instructions int null,
local_time datetime null,
enlisted_by_nt_user nvarchar(200) null,
poll_interval int null)

insert into #tmp_sp_help_targetserver
exec msdb.dbo.sp_help_targetserver

SELECT SERVER_NAME
FROM #tmp_sp_help_targetserver
where status = 5

drop table #tmp_sp_help_targetserver

If you’ve seen my “Seven Jobs” presentation, this is a “see something, say something” situation. We’re seeing something bad, so we’re going to speak up and fire off an alert.

Keep in mind that this would be something you run from your MSX servers and not your targets.

  • I am not sure the place you’re getting your information, but great topic. I must spend some time finding out much more or figuring out more. Thanks for great info I used to be in search of this info for my mission.

  • faran

    Hi there, do you know if a target is in Normal suspected offline mode (status=3), and not downloading any changes from master, what could be the reason apart from one you have mentioned?

    Any ideas how to figure it out?

    Many thanks

  • We are a group of volunteers and opening a new scheme
    in our community. Your site offered us with valuable info to work on. You have done an impressive job and our entire
    community will be thankful to you.