How to Solve “Agent XPs disabled” error

This article explains however we will fix SQL Server error “Agent XPs Disabled”. Before we have a tendency to dive into troubleshooting and strategies for fixing the error, let Maine justify regarding Microsoft SQL Server Agent and therefore the Agent XPs configuration parameter.

SQL Server Agent and SQL Server Agent XPs:
The SQL Server Agent is employed to make machine-driven info maintenance and info administration tasks that area unit referred to as SQL jobs. SQL Server agent has the subsequent components:

• Jobs
• Schedules
• Alerts
• Operators
• Jobs

Jobs:

Jobs area unit a selected set of tasks performed on the precise schedule by the SQL Server Agent. SQL jobs will execute once, or multiple times supported the outlined schedule. You’ll be able to conjointly run employment by death penalty the system keep procedure named sp start job. The execution standing of the SQL jobs is monitored by the SQL Server agent.

SQL jobs will have multiple steps, and every step will perform totally different tasks. The SQL job executes at a selected schedule designed by the user. excluding body tasks, we will use it to perform the subsequent tasks:

1. Execute SQL Server integration services packages
2. Execute the T-SQL question, software commands (CmdExec), and PowerShell

Commands

• The execute SQL Server analysis service command or the question.
• Following Replication Jobs:
• Replication distributor
• Replication Merge
• Replication queue reader
• Replication pic
• Replication Transactional-Log reader

Schedules:

The schedule may be a specific time once the SQL job can run. One or multiple jobs will run at constant time or we will apply constant schedule to multiple jobs. The SQL job execution may be regular beneath the subsequent conditions:

Recurring schedule (Daily, Weekly, Monthly)
Executes one time at a selected date and time (One Time)
When the central processing unit becomes idle
Start at the work once the SQL Server agent service started

Alerts:

Alert is an automatic response to the precise event that occurred throughout the execution of the SQL job. for instance, if we have a tendency to area unit running employment that executes AN SSIS package that inserts a great deal of information within the table and through the execution of the work, if the central processing unit utilization reaches the precise threshold, then we will inform the operators. Alerts may be designed on the subsequent events:

1. SQL Server performance condition
2. A specific WMI event happens on the pc wherever the SQL job is running
3. Any SQL server condition happens

Operators:

An operator defines contact info for an individual United Nations agency is accountable for the upkeep of 1 or additional instances of SQL Server. For example, in some enterprises, the responsibility of managing the assembly info’s and development info servers to separate groups of database admins. In these cases, we will produce 2 separate operators for higher traceableness of alerts. Operators don’t contain any security info and don’t outline a security principal.

SQL Server notifies operators of alerts victimization the subsequent methods:

• E-mail
• Pager (through e-mail)
  
  

We can conjointly piece the fail-safe operator that is employed if all alternative operator notifications fail. We will piece the fail-safe operator once you piece one or additional operators.

When we install the SQL Server, by default, the agent service doesn’t begin mechanically, and therefore the service startup kind is “Manual”. We have a tendency to should begin it manually and set the service startup kind to “Automatic”.

In order to make a replacement SQL Server maintenance set up or SQL Server job, the SQL Server Agent service should be running, and that we should modify the SQL Server Agent XPs configuration parameter. once we modify Agent XPs, it allows the precise extended keep procedure that’s utilized by SQL Server Agent services to make maintenance plans and SQL jobs. The Agent XPs permit the SQL Server Agent to perform the privileged actions that execute outwardly to the SQL Server beneath the safety context of the SQL Server Agent service account.

Two doable values of the Agent XPs configuration parameter area unit as following:

• Zero (0): This price indicates that SQL Server extended keep procedure OR Agent XPs area unit disabled
• One (1): This price indicates that the SQL Server extended keep procedure OR Agent XPs is enabled

We can modification the configuration settings while not restarting the SQL Server services or SQL Server Agent service.

Problem statement:

After installation of SQL Server on new servers, users were unable to make the upkeep plans or SQL jobs. Once they attempt to produce a maintenance set up or SQL Server job, they were facing the error: Agent XPs Disabled.

See the subsequent image:

There area unit 2 doable root causes of the issue:

1. The SQL Server Agent service isn’t running
2. The SQL Server Agent job is running, however the configuration parameter Agent XPs is disabled

SQL Server Agent service isn’t running:

You might face this once the SQL Server service isn’t running. See the subsequent image:

To fix the problem, you need to begin the Agent service from the SQL Server Configuration Manager.

To do that, open SQL Server Configuration Manager, choose SQL Server Services, right-click on SQL Server Agent (MSSQLSERVER), and opt for begin. See the subsequent image:

Alternatively, you’ll be able to begin the agent service from services. To do that, open electrical device | body tools find and open Services, then within the Services window choose the SQL Server Agent (MSSQLSERVER) service and click on on begin. See the subsequent image:

You can begin from SQL Server Management Studio furthermore. To do that, from Object person, expand Integration Services Catalogs, right-click on SQL Server Agent and choose begin. See the subsequent image:

Once the SQL Server Agent service is started, you’ll be able to produce the upkeep set up and SQL jobs.

The configuration parameter Agent XPs is disabled:

When we begin the SQL Server Agent service, by default, the element Agent XPs ought to be enabled mechanically, however typically, it is not, and so we’ve to manually modify it by dynamical the values of the configuration parameter, beneath those circumstances, you would possibly receive a slip-up Agent XPs disabled.

If SQL Service is running and you continue to receive the Agent XPs disabled error, then you must check the worth of the Agent XPs configuration parameter. to look at the worth of it, execute the subsequent query:

Following is that the output

As you’ll be able to see within the higher than screenshot, the worth of Agent XPs is zero, which suggests that the Agent XPs element is disabled. you’ll be able to conjointly see the small NGO icon in Object person of SQL Server Management Studio:

To modify Agent XP, we have a tendency to should modification the configuration price victimization the executive department sp. configure command. The sp. configure is that the system keeps procedure, and to execute it, the user should be a member of the sysadmin fastened server role on the SQL Server instance.

The Agent XP is a sophisticated configuration parameter; thence initial, we have a tendency to should modify the advanced choices. To do that, execute the subsequent query:

use master
go
exec sp_configure ‘Show advanced options’,1
Go
reconfigure with override
go

See the subsequent screenshot:

Show Advanced choices is enabled
Once the advance configuration possibility is enabled, execute the subsequent question to modify the Agent XP:

Show Advanced choices is enabled
Once the advance configuration possibility is enabled, execute the subsequent question to modify the Agent XP:

use master

See the subsequent screenshot:

Execute the subsequent question to verify that the values of the configuration parameters are changed:

See the subsequent image:

As you’ll be able to see within the higher than image, the worth of the configuration parameters Agent XPs and Show advanced choices are modified from zero to one, that indicates that each configuration parameters area unit enabled. Once the Agent XPs possibility is enabled, you’ll be able to see that the small NGO has been disappeared from Object Explorer:

For security functions, it’s continually sensible to stay the Show advanced choices parameter disabled. Execute the subsequent question to disable the Show advanced possibility configuration parameter:

Once the Agent XPs element is enabled, you must be able to produce the SQL jobs and maintenance plans.

Conclusion:

In this article, I actually have explained regarding SQL Server Agent service, its elements, and Agent XPs configuration parameters. Moreover, I actually have explained the doable root causes of the error Agent XPs disabled and the way to repair them.