Recently, a friend of mine encountered an issue while working with SQL Agent parameters. He reached out to me for assistance as he was unable to start the SQL Agent service. The error message he received in SQL Server Management Studio (SSMS) indicated a failure to initialize the SQL Agent log.
Here is the exact error message he encountered:
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
Upon further investigation, we discovered that the SQL Agent log file was missing. We checked the SQLAgent.out file in the same folder as the SQLERRORLOG file, but it was not present. We then examined the event log and found the following information:
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).
Based on this information, we concluded that the issue was related to the path setting for the SQL Agent log. However, when we attempted to open the properties of the management studio, we encountered another 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
According to this error message, we needed to enable the ‘Agent XPs’ parameter. To do this, we executed the following commands:
sp_CONFIGURE 'SHOW ADVANCE',1 GO RECONFIGURE WITH OVERRIDE GO sp_CONFIGURE 'AGENT',1 GO RECONFIGURE WITH OVERRIDE GO
After enabling the ‘Agent XPs’ parameter, we checked the properties again and discovered that the SQL Agent log file was set to a non-existent location (D drive, which had been removed). To resolve this, we used the following command:
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
Additionally, we located the relevant registry entry for the SQL Server Agent log path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL13.MSSQLSERVER \SQLServerAgent
The highlighted portion in the registry path represents the combination of the SQL version and instance name. For example, MSSQL13 corresponds to SQL Server 2016, while MSSQL12 is for SQL Server 2014.
After updating the path to a valid location, we were able to successfully start the SQL Server Agent.
Remember, if you encounter a similar issue with the SQL Server Agent log initialization, make sure to check the log file path, enable the ‘Agent XPs’ parameter, and update the path to a valid location if necessary.