Posts Tagged ‘Agent XPs component is turned off’

Today I was working on Maintenance Plans of SQL Server and I had to schedule a database backup on daily basis for a client. But once I right clicked on Management >> Maintenance Plan and selected New Maintenance Plan using SSMS, it gave me given below error.

Let’s discuss this in detail:

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. (Microsoft.SqlServer.Management.MaintenancePlanWizard)

Agent Xps component is turned off.1.1


Basically, Agent XPs option enables the SQL Server Agent extended stored procedures on the SQL Server and the reason why this error occurs is because Agent XPs is not enabled in SQL Server. Also, if this option is not enabled, SQL Server Agent node will not display in SQL Server Management Studio Object Explorer.

There are two methods to resolve it.

Method 1:
In this method, we will execute the script to enable Agent XPs. You need to execute the given below script in Query window.

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

Agent Xps component is turned off.1.2

Method 2:
In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. It will automatically enable Agent XPs.

Agent Xps component is turned off.1.3

Note : There is no need to stop and restart the server, these settings take immediate effect.

Read Full Post »