CODEWITHSUNDEEP
sql-serversql-server-2012
perrydyball
perrydyball

Reputation: 51

SQL Server 2012 tells me Agent XPs component is turned off but SQL Agent is running

I have a very strange situation on SQL Server that I cannot fathom out.

Environment : SQL Server 2012 SP3 CU3 running on a 2 node Windows 2008 R2 cluster

In SQL Server Management Studio\Management\Maintenance Plans\ I am unable to create or edit existing plans.

I receive the error:

'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (ObjectExplorer)

Checking around that error I expected the following config was going to be required.

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE; 
GO  
-- To enable the feature.  
EXEC sp_configure 'Agent XPs', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE; 
GO

However, I noticed that SQL Agent was already running so I thought I would also check existing config options for 'Agent XPs'

What was interesting was that config_value = 0, run_value = 1 where I was expecting config_value = 1, run_value = 1.

I thought I'd try the sp_configure solution to 'force' the config but when I ran it (step by step), the first RECONFIGURE statement just hung and indeed when it ran I could not even run an sp_who2 to see if it was blocking or being blocked.

The only way I could kill the RECONFIGURE was to close the query window which cancelled it. I therefore am unable to run EXEC sp_configure 'Agent XPs', 1 as the required RECONFIGURE cannot be run.

After a failover of the cluster, the config settings for 'Agent XPs' remains at config_value = 0, run_value = 1.

Has anyone got any ideas as to how to fix it?

Upvotes: 4

Views: 4458

Answers (1)

perrydyball
perrydyball

Reputation: 51

I stumbled across an internet post with a similar issue and that contained a nugget of information that allowed me to ultimately fix the issue.

I documented the case over at SQLServerCentral

https://www.sqlservercentral.com/Forums/1927277/SQL-Server-2012-tells-me-Agent-XPs-component-is-turned-off-but-SQL-Agent-is-running

Upvotes: 1

Related Questions