Recently, one of my clients encountered an error while trying to create a maintenance plan in SQL Server. They received the following error 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. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

This error occurs when the SQL Server Agent service is not running or when the ‘Agent XPs’ component is disabled. In order to fix this error, there are two possible solutions:

Solution 1: Start SQL Server Agent Service

The first solution is to start the SQL Server Agent service. This can be done using the SQL Server Configuration Manager or by running the following command in the SQL Server Management Studio query window:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE WITH OVERRIDE;

By executing these commands, you will enable the ‘Agent XPs’ component and allow the creation of maintenance plans.

Solution 2: Enable Agent XPs using sp_configure

If you prefer not to start the SQL Server Agent service, you can enable the ‘Agent XPs’ component directly using the sp_configure command. Run the following commands in the SQL Server Management Studio query window:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE WITH OVERRIDE;

Enabling ‘Agent XPs’ using this method allows you to create maintenance plans without starting the SQL Server Agent service. This can be useful if you want to avoid running scheduled jobs that may be affected by starting the service.

It’s important to note that if you encounter any issues starting the SQL Server Agent service, you should check the SQLAgent.out and event log for further troubleshooting. These logs can provide valuable information to help resolve the problem.

Have you ever encountered the ‘Agent XPs Component is Turned Off’ error in your SQL Server environments? If so, please share your experience in the comments below.