SQL Server Agent service failed to come Online on the Active Node in a 2 Node Fail-over cluster.
Error Messages in the Event-logs.
[sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 435)
[sqagtres] StartResourceService: Failed to start SQLSERVERAGENT service. CurrentState: 1
[sqagtres] OnlineThread: Error 435 bringing resource online.
To understand the problem, we have to check the AgentError log file (SQLAGENT.OUT) but what i see that there is no file in the Log Folder.
This means that file is not getting generated so what next let start the service from comand prompt to ge some more information and to find any permission issues.
Refer the below output when service started from command prompt.
Microsoft (R) SQLServerAgent 10.50.4000.0
Copyright (C) Microsoft Corporation.
StartServiceCtrlDispatcher failed (error 6)
Error 6 menas “The handle is invalid”
Now to understand and find the problemtic area let we run the procmon (Process Monitor is a free tool from Windows Sysinternals, part of the Microsoft TechNet website. The tool monitors and displays in real-time all file system activity). Use the command prompt with Administrator rights and run the procmon. You will get something like as give below which seems that there is no permission issues.
“04:14:05.2397190 PM”,”SQLAGENT.EXE”,”12245″,”RegQueryValue”,”HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\SQLServerAgent\ErrorLogFile”,”SUCCESS”,”Type: REG_SZ, Length: 84, Data: G:\MSSQL\Log\”
Now to cross verify we have logged in another node and check the SQLAGENT.OUT file registry entry which is different from failing node.
In Working Node the value is I:\MSSQL\Log\SQLAGENT.OUT
In failing Node the value is I:\MSSQL\Log\
Now added the file name SQLAGENT.OUT in the registery.
Now the SQL agent service is started without any issue.