Enabling "Agent XPs" on SQL 2005

Posted by jeff.story
January 15, 2008

Chances are that if you have just done a fresh install of SQL 2005 you will get an error when you try to create your first SQL 2005 maintenance plan.   This error tells you that the “‘Agent XPs component is turned off as part of the security configuration for this server”  and refers you to your system administrator or the SQL Books Online.

In order to enable the “Agent XPs” component. Start up SQL Management Studio and click the “New Query” button on the tool bar.   Execute the following script in the query window (on the right hand side of the screen) .  

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
 
This SQL script enables the advanced configuration options then turns on the “Agent XPs” component.    
 
You are now free to create your SQL 2005 Maintenance plans.
 
StumbleUpon It!
.Net Development, Windows Administration

If you found this page useful, consider linking to it.
Simply copy and paste the code below into your web site (Ctrl+C to copy)
It will look like this: Enabling "Agent XPs" on SQL 2005


Enjoy this post? Please consider leaving a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments
Comment by Gianluca on February 14, 2008 @ 3:50 am

It works perfectly!
Thx

Gianluca

Comment by Chetan on April 28, 2008 @ 8:59 am

Thank you very much, Really helpful..

Ihave few queries on, How to Test Integration Services??

Comment by Jaber on July 1, 2008 @ 6:44 pm

Thanks Jeff

Bull’s Eye!!!!

Great worked perfectly well..

Thanks Heaps

Comment by Tony on July 2, 2008 @ 11:16 am

Didn’t work…. got this error:
Configuration option ’show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.
Configuration option ‘Agent XPs’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.

Comment by Jeroen on July 10, 2008 @ 3:47 am

Thanks ! But if you have AWE enabled you get the error:
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.

A quick google gave me this solution:
http://www.tipandtrick.net/200.....g-to-disk/

Regards!
Jeroen

Comment by Fahan on August 26, 2008 @ 7:34 pm

Thanks buddy.

Comment by Brett on October 7, 2008 @ 10:55 pm

Perfect….nice fix

Thanks

Comment by Benjamin Lotter on February 4, 2009 @ 9:29 am

In our case, we had recently turn on AWE to manage the memory of the instance. I have no idea why but we then received the error regarding the Agent XPs being disabled when opening a maintenanace plan. Unchecking the setting resolved our issue.

Comment by krishna on March 17, 2009 @ 7:05 am

good one.. it’s worked for me thank you

Comment by Benny on April 6, 2009 @ 3:07 am

the perfect solution, work for me right away, thanks.

Comment by Chris on July 21, 2009 @ 10:25 am

That’s the ticket. Thanks!!

Comment by Mark on July 27, 2009 @ 3:10 pm

Thanks alot bud, I’av been looking for this fix.

Comment by cruzagr3 on November 26, 2009 @ 4:07 pm

Maybe you are going to have some problems when execute the script, in some cases you will have an error like this:
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

This is caused when you try to execute RECONFIGURE

So, You will need to change to this

sp_configure ’show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

I hope this work for you

Leave a comment

(required)

(required)