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.