Параметр конфигурации agent xps изменен с 1 на 0 выполните инструкцию reconfigure для установки

Problem

The other day we found that SQL Server Agent was stopped with the following message in SSMS “SQL Server Agent (Agent XPs disabled)” for one of our SQL Server instances, but the service was running according to the services console.  I tried to start the service from SQL Server Management Studio, but this did not work.  What is the issue and how do I fix this?

Solution

The other day I changed an implementation on one of our SQL Server instances. After the change, I started all of the SQL Server services from the services.msc console and everything started successfully. But when I launched SQL Server Management Studio, SQL Server Agent showed that is was not running with ‘Agent XPs disabled’ message as shown below. I tried to restart the service from SSMS, but it did not work. The interesting thing was that the service showed as ‘started’ when I was looking at it in the services.msc console.

As SQL Server Agent was not running in SSMS, we were not able to access any of the contents like jobs, error logs, etc… 

It appears, that this issue appears when ‘Agent XPs’ advance configuration option is disabled and set to 0 for the SQL Server configuration..

Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server.  When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables ‘Agent XPs’, but sometime it fails to enable or sets the value to 0 and then this issue will appear.

To fix this issue we should first set the ‘Agent XPs’ to 1 and then run RECONFIGURE to bring it into effect.

Step 1. 
Run sp_configure to check ‘Agent XPs’ value.

EXEC SP_CONFIGURE ‘Agent XPs’

Картинка с сайта: www.mssqltips.com

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’

Картинка с сайта: www.mssqltips.com

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
Run sp_configure again to check the value for Agent XPs. Here we can see the run value is set to 0.

Now we need to change this setting from 0 to 1 to run SQL Server Agent in SQL Server Management Studio.

EXEC SP_CONFIGURE ‘Agent XPs’,1

GO

RECONFIGURE

Картинка с сайта: www.mssqltips.com

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.

Next Steps

• Always use SQL Server Configuration Manager to start SQL Server services
• If you get this message in SQL Server, check the configuration settings to enable the Agent XPs
• Read the additional articles on SQL Server Agent

Recently, one of my clients encountered an error while trying to create a maintenance plan in SQL Server. They received the following 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.

This error occurs when the SQL Server Agent service is not running or when the ‘Agent XPs’ component is disabled. In order to fix this error, there are two possible solutions:

Solution 1: Start SQL Server Agent Service

The first solution is to start the SQL Server Agent service. This can be done using the SQL Server Configuration Manager or by running the following command in the SQL Server Management Studio query window:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE WITH OVERRIDE;

By executing these commands, you will enable the ‘Agent XPs’ component and allow the creation of maintenance plans.

Solution 2: Enable Agent XPs using sp_configure

If you prefer not to start the SQL Server Agent service, you can enable the ‘Agent XPs’ component directly using the sp_configure command. Run the following commands in the SQL Server Management Studio query window:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Agent XPs', 1;
RECONFIGURE WITH OVERRIDE;

Enabling ‘Agent XPs’ using this method allows you to create maintenance plans without starting the SQL Server Agent service. This can be useful if you want to avoid running scheduled jobs that may be affected by starting the service.

It’s important to note that if you encounter any issues starting the SQL Server Agent service, you should check the SQLAgent.out and event log for further troubleshooting. These logs can provide valuable information to help resolve the problem.

Have you ever encountered the ‘Agent XPs Component is Turned Off’ error in your SQL Server environments? If so, please share your experience in the comments below.

One of my clients has installed a new instance of SQL Server and then wanted to create maintenance plan. They were complaining about getting some error and we’re looking forward for my help to solve the same. As soon as he did right click on a Maintenance Plan under “Management” node and selected “New Maintenance Plan Wizard” using SQL Server Management Studio as shown below. Let us learn how to fix Agent XPs Component is Turned Off as Part of the Security Configuration for this Server error.

It gave below error.

Картинка с сайта: blog.sqlauthority.com

Here is the text of the message.

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Unable to execute requested command.
——————————
‘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)
——————————
BUTTONS:
OK
——————————

There are two ways to fix the problem.

1. Start SQL Server Agent Service.
   

   Картинка с сайта: blog.sqlauthority.com

   You can also start SQL Server Agent service by using the SQL Server Configuration Manager on start>run>services.msc applet.

1. Enable Agent XPs under sp_configure using below command in SQL Server Management Studio query window.

SP_CONFIGURE 'SHOW ADVANCE',1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'AGENT XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO

Here is the output

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once any of the above is followed, you should be able to create maintenance plan. You might ask, why someone would need use # 2 instead of # 1. It is important to remember that if you want to create maintenance plan without starting SQL Agent service (as it might cause jobs to run which are scheduled) then you would choose option 2. A very uncommon but possible scenario.

If you are not able to start SQL Server Agent service, then you may want to look at below blog

SQL SERVER – Unable to start SQL Server Agent – Failed to Initialize SQL Agent log

There might be some other error but SQLAgent.out and event log is a good place to start troubleshooting the issue. Do let me know if you encountered this error ever in your environments. Do leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)