I recently uninstalled and install SQL Server 2008 R2 on my machine. Then, I found out that SQL Agent fails to start. It starts and stops immediately. There were no errors in Event logs or SQL logs. I am unable to open the SQLAgent.1 or SQLAgent.OUT log files. Whenever I tried to open these files, windows prompts with “Unable to Open” message.
I check the permissions. System, Administrators, SQLServerMSSQLUser$……… as well as SQLServerSQLAgentUser$….. all had the appropriate Read and Write permissions.
Then, I found a Microsoft blog explaining old SQLAgent.OUT log file may not be accessible by the SQLAgentUser and therefore, blocking the SQLAgent from starting.
To solve this:
- Make sure SQLServerSQLAgentUser$…………. has ReadWrite access to the Log folder. The format of username follows the pattern “SQLServerSQLAgentUser$MachineName$InstanceName”.
- Delete or rename all old SQLAgent.x log files. The log files are usually under Program Files\Microsoft SQL Server\MSSQL\LOG. However, the location can be changed.
- Restart the SQL Agent.
That worked for me. SQL Agent started successfully. A new SQLAgent.OUT log file was created.