Unable to start SQL Agent on Server 2014 Standard

Question

Hello.

I have inherited responsibility for a SQL Server 2014 Standard database. One of my first tasks is to get the SQL Agent to start. Currently it is unable to do so. The Event Log has the following entry:

Event ID: 324 Alert Engine
Failed to initialize SQL Agent log (reason: Access is denied).

I have checked the Services snap-in, and the the SQL Server Agent is configured to Log On As NTSERVICE\SQLSERVERAGENT

I ran the script shown below to enable the AgentXP's parameter:

sp_CONFIGURE 'SHOW ADVANCE',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_CONFIGURE 'AGENT',1
GO
RECONFIGURE WITH OVERRIDE
GO

However even with that, the agent will not start. What other steps do I need to take to correct this problem?

Answer 1

Hi, Seems to be a permission problem. In SQL configuration manager change the account for the agent to some builtin like Networkservice. Try start it again. Then set it back to NT Service\SQLSERVERAGENT. Maybe someone has done change from Windows services on this service, so the permission has been lost. It is important to always use SQL Configuration Manager for this kind of settings.

Regard Joel

Answer 2

Hello.

Thank-you for your reply. I did your suggested steps, and the results were the following:

1. When going from Networkservice back to SQLSERVERAGENT, the system reported the object SQLSERVERAGENT could not be found.
2. I used a domain account that has local Administrator access as the login for the SQL Agent service, and that did work.

How can I recover the SQLSERVERAGENT object, and get it back to operational status?

Answer 3

SQL Agent creates a log file which should have details at:

C:\Program Files\Microsoft SQL Server{version.instance}\MSSQL\Log\SQLAGENT.OUT

Answer 4

Hello.

I checked the SQLAGENT.LOG in question, and the following is the extent of the entries within it:

2019-03-29 09:19:15 - ? [100] Microsoft SQLServerAgent version 12.0.5000.0 (X64 unicode retail build) : Process ID 2728
2019-03-29 09:19:15 - ? [495] The SQL Server Agent startup service account is NT Service\SQLSERVERAGENT.
2019-03-29 09:19:15 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2019-03-29 09:19:15 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2019-03-29 09:19:15 - ? [101] SQL Server BLACKBOARD-DEV version 12.00.5000 (0 connection limit)
2019-03-29 09:19:15 - ? [102] SQL Server ODBC driver version 11.00.5643
2019-03-29 09:19:15 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is
2019-03-29 09:19:15 - ? [310] 12 processor(s) and 16384 MB RAM detected
2019-03-29 09:19:15 - ? [339] Local computer is <HOSTNAME> running Windows NT 6.2 (9200)
2019-03-29 09:19:15 - ? [432] There are 8 subsystems in the subsystems cache
2019-03-29 09:19:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2019-03-29 09:19:16 - + [475] Database Mail is not enabled for agent notifications.
2019-03-29 09:19:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2019-03-29 09:39:13 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2019-03-29 09:39:15 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2019-03-29 09:39:15 - ? [098] SQLServerAgent terminated (normally)

Answer 5

Hi seems like permission issue on sql agent log folder, goto your sql agent log folder(C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log) right click on log folder and select properties then select security tab and check the permissions of sqlserveragent account if its listed there, it should have read write permission as sqlagent creates a new sqlagent.out everytime its restarted and renames the old one to sqlagent.1 which it might be failing to do in your case.
If sqlserveragent account is not listed add it using Edit button in same window and then Add and give read write permissions on Log folder and retry.