SQL SERVER – SQL Server Agent Not Starting – Failed to Initialize SQL Agent Log (Reason: Access is Denied).

My lab machine is my playground. Other than my real-world client’s problem, many blogs have come from my lab machine because I keep on breaking something or another on my server. Once I found that I was unable to start SQL Server Agent service for one of the SQL Server instances. In this blog, we would learn how to fix error Failed to initialize SQL Agent log (reason: Access is denied)

As the first step of troubleshooting, I went to the SQL Server Errorlog folder to check for errors. The SQL Server Errorlog default path is,

<Drive>:\Program Files\Microsoft SQL Server\MSSQL1x.<InstanceID>\MSSQL\Log\SQLAGENT.OUT

Surprisingly when I went to the above location, I could not find the latest SQL Agent log. It only showed the older ones. We tried to start the SQL Agent service from service control manager and got the below error.

SQL SERVER - SQL Server Agent Not Starting - Failed to Initialize SQL Agent Log (Reason: Access is Denied). sqlagt-error-01

I still didn’t see the SQL Agent log. I tried to investigate the Event Viewer logs and found the below info in application event log.

Information SQLSERVERAGENT 102 Service Control SQLServerAgent service successfully stopped.
Error SQLSERVERAGENT 324 Alert Engine Failed to initialize SQL Agent log (reason: Access is denied).

Now, even the above error is not helping us much. But it revealed few vital clues for us.

Solarwinds
  • We have a permission issue.
  • And it is related to SQL Agent Log.

I went ahead and checked the required permission on the SQL Agent log folder and they all looked fine. From my experience, for any “Access Denied” message the next tool which I prefer is Process Monitor (Procmon). I will not deal with procmon on this blog. So, let’s try to start SQL Agent in CMD Prompt and use some startup parameters.

I tried starting SQL Agent using the below command,

<Drive>:\Program Files\Microsoft SQL Server\MSSQL1x.<InstanceID>\MSSQL\Binn\SQLAGENT.exe -i MSSQLSERVER -c -v

  • -i = Instance name
  • -c = console mode
  • -v = verbose mode

I got the below errors when we started SQL Agent using the above command.

SQL SERVER - SQL Server Agent Not Starting - Failed to Initialize SQL Agent Log (Reason: Access is Denied). sqlagt-error-02

This error looks similar to the error we saw in event viewer. But read again!! I’m sure you might have missing reading the first line.

Unable to rename C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ to C:\Program Files\Microsoft SQL Server\MSSQL13.1 (reason: Access is denied)

If you closely notice, SQL Agent service trying to rename the above folder! After spending a few moments decrypting this, I was finally able to get the logic. Hear me out and let me know if my logic was correct.

The current SQL Agent log file is called as “SQLAGENT.OUT” Every time we restart SQL Agent service, the SQLAGENT.OUT is renamed to SQLAGENT.1 and a new SQLAGENT.OUT file is created and eventually becomes the current log file.

In the above scenario, SQL Agent thinks the current log file name is- “MSSQLSERVER\MSSQL\Log

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

This is because we have DOT separator as an extension of a file. So, SQL Agent is trying to rename MSSQLSERVER\MSSQL\Log to .1 as per the renaming logic and thus failing. In an ideal scenario, it should look like,

Rename – C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT to
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAGENT.1

WORKAROUND/SOLUTION

It looks like the SQL Agent Log file name is missing from the string and we had to edit that to fix the issue. From my experience, I know that this information is stored in,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent\ErrorLogFile

When we went to the above-mentioned registry location we could that the SQL Agent log file name was missing.

SQL SERVER - SQL Server Agent Not Starting - Failed to Initialize SQL Agent Log (Reason: Access is Denied). sqlagt-error-03

After we added the log file name we were able to start SQL Agent successfully.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – All the Features not Listed While Installing SQL Server
Next Post
Driving up Database Coding Standards and Productivity with SQL Prompt

Related Posts

5 Comments. Leave new

  • Hi,

    I got the same issue but having all correct parameters file is created but SQL agent services are not able to start.

    Checked registry values and provide permission on eventlog in registery services, also checked system.evtx, checked by providing admin privileges to service accounts but no lunk.

    Have you face any such scenario or if you could guide for further troubleshooting.

    Thanks in Advance !!!

    Reply
  • I too ran into this issue. The registry parameters were correct, but in my case the solution was to just delete all existing SQLAGENT.* files. After some stop and starts the logs were just created and renamed as normal.

    Reply
  • I ran into this and Jim K’s suggestion worked. The server had been running via standard (user) service account for months. SQL was uninstalled, and reinstalled to run under a different account (a Managed Service Account). I’d get Access Denied on SQL Agent start up attempts. I think the agent service account was trying to rename the SQLAGENT.OUT and SQLAGENT.# files, which it likely did not have permission to do so. Deleting/relocating the files appears to take care of it; agent starts successfully. I know it’s an obscure situation, but the info here was very helpful and saved a lot of headaches.

    Reply
  • Roszaidah Mat Jihin
    February 1, 2020 9:19 am

    it still not working for my case

    Reply

Leave a Reply

Menu