This article explains how we can fix SQL Server error “Agent XPs Disabled”. Before we dive into troubleshooting and methods for fixing the error, let me explain about Microsoft SQL Server Agent and the Agent XPs configuration parameter.
SQL Server Agent and SQL Server Agent XPs
The SQL Server Agent is used to create automated database maintenance and database administration tasks that are called SQL jobs. SQL Server agent has the following components:
- Jobs
- Schedules
- Alerts
- Operators
Jobs
Jobs are a specific set of tasks performed on the specific schedule by the SQL Server Agent. SQL jobs can execute once, or multiple times based on the defined schedule. You can also run a job by executing the system stored procedure named sp_start_job. The execution status of the SQL jobs is monitored by the SQL Server agent.
SQL jobs can have multiple steps, and each step can perform different tasks. The SQL job executes at a specific schedule configured by the user. Apart from administrative tasks, we can use it to perform the following tasks:
- Execute SQL Server integration services packages
- Execute the T-SQL Query, Operating system commands (CmdExec), and PowerShell commands
- The execute SQL Server analysis service command or the query
-
Following Replication Jobs:
- Replication distributor
- Replication Merge
- Replication queue reader
- Replication Snapshot
- Replication Transactional-Log reader
Schedules
The schedule is a specific time when the SQL job will run. One or multiple jobs can run at the same time or we can apply the same schedule to multiple jobs. The SQL job execution can be scheduled under the following conditions:
- Recurring schedule (Daily, Weekly, Monthly)
- Executes only once at a specific date and time (One Time)
- When the CPU becomes idle
- Start at the job when the SQL Server agent service started
Alerts
Alert is an automated response to the specific event that occurred during the execution of the SQL job. For example, if we are running a job that executes an SSIS package that inserts a lot of data in the table and during the execution of the job, if the CPU utilization reaches the specific threshold, then we can notify the operators. Alerts can be configured on the following events:
- SQL Server performance condition
- A specific WMI event occurs on the computer where the SQL job is running
- Any SQL server condition occurs
Operators
An operator defines contact information for a person who is responsible for the maintenance of one or more instances of SQL Server. For instance, in some enterprises, the responsibility of managing the production databases and development database servers to separate teams of database admins. In these cases, we can create two separate operators for better manageability of alerts. Operators do not contain any security information and do not define a security principal.
SQL Server notifies operators of alerts using the following methods:
- Pager (through e-mail)
We can also configure the fail-safe operator, which is used if all other operator notifications fail. We can configure the fail-safe operator after you configure one or more operators.
When we install the SQL Server, by default, the agent service does not start automatically, and the service startup type is “Manual”. We must start it manually and set the service startup type to “Automatic”.
In order to create a new SQL Server maintenance plan or SQL Server job, the SQL Server Agent service must be running, and we must enable the SQL Server Agent XPs configuration parameter. When we enable Agent XPs, it enables the specific extended stored procedure that is used by SQL Server Agent services to create maintenance plans and SQL jobs. The Agent XPs allow the SQL Server Agent to perform the privileged actions which execute externally to the SQL Server under the security context of the SQL Server Agent service account.
Two possible values of the Agent XPs configuration parameter are as following:
- Zero (0): This value indicates that SQL Server extended stored procedure OR Agent XPs are disabled
- One (1): This value indicates that the SQL Server extended stored procedure OR Agent XPs is enabled
We can change the configuration settings without restarting the SQL Server services or SQL Server Agent service.
Problem statement
After installation of SQL Server on new servers, users were unable to create the maintenance plans or SQL jobs. When they try to create a maintenance plan or SQL Server job, they were facing the error: Agent XPs Disabled. See the following image:
There are two possible root causes of the issue:
- The SQL Server Agent service is not running
- The SQL Server Agent job is running, but the configuration parameter Agent XPs is disabled
SQL Server Agent service is not running
You might face this when the SQL Server service is not running. See the following image:
To fix the issue, you must start the Agent service from the SQL Server Configuration Manager.
To do that, open SQL Server Configuration Manager, select SQL Server Services, right-click on SQL Server Agent (MSSQLSERVER), and choose Start. See the following image:
Alternatively, you can start the agent service from services. To do that, open Control Panel | Administrative tools locate and open Services, then in the Services window select the SQL Server Agent (MSSQLSERVER) service and click on Start. See the following image:
You can start from SQL Server Management Studio as well. To do that, from Object Explorer, expand Integration Services Catalogs, right-click on SQL Server Agent and select Start. See the following image:
Once the SQL Server Agent service is started, you will be able to create the maintenance plan and SQL jobs.
The configuration parameter Agent XPs is disabled
When we start the SQL Server Agent service, by default, the component Agent XPs should be enabled automatically, but sometimes, it is not, and then we have to manually enable it by changing the values of the configuration parameter, under those circumstances, you might receive an error Agent XPs disabled.
If SQL Service is running and you still receive the Agent XPs disabled error, then you should check the value of the Agent XPs configuration parameter. To view the value of it, execute the following query:
|
1 2 3 |
use master go select * from sys.configurations where name='Agent XPs' |
Following is the output:
As you can see in the above screenshot, the value of Agent XPs is 0, which means that the Agent XPs component is disabled. You can also see the tiny red cross icon in Object Explorer of SQL Server Management Studio:
To enable Agent XP, we must change the configuration value using the exec sp_configure command. The sp_configure is the system stored procedure, and to execute it, the user must be a member of the sysadmin fixed server role on the SQL Server instance.
The Agent XP is an advanced configuration parameter; hence first, we must enable the advanced options. To do that, execute the following query:
|
1 2 3 4 5 6 |
use master go exec sp_configure 'Show advanced options',1 Go reconfigure with override go |
See the following screenshot:
Once the advance configuration option is enabled, execute the following query to enable the Agent XP:
|
1 2 3 4 5 6 |
use master go exec sp_configure 'Agent XPs',1 Go reconfigure with override go |
See the following screenshot:
Execute the following query to verify that the values of the configuration parameters have been changed:
|
1 2 3 |
use master go select * from sys.configurations where name in ('Agent XPs','Show advanced options') |
See the following image:
As you can see in the above image, the value of the configuration parameters Agent XPs and Show advanced options have been changed from 0 to 1, which indicates that both configuration parameters are enabled. Once the Agent XPs option is enabled, you can see that the tiny red cross has been disappeared from Object Explorer:
For security purposes, it is always advisable to keep the Show advanced options parameter disabled. Execute the following query to disable the Show advanced option configuration parameter:
|
1 2 3 4 5 6 |
use master go exec sp_configure 'Show advanced options',0 Go reconfigure with override go |
Once the Agent XPs component is enabled, you should be able to create the SQL jobs and maintenance plans.
Conclusion
In this article, I have explained about SQL Server Agent service, its components, and Agent XPs configuration parameters. Moreover, I have explained the possible root causes of the error Agent XPs disabled and how to fix them.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022