How to: Start SQL Server Agent When Agent XPs show Disabled

Problem
The SQL Server Agent was found stopped and the status shows “(Agent XPs disabled)” besides the SQL Server Agent, the funny thing is the service was running according to the services console. Tried to start the service from SQL Server Management Studio, but it didn’t work.

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.

Solution
To fix this issue we should first set the ‘Agent XPs’ to 1 and then run RECONFIGURE to bring it into effect. Execute the following query and try to restart the agent service. The SQL Server agent should be online now.

sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go

This will be the possible output

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

References

http://sqlserverzest.com/2012/09/19/agent-xps-server-configuration-option/

How to: Check SQL Service Status Using sys.dm_server_services

The sys.dm_server_services DMV (Dynamic Management View) only slipped in as part of SQL Server R2 SP1 (only in service pack 1). The following scripts will not generate the result if you try to execute it in SQL Server 2005 environment. So, if you are using SQL Server 2008 R2, you can install service pack 1, and the new DMVs will be available. Please take note as of R2 SP1, only information about the SQL Server and SQL Server Agent services in the current instance of SQL Server will be return. To read more about sys.dm_server_services, click here.

The following T-SQL script will show you the status of your SQL Server service and agent and your SQL Server uptime

SELECT 
servicename, startup_type_desc, status_desc, 
RTRIM(CONVERT(CHAR(17),DATEDIFF(second,convert(datetime,last_startup_time),getdate())/86400)) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400/3600)),2) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400%3600/60)),2) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400%3600%60)),2) AS [Service_Uptime D:H:M:S]
FROM sys.dm_server_services 

Source from: MSSQLTips.com – SQL Server Service Status Check