Sql-server – SQL Server Agent will not start – times out

sql server 2014sql-server-agent

On my localhost, I am running 2014 (not express). I need to create a job, and for that, I need the Agent service to run. But I can't get it started.

I have been googling the snot out of this, but so far no luck.

In the Windows Logs, under System, I have only this:

The SQL Server Agent (SQL2014) service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

and

A timeout was reached (30000 milliseconds) while waiting for the SQL Server Agent (SQL2014) service to connect.

and

The server {1B1F472E-3221-4826-97DB-2C2324D389AE} did not register with DCOM within the required timeout.

Not very helpful. I found the SQLAGENT.OUT file, but there is nothing recent in it. The last entry is from over a month ago. None the less, this is the contents of the file:

2017-10-26 15:35:51 - ? [100] Microsoft SQLServerAgent version 12.0.5207.0 (X64 unicode retail build) : Process ID 4980
2017-10-26 15:35:51 - ? [495] The SQL Server Agent startup service account is NT Service\SQLAgent$SQL2014.
2017-10-26 15:35:56 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2017-10-26 15:35:57 - ? [000] 
2017-10-26 15:35:57 - ? [101] SQL Server EBDEVELOPER\SQL2014 version 12.00.5207 (0 connection limit)
2017-10-26 15:35:57 - ? [102] SQL Server ODBC driver version 11.00.2100
2017-10-26 15:35:57 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is EBDEVELOPER\SQL2014
2017-10-26 15:35:57 - ? [310] 8 processor(s) and 16315 MB RAM detected
2017-10-26 15:35:57 - ? [339] Local computer is EBDEVELOPER running Windows NT 6.2 (9200) 
2017-10-26 15:35:57 - ? [432] There are 13 subsystems in the subsystems cache
2017-10-26 15:35:58 - ? [129] SQLAgent$SQL2014 starting under Windows NT service control
2017-10-26 15:35:58 - + [475] Database Mail is not enabled for agent notifications.
2017-10-26 15:35:58 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2017-10-26 15:37:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:38:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:39:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:40:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:41:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'xxxxx', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2017-10-26 15:41:33 - ? [131] SQLAgent$SQL2014 service stopping due to a stop request from a user, process, or the OS...
2017-10-26 15:41:35 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2017-10-26 15:41:35 - ? [098] SQLServerAgent terminated (normally)

(The xxxxx was the name of the former developer who used to use this laptop. I am his replacement. For his sake, I removed his name.)

Following some advice I found on other threads, I ran this against the master database:

sp_configure 'show advanced options','1'
RECONFIGURE
go

sp_configure 'Agent XPs','1'
RECONFIGURE
go

It returned this:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.

Service still would not start (although it looks like nothing changed, so I wasn't surprised).

I've tried to start it from within the normal Windows Services, and from within SQL Server Configuration Manager.

In the Properties, I have tried to logon as all of the options on the Built-in account dropdown. I've also tried the admin account I use to connect to the DB from within SSMS.

The service is set to Start Type automatic.

I'm not sure what else to try. Any suggestions?

Best Answer

Possibly the SQL Server Agent service was previously running under the old developer's account 'xxxxx'.

The developer left and his/her account was deleted in Active Directory.

The service will no longer start.

Solution

  1. Start the SQL Server Configuration Manager
  2. Right-click the SQL Server Agent (INSTANCE_NAME) service to bring up the properties.
  3. In the properties change the service account to start with either your Windows account or switch to "Local System".
  4. Close the properties
  5. Start the service in Configuration Manager.

Note:
It is important to change these settings using the SQL Server Configuration Manager and not with the normal Windows Services Management Console. The reason being that SQL Server Configuration Manager will ensure that all the directory/file permissions and SQL Server permissions are set correctly.

Related Question