­

Starting SQL Server Agent when the Agent XPs shows as being disabled

8:39 PM

Starting SQL Server Agent when the Agent XPs shows as being disabled


Recently I had a problem with a client's SQL Server cluster where C3 pinged me about the SQL Agent being down.  When I logged on to the server and tried to start it in SQL Server Management Studio it reported the Agent XPs was disabled.  I opened the services.msc and found that it reported the agent service as running.
This happens when the SQL Agent is disabled.  The first step to correcting this is to run sp_configure and see what the setting for Agent XPs is set to.  Run the following SQL statement:
EXEC sp_configure
As long as the advanced configuration option has been enabled you will see Agent XPs on the eighth line of the results.


 


If they have not been enabled first run this statement.
EXEC sp_configure 'Show Advanced Options', '1'
GO
RECONFIGURE WITH OVERRIDE
When the SQL Agent extended stored procedures are not enabled SQL Server Agent will not be active in Management Studio. Most of the time when you start the SQL Server services it automatically enables 'Agent XPs', but occasionally or sets the value to 0 and then this issue will appear.
To fix this issue we should first set the 'Agent XPs' to 1 and then run RECONFIGURE to bring it into effect.
Step 1
Run sp_configure to check 'Agent XPs' value.
EXEC SP_CONFIGURE 'Agent XPs' 


 

Step 2
The above screenshot shows that advanced options is not enabled on this instance, so we must first enable advanced option to see all of the advanced configuration values.
EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'show advanced options' 


 

You can see 'show advanced options' is set to 1 this means that advanced options are enabled and we can see and change the values.
Step 3
Run sp_configure again to check the value for Agent XPs. Here we can see the run value is set to 0. 


 

Now we need to change this setting from 0 to 1 to run SQL Server Agent in SQL Server Management Studio.
EXEC SP_CONFIGURE 'Agent XPs', 1
GO
RECONFIGURE 


 

Step 4
Now restart your SQL Server Agent service from SQL Server Configuration Manager. This time the service should come up and we can successfully access all of the content for SQL Server Agent.

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results