This was a troublesome error, because there is not much information available on what is going wrong. I looked the application log, SQL Agent Error Log, cluster logs; but neither gave me much details on what was causing the issue. So after research on internet found an article by Tom Green (Blog). However after looking at the said keys in [HKLM]\Software\Microsoft\Microsoft SQL Server\MSSQL.MSSQLServer\SQLServerAgent\ values WorkingDirectory and ErrorLogFile were already correct for me; however agent still refused to start up.
So another suggestion Tom makes is it might be permission issue on directory. Which were also correct; slowly running out of options…. but …
It dawned on me this key was recently imported because of corruption issue; and with Tom’s article’s hint, I figured what if permissions in registry got deleted.
Sure enough SQL Agent services had lost permission to [HKLM]\Software\Microsoft\Microsoft SQL Server\MSSQL.MSSQLServer\SQLServerAgent\ hive. After adding the agent account back in with Full Control, services started without problems.
- Run regedit.msc
- Navigate to [HKLM]\Software\Microsoft\Microsoft SQL Server\MSSQL.MSSQLServer\
- Right click on SQLServerAgent, go to Permissions.
- Click Add…
- If using Service SIDS …
- Change Location from Domain to Local Computer
- If using Default Instance …
- Type “NT Service\SQLServerAgent”
- If using Named Instance …
- Type “NT Service\SQLAgent$InstanceName”
- Click OK.
- If using Domain Service Accounts …
- Type in Domain\AccountName
- Click OK.
- Select Full Control in Permissions Dialog Box.
- Click OK.
Restart SQL Agent, should work without problem.
SQL Canada (SQLCAN) 