Recently I worked on an issue where SQL Server Agent was failing to start. Before this there was some problems with the disks on the server and the databases were restored from backups.
I started looking for errors in few of the logs
- System Event Log – No errors
- SQL Server Error Log- No errors
Looked for the SQL Server Agent logs in the directory where SQL Server Error Logs are located but could not find any. The attempts to start the service from services.msc and SQL Server Configuration Manager failed with a generic error message.
Hence I took the SQL Server Agent service’s binary path from the service properties.
Then typed “…..Binn\SQLAGENT.EXE” -i MSSQLSERVER” the same command in the command prompt.
Error message “StartServiceCtrlDispatcher failed (error 6).” doesn’t help! Hence I started sqlserveragent.exe with the “-c” parameter which indicates SQL Server Agent is running in console mode.
“…..Binn\SQLAGENT.EXE” -i MSSQLSERVR -c”
Now the details came out!
SQL Server Agent is trying to rename D:\Data3\SQLAGENT.OUT to D:\Data3\SQLAGENT.1
It is failing to start since the file doesn’t exist because the drive isn’t there!
Now time to change the SQL Server Agent Log path. Tried executing the following command but it failed
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT’
Msg 15281, Level 16, State 1, Procedure sp_set_sqlagent_properties, Line 0
SQL Server blocked access to procedure ‘dbo.sp_set_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’, see “Surface Area Configuration” in SQL Server Books Online.
The only option left was to modify the SQL Server Agent Error Log path in the Registry.
Navigated to the following registry key and modified it to point to the correct path.
As expected now the SQL Server Agent started successfully.