Tuesday, November 17, 2009

SQL 2005 cannot view or run Maintenance Plans

Server 2003 SQL 2005 Enterprise could not modify or execute Maintenance Plans without seeing this message.

'Agent XPs' component is turned off as part of the security configuration for this server.  A system administrator can enable the use of 'Agent XPs' by using sp_configure.

[caption id="attachment_207" align="alignnone" width="611" caption="Agent XPs component is turned off"]Agent XPs component is turned off[/caption]

Our first google search lead us to an MSDN article that provide SQL commands to run:


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

Those commands provided these results which actually got us closer to the fix:

We searched and found that AWE was enabled within SQL Server.  This lead us to another Microsoft article that related AWE to a local security policy setting and the fix.

FIX:  Edit local security policy / Local Policies / User Rights Assignment / Lock pages in memory properties - add to this group the account used to run the SQL Services.