Problem
The other day we found that SQL Server Agent was stopped with the following message in SSMS “SQL Server Agent (Agent XPs disabled)” for one of our SQL Server instances, but the service was running according to the services console. I tried to start the service from SQL Server Management Studio, but this did not work. What is the issue and how do I fix this?
Solution
The other day I changed an implementation on one of our SQL Server instances. After the change, I started all of the SQL Server services from the services.msc console and everything started successfully. But when I launched SQL Server Management Studio, SQL Server Agent showed that is was not running with ‘Agent XPs disabled’ message as shown below. I tried to restart the service from SSMS, but it did not work. The interesting thing was that the service showed as ‘started’ when I was looking at it in the services.msc console.
As SQL Server Agent was not running in SSMS, we were not able to access any of the contents like jobs, error logs, etc…
It appears, that this issue appears when ‘Agent XPs’ advance configuration option is disabled and set to 0 for the SQL Server configuration..
Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server. When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables ‘Agent XPs’, but sometime it fails to enable 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.
Next Steps
- Always use SQL Server Configuration Manager to start SQL Server services
- If you get this message in SQL Server, check the configuration settings to enable the Agent XPs
- Read the additional articles on SQL Server Agent
