How to fix the “Agent XPs disabled” error

This article explains the steps to fix the SQL Server agent error “Agent XPs disabled” using T-SQL scripts.

In Microsoft SQL Server, “Agent XPs” refers to the configuration option that controls the surface area of SQL Server Agent extended stored procedures. SQL Server Agent is a component of SQL Server that automates various administrative tasks, such as job scheduling, alerting, and performance monitoring. Extended stored procedures (XPs) are functions in a dynamically linked library (DLL) that can be called from Transact-SQL just like regular stored procedures.

Agent XPs Configuration:

  • The Agent XPs configuration option controls whether SQL Server Agent extended stored procedures are available for use. When Agent XPs is enabled, SQL Server Agent functionality is accessible.

Purpose of Agent XPs:

  • Enabling Agent XPs is necessary if you want to use SQL Server Agent to schedule and automate tasks through SQL Server Agent jobs. These jobs can include activities like database backups, maintenance tasks, or running custom T-SQL scripts.

Security Considerations:

  • Enabling Agent XPs increases the surface area of SQL Server by providing additional functionality. It’s important to carefully consider security implications and follow best practices, especially in production environments.

Step 1:

Run sp_configure to check ‘Agent XPs‘ value.

EXEC SP_CONFIGURE ‘Agent XPs’

You might see the error - Agent XPs disabled (as shown below) after installing a new SQL Server instance.

Step 2:

The above screenshot shows that advanced options is not enabled on this instance, so we must first enable advanced option to see all of the advanced configuration values.

EXEC SP_CONFIGURE ‘show advanced options’,1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE ‘show advanced options’

You can see ‘show advanced options’ is set to 1 this means that advanced options are enabled and we can see and change the values.

Step 3:

Once the advance configuration option is enabled, execute the following query to enable the Agent XP:

sp_configure ‘show advanced options’, 1;
RECONFIGURE;
sp_configure ‘Agent XPs’, 1;
RECONFIGURE;

Step 4:

Now restart your SQL Server Agent service from SQL Server Configuration Manager. This time the service should come up and we can successfully access all of the content for SQL Server Agent.

Disabling Agent XPs:

  • If needed, you can disable Agent XPs using the following Transact-SQL commands:

              sp_configure ‘Agent XPs’, 0;
              RECONFIGURE

 

Understanding and Monitoring Active Sessions in SQL Server

How to Find Long Running Queries in SQL Server

Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment