SQLAGENT.OUT, the log file for SQL Agent, is usually the first place to investigate what could go wrong. However, when SQL Agent cannot start, a new SQLAGENT.OUT even is not generated at all. What should we investigate? If this kind of things happens, a possible reason is due to the file permission on SQLAGENT.OUT that is granted to SQL Agent service account.
The scenario could be that you are trying to start SQL Agent, but it doesn't start. Check SQLAGENT.OUT file, it is still an old SQLAGENT.OUT file that doesn't collect any new information. Every start of SQL Agent should generate a new SQLAGENT.OUT. An old log file indicates that SQLAGENT.OUT is not generated.
SQL Agent service account must have Read/Write permission to the file SQLAGENT.OUT. If the permission is denied, SQL Agent will fail to start; a new log file SQLAGENT.OUT will not be and cannot be generated. Most likely, this is because the permission of SQL Agent service account is removed, SQL Agent can't write anything into the file SQLAGENT.OUT.
SQL Agent service account is added in SQL Agent user group. All permissions are granted to the user group. The name of SQL Agent user group starts with "SQLServerSQLAgent" followed by the machine name and SQL instance name that are separated by the symbol $ as follows.
For example, SQL Agent user group is "SQLServerSQLAgentUser$YUHONGLI5$KJINST". If your SQL Server is installed in cluster, the user group will be the domain group that is specified during installation, not this name.
Once you know the user group for SQL Agent, you can check the security settings on the directory ".\Log" where SQLAGENT.OUT is generated. The security setting must contain Agent user group for the permission of Read/Write as shown in the example below.
If it doesn't exist, add Agent user group to the permission list. Then rename SQLAGENT.OUT to other file name or delete it if you don't need it, start Agent again. In this situation, Agent should start successfully. If it has some problem, at least SQLAGENT.OUT should be generated to contain more information.
<End of Post>
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
Note that folder changes for SQL server will not automatically propagate in any way to agent. We changed a test server SAN access totally with drive letters and all, and you need to have sql agent offline (in cluster) and edit registry at pretty much HKLM\software\Microsoft\Microsoft SQL Server\MSSQL10_50.[instancename]\SQL server agent, you will find 2 directory references under there which need to work, be accessible, AND have the rights.
AllTheGoodNamesAreInUse - I can't thank you enough for letting me know the registry entry. Save me a huge amount of pain!!!!!