DataGinger.com

Today we got an escalation where on one of the servers ‘Agent XPs’ was in a disabled state. And when looked at SQL Server Agent node, it is displayed in Red which basically indicates SQL Agent Service is not running, however when checked the Agent Services are actually running, which is misleading!

So here is some basic information I gathered about Agent XPs option – only found in SQL Server 2005 and above versions

Summing up from MSDN BOL:

• When Agent XPs is not enabled (which makes Agent extended stored procedures are not enabled), SSMS does not display the contents of the SQL Server Agent node regardless of the SQL Server Agent service state.
• Hence, in order to enable the SQL Server Agent extended stored procedures on the server, Agent XPs should be enabled
• When SQL Server Management Studio tool is used to start the SQL Server Agent service, these extended stored procedures are enabled automatically, which should enable Agent XPs server configuration option.

It is not very clear from BOL what makes Agent XPs setting enable/disable, so I dug more into this and here is some brief notes:

• I Installed a local instance (SQL 2012) on my machine and did a few test scenarios which confirms that Agent XPs option will be enabled automatically when SQL Agent Services are started, either with Manual mode or Automatic mode.
• And whenever the SQL Agent service is started or stopped regardless of the SQL Agent start mode (Manual/Automatic), it will enable or disable the Agent XPs respectively.
• Hence, if SQL Agent is stopped and don’t restart – it will disable the Agent XPs option(even though we enabled the Agent XPs prior to stopping SQL Agent)
• When the Agent XPs option is disabled, we cannot access any SQL Agent nodes or even its properties from SSMS.
• Vice-versa, when the Agent XPs option is enabled, we can access SQL Agent nodes and properties from SSMS whether or not SQL Agent services are running or stopped
• And, from the above observations, it is clear says that Agent XPs option disabled doesn’t mean that SQL Agent services are stopped

Finally from my understanding most of the time Agent XPs option shouldn’t be an issue since whenever the SQL Agent services are started the Agent XPs option will be automatically enabled.

If this option is disabled, either the SQL Agent services never started or Agent XPs option might have disabled due to some reasons.. such as security patching on the servers (which can be enabled anytime using a simple script which doesn’t need Agent/Server restart).

--To check if Agent XPs option is enabled or not 
--(show advanced options must be enabled for this)
EXEC SP_CONFIGURE 'Agent XPs'
GO 
--To enable show advanced options
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
--To see advanced options
EXEC SP_CONFIGURE 'show advanced options'
GO
--To enable Agent XPs option
EXEC SP_CONFIGURE 'Agent XPs', 1
GO
RECONFIGURE
GO

Read Full Post »