Log Shipping – NORECOVERY vs. STANDBY mode

Today I was working on updating operator emails on SQL Server and when i ran the script  it gave me given below error.

SQL Server blocked access to procedure 'dbo.sp_update_operator' of component 'Agent XPs' because this 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.

I then realized that, the sql server agent is not running on that server. Basically, Agent XPs option enables the SQL Server Agent extended stored procedures on the SQL Server and the reason why this error occurs is because Agent XPs is not enabled in SQL Server. Also, if this option is not enabled, SQL Server Agent node will not display in SQL Server Management Studio Object Explorer.

Resolutions

Method 1:
In this method, we will execute the script to enable Agent XPs. You need to execute the given below script in Query window.

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

Method 2:

In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. It will automatically enable Agent XPs.

 

Method 3 :

Start –> run –> services.msc

find the SQL Server agent service , right click and start.