SQL Server Agent - Agent XPs Disabled
page 2 of 3
by Aamod Thakur
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21363/ 19

What is Agent XPs option?

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

Note: SQL Server Management Studio Object Explorer does not display the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state. i.e Even if the SQL Server Agent Service is Started or Stopped ,you will still see the  SQL Server Agent (Agent XPs disabled) in object explorer.

How do we get out of this SQL Server Agent (Agent XPs disabled) problem?

Just run the below code.

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

Once this is done, Refresh your Object Explorer or SQL Server Agent node and its all done.

SQL Server Agent node is enabled for you.


View Entire Article

User Comments

Title: Mr.   
Name: Aamod
Date: 2010-04-27 1:58:49 PM
Comment:
Thanks Schmid, Good to know that it helped someone. N thanks for sharing the other way of solving this issue in SQL Server 2008.

Aamod Thakur
MCITP-DBA
Title: Mr.   
Name: Schmid
Date: 2010-04-27 11:57:58 AM
Comment:
Hi Aamod.

This solution worked without any problems or errors. This morning I was checking the SQL Server 2008 instances on our server in preparation for migrating some Access database to SQL 2008, when I noticed the 'Agent XP's disabled' message next to SQL Server Agent for one of those instances. That's odd, I thought. The other instance showed an icon with a red circle with an x, and I could expand the node. I checked SQL Server Configuration Manager, and the settings were the same. I then found your article, ran the script, and I solved my problem.

After doing some more research, I learned that the former SQL Server surface configuration options in SQL 2005 got moved to a new View Facets window in SQL 2008. If one right-clicks on a server in Object Explorer, and then click on Facets, you are able to access all the surface configuration options, and then some! Under the Facet drop down list in the View Facets window, select Server Configuration. Under Facet Properties you will see the option 'AgentXPsEnabled'. If it is set to False, then the SQL Server Agent node is not available. Change it to True, and the node becomes available.

Of course, the code you gave makes the job quick and simple without having to go through Facets.

Cheers, Dirk






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 4:34:19 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search