SQL Server Agent is a crucial component in automating database maintenance and administration tasks. It allows users to create SQL jobs that can be scheduled to run at specific times or in response to certain events. However, sometimes users may encounter the error “Agent XPs Disabled” when trying to create maintenance plans or SQL jobs. This article will explain the concept of Agent XPs and provide step-by-step instructions on how to enable it in SQL Server.

Understanding SQL Server Agent and Agent XPs

SQL Server Agent is responsible for executing SQL jobs, which are sets of tasks performed on a specific schedule. These tasks can include executing SQL queries, running operating system commands, or even performing actions in other SQL Server components like Analysis Services or Replication. SQL jobs can be scheduled to run at recurring intervals or triggered by specific events.

Agent XPs is a configuration parameter that allows SQL Server Agent to perform privileged actions outside of the SQL Server environment. When Agent XPs is enabled, SQL Server Agent can execute tasks under the security context of its service account. However, if Agent XPs is disabled, users will encounter the “Agent XPs Disabled” error when trying to create SQL jobs or maintenance plans.

Fixing the “Agent XPs Disabled” Error

There are two possible root causes for the “Agent XPs Disabled” error:

1. The SQL Server Agent service is not running
2. The Agent XPs configuration parameter is disabled

1. Starting the SQL Server Agent service

If the SQL Server Agent service is not running, users need to start it manually. This can be done through the SQL Server Configuration Manager or the Services window in the Control Panel. Once the SQL Server Agent service is started, users will be able to create SQL jobs and maintenance plans.

2. Enabling the Agent XPs configuration parameter

If the SQL Server Agent service is running but users still encounter the “Agent XPs Disabled” error, it means that the Agent XPs configuration parameter is disabled. To enable it, users need to execute the following steps:

1. Open SQL Server Management Studio and connect to the SQL Server instance
2. Execute the following query to enable advanced options:

USE master;
EXEC sp_configure 'Show advanced options', 1;
RECONFIGURE WITH OVERRIDE;

3. Execute the following query to enable the Agent XPs configuration parameter:

USE master;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE WITH OVERRIDE;

After executing these queries, users can verify that the values of the configuration parameters have been changed by executing the following query:

USE master;
SELECT * FROM sys.configurations WHERE name IN ('Agent XPs', 'Show advanced options');

If the values of the configuration parameters have been changed to 1, it means that both Agent XPs and advanced options are enabled. Users should now be able to create SQL jobs and maintenance plans without encountering the “Agent XPs Disabled” error.

Conclusion

In this article, we have discussed the importance of SQL Server Agent and the Agent XPs configuration parameter. We have also provided step-by-step instructions on how to enable Agent XPs in SQL Server to fix the “Agent XPs Disabled” error. By following these instructions, users can ensure that SQL jobs and maintenance plans can be created without any issues.