If the SQL Server 2005 Surface Area Configuration tool is used to start the SQL Server Agent service, it also turns on the “Agent XPs” advanced option. This allows the Database Maintenance Wizard to run with no issues. This is a new SQL Server option found in SQL Server 2005.
But if you don’t start the SQL Server Agent service using the SQL Server 2005 Surface Area Configuration tool (using some alternative method), you will get the message you described above. This is because the Surface Area Configuration tool, when it is used to start the SQL Server Agent service, automatically sets the “Agent XPs” advanced option to on, while alternative methods of starting the SQL Server Agent service do not turn this advanced option on.
To turn on the “Agent XPs” advanced option, run the following script from Management Studio.
use master
go
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
go
RECONFIGURE
GO
Once the above script is run, you should be able to use the Database Maintenance Plan Wizard.
“Agent XPs” are extended stored procedures used by the SQL Server Agent, and are turned off by default to help boost overall SQL Server 2005 security.
]]>