SQL Server 2005 Agent XPs disabled error - how to resolve this issue?
When SQL Server 2005 Management Studio's Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP's disabled, the service is not started or disabled. This used to be a problem prior to Service Pack 2 for SQL Server 2005, obviously SQLAgent is important part of SQL Server tasks that you need to schedule on day-to-day basis and you must start the SQLAgent using following methods:
Start the SQL Server Agent service by:
- using the SQL Server Configuration Manger
located in Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager
- Or use the Services Management Console
located in Start -> Control Panel -> Administrative Tools -> Services
- Or you can start the default service from the command prompt
net start "SQL Server Agent (instance name)"
Eg: net start "SQL Server Agent (MSSQLSERVER)"
You may not see a success here and still the error appears in the Object Browser with "Agent XPs disabled" next to it. Using the folloiwng TSQL you can execute few SP_CONFIGURE statements to trap :
declare @agent_enabled bit
declare @show_advanced bit
select @show_advanced = cast(value_in_use as bit)
from sys.configurations where name = N'show advanced options'
select @agent_enabled = cast(value_in_use as bit)
from sys.configurations where name = N'Agent XPs'
if 0 <> @agent_enabled
begin
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1
reconfigure with override
end
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0
reconfigure with override
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0
reconfigure with override
end
end
Event after executing you may still get a message saying the service has started and stopped, when you run sp_configure, still 'Agent XPs' value will changed back to 0. To see this you could use PROFILER too by referring to the event "SQLAgent - Enabling/disabling Agent XPs". So next point of reference will be to review SQL Server error logs & SQLAGent error log, in some of the Enterprise network there may be security policies to stop any of the scheduling services on the server, so better to have a check on it as a policy would initiate a process to stop the Agent.
Further you need to perform few tasks by ensuring that SQL Server service account needs "Read access" to the "Remote Procedure Call (RPC) service" in order to initialise the MSDTC interface. and also to the "Event Log service" in order to start. To check these you can use Security Configuration Manager tool in Windows 2003 to set/check referred permissions.