Export (0) Print
Expand All
Expand Minimize


SQL Server 2000

Removes the current server from multiserver operations.

Caution  sp_msx_defect edits the registry. Manual editing of the registry is not recommended because inappropriate or incorrect changes can cause serious configuration problems for your system. Therefore, only experienced users should use the Registry Editor program to edit the registry. For more information, see the Microsoft® Windows NT® or Microsoft Windows® 95 documentation.


sp_msx_defect [@forced_defection =] forced_defection


[@forced_defection =] forced_defection

Is whether or not to force the defection to occur if the Master SQLServerAgent has been permanently lost due to an irreversibly corrupt msdb database, or no msdb database backup. forced_defection is bit, with a default of 0, which indicates that no forced defection should occur. A value of 1 forces defection.

After forcing a defection by executing sp_msx_defect, a member of the sysadmin fixed server role at the Master SQLServerAgent must run the following command to complete the defection:

EXECUTE msdb.dbo.sp_delete_targetserver @server_name = 'tsx-server', @post_defection =  0
Return Code Values

0 (success) or 1 (failure)

Result Sets



When sp_msx_defect properly completes, a message is returned:

Server defected from MSX ''. n Job(s) deleted.

Only members of the sysadmin fixed server role can execute sp_msx_defect.

See Also


System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft