After the installation of a production server SQL Server 2014 in Standard or Enterprise version (Express is not concerned), it is logical to want to activate automatic backups by the Maintenance Plans SQL . Only, an error appears when trying to create a new maintenance plan for an XPs Agent. This tutorial explains how to fix this error and how to configure tasks in SQL Server.
The “XP Agent” component is disabled due to the security settings defined for this server. A system administrator can define the use of the “Agent XPs” component using sp_configure. For more information on activating the “Agent XPs” component, see “Configuration of the exposure surface” in the online documentation of SQL Server. (ObjectExplorer).
The agent SQL Server must be disabled.
1. Run this request to activate the XPs Agent.
sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO
2. The result is:
The’show advanced options’ configuration option has been changed from 0 to 1. to install, execute the instruction RECONFIGURE .
The configuration option’Agent XPs’ has been changed from 0 to 1. to install, execute the instruction RECONFIGURE .
3. Maintenance Plans and other functions are now operational since the Agent SQL Server is started.