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

My friend was researching with SQL Agent parameters and after sometime he called me and told that he is not able to start SQL Agent service. Here was the error in SSMS which is about failed to initialize a SQL Agent log.

Text is below:

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to start service SQLSERVERAGENT on server SQLSERVER2016. (mscorlib)
——————————
ADDITIONAL INFORMATION:
The SQLSERVERAGENT service on SQLSERVER2016 started and then stopped. (ObjectExplorer)
——————————
BUTTONS:
OK
——————————

I asked to check SQLAgent.out file in the same folder which has a SQLERRORLOG file, but he said that nothing was there. Then looked around in event log and found below.

Log Name:      Application
Source:        SQLSERVERAGENT
Date:          2/23/2016 12:35:14 AM
Event ID:      324
Task Category: Alert Engine
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      sqlserver2016
Description:   Failed to initialize SQL Agent log (reason: The system cannot find the path specified).

At this point, I told my friend that we need to find the path setting for SQL Agent log and fix it. But we were unable to open the properties of management studio with below error.

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
—————————–
SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ 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’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
——————————
BUTTONS:
OK
——————————

As per error message, we have enabled ‘Agent XPs’ parameter. For this is an advanced setting, we need to enable “show advanced options” before changing the value for “Agent XPs”

sp_CONFIGURE 'SHOW ADVANCE',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_CONFIGURE 'AGENT',1
GO
RECONFIGURE WITH OVERRIDE
GO

Checked properties and found below

Since there was no D drive (it was a USB which he removed), we need to change that. Here is the command to fix that.

USE [msdb] GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAgent.out'
GO

Here is the location in the registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent

Highlighted piece is the combination of SQL version and instance name. MSSQL13 stands for SQL Server 2016. MSSQL12 is for SQL Server 2014 and so on.

Once we change the path to a valid location, SQL Server Agent was started.

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