How to resolve : Agent XPs disabled

Posted: January 15, 2009 in SharePoint, SQL Server
Tags: ,

You may have seen this sentence in front of your SQL Server Agent in SSMS. and so you can’t start your SQL Server Agent ( maybe only with Network Service account )
You can enable Agent Xp’s in sql server 2005.  I think you’d better first create a Login and give proper roles to the user who run this service ( in my case Network Service with sysadmin role ). Then  run the below query in management studio to enable agent xp’s.

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

Use the Agent XPs option 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.

When you use the SQL Server Management Studio tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically.

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. You can find more about SQL Server Agent and Agent XPs option here.

Advertisements
Comments
  1. After reading the article, I feel that I need more information on the topic. Can you suggest some resources please?

  2. Viktar says:

    Thank you, it resolved my issue.

  3. Himalaya Garg says:

    To solve this issue you dont need to write any command:
    You can do it using the SQL Server Configuration Manger located in:

    1. Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

    2. Open SQL Server 2005 Services->Start Sql Server Agent->Set start mode-Automatic

  4. ekia2 says:

    Always maintain up the very good operate. Everybody is approved to help furthermore there viewpoint. Terrific weblog right here, we are always analyzing 🙂

  5. FDiderot says:

    It helps me a lot, thx

  6. click says:

    I have got 1 recommendation for your website. It appears like right now there are a handful of cascading stylesheet problems when launching a number of webpages inside google chrome and firefox. It is working fine in internet explorer. Perhaps you can double check this.

  7. na says:

    “Click” and probably “ekia2” are just comment spammers. They paste generic comments that could (almost) seem reasonable in many blogs.

  8. Bob says:

    We had same issue, but while the above did address issue, once we brought Cluster instance back online the Config value would revert back to 0. The fix was the SQL Agent error log path had gotten corrupted. To change.. In SSMS under SQL Agent, right click Error Log, click Configure and review/verify/update log path.

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