Have you wondered what these Agent XPs next to SQL Server agent mean?
Agent XPs
Agent XPs are extended stored procedures with SQL Server agent. They are enabled by default. Both the SQL server agent service and Agent XPs have to enabled for the Agent to fully work. Agent XPs is a configuration option that can be enabled\disabled in sp_configure.
What Happens When You Disable Agent XPs?
I tried enabling and disabling SQL server agent service and Agent XPs in different combinations to see how the agent reacts and here’s what I found.
When Agent XPs are disabled:
If Agent XPs are disabled, regardless of SQL Server agent is started or stopped, SQL agent node will NOT expand in SSMS and no actions can be done on the agent.
When Agent XPs are enabled:
If Agent XPs are enabled and SQL Server agent is stopped, you can still expand the node in SSMS, but you won't be able to run the jobs.
If Agent XPs are enabled and SQL Server agent is started, you can perform all actions on the agent.
Troubleshooting Agent Node Issues
So, the next time you face an issue where the SQL Server Agent node won’t expand, or it expands but you’re unable to run jobs, be sure to check the config value of Agent XPs.
Since Agent XPs are enabled by default, this issue is often caused by them being accidentally disabled. If for some reason Agent XPs have been turned off, here’s how you can verify and enable them.
How to Enable Agent XPs
--Enable advanced options
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
--Check value of Agent XPs
EXEC SP_CONFIGURE 'Agent XPs'
GO
--Enable Agent XPs
EXEC SP_CONFIGURE 'Agent XPs', 1
GO
RECONFIGURE
GO
--Verify value of Agent XPs
EXEC SP_CONFIGURE 'Agent XPs'
GO
--Disable advanced options
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GOAfter running the above commands, Agent XPs will be enabled, and SQL Server Agent should work without any issues.
If SQL Agent service is stopped, you can start using Configuration Manager for standalone instances and Cluster Manager for clustered instances.
Conclusion
Agent XPs are enabled by default. If there are any issues with SQL Server agent, then check the value for Agent XPs and enable it, if it is not.