you're reading...
Database, IT-Center, SQL Server

Enable SQL Server Agent (Agent Xps Disabled)

Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.

When you use the SQL Server Management Studio tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically. For more information, see Understanding Surface Area Configuration.


Management Studio Object Explorer does not display the contents of the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state.

The possible values are:

  • 0, indicating that SQL Server Agent extended stored procedures are not available (the default).
  • 1, indicating that SQL Server Agent extended stored procedures are available.

The setting takes effect immediately without a server stop and restart.

sp_configure 'show advanced options', 1;
sp_configure 'Agent XPs', 1;

See Also


Other article from : http://sqlserver-qa.net/blogs/tools/archive/2008/02/06/agent-xps-disabled-error-how-to-resolve-and-restart-sql-server-agent-without-any-error.aspx

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
if 1 <> @show_advanced
exec sys.sp_configure @configname = N‘show advanced options’, @configvalue = 1
reconfigure with override
exec sys.sp_configure @configname = N‘Agent XPs’, @configvalue = 0
reconfigure with override
if 1 <> @show_advanced
exec sys.sp_configure @configname = N‘show advanced options’, @configvalue = 0
reconfigure with override

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.



About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 10 other followers

February 2012
« Jan   Mar »


Web Statistic

Blog Stats

  • 159,120 hits
%d bloggers like this: