Click here to Skip to main content
15,885,244 members
Articles / Database Development / SQL Server
Tip/Trick

SQLAgent XPs disabled after repair of MSSQL 2008 R2

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
23 Sep 2011CPOL 18K  
How to resolve this.

After running a repair on MSSQL 2008 R2 you may find that the SQLAgent does not start up and indicates "Agent XPs Disabled" in SSMS. The following steps can be used to try and resolve this issue:


Configure XPs Option


SQL
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go

Security Groups


Ensure that the startup account of SQLAgent is part of the sysadmin group. Ensure that the SQLAgent startup account is a member of the local Windows group on the server called SQLServerSQLAgentUser$ServerName$InstanceName.


After our repair, all the above steps were required. Step 3 wasn't found in any article online, but only by going through all the options available.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer CyberNerd
South Africa South Africa
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --