I am a Defector!

Ever worked with SQL Server Master Job Servers? A great idea really - have a single server that pushes out job definitions to loads of other instances. The problem comes when you try and 'defect' a server and SQL just says 'MSX defect failed for JobServer', without any explanation.

Ok, so there are some obvious things to check, like whether or not the master server still exists and is online, whether there are any security issues between the two instances and so on. If you get to the stage that we were in where the master server doesn't know about the target server and the target server can't defect...well, this is what I did on the target server instance to resolve the issue:

1. Try force defection the standard way
EXEC dbo.sp_msx_defect @forced_defection = 1

if that fails...

2. Clear the MSXServerName registry key value

EXECUTE master.dbo.xp_instance_regwrite 

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXServerName',

N'REG_SZ',

N''

3. Delete the MSXPollInterval registry key value

        EXECUTE master.dbo.xp_instance_regdeletevalue

                N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

N'MSXPollInterval'

4. Remove the entry from sqlagent_info

        DELETE FROM msdb.dbo.sqlagent_info

        WHERE (attribute = N'DateEnlisted')

5. Delete any master jobs

        EXECUTE msdb.dbo.sp_delete_all_msx_jobs @current_msx_server, @jobs_deleted OUTPUT

6. Restart SQL Agent

7. Delete the MSXOperator SQL Agent Operator

        EXECUTE msdb.dbo.sp_delete_operator @name = N'MSXOperator'

This worked for me but usual caveats apply.

Later dudes!