8

On a clustered Windows 2012 R2 server with SQL Server 2014 Enterprise.

Just upgraded an instance from 2014 SP1 CU4 to 2016 RTM and now getting this error when attempting to start the SQL Server Agent.

SQL SERVER AGENT LOG

2016-06-06 11:53:58 - ? [100] Microsoft SQLServerAgent version 13.0.1601.5 (X64 unicode retail build) : Process ID 10884
2016-06-06 11:53:58 - ? [495] The SQL Server Agent startup service account is DOMAIN\USERNAME.
2016-06-06 11:54:28 - ! [150] SQL Server does not accept the connection (error: 65535). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
2016-06-06 11:54:28 - ! [000] Unable to connect to server 'SERVERNAME\INSTANCENAME'; SQLServerAgent cannot start
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001] 
2016-06-06 11:54:33 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] 
2016-06-06 11:54:33 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001] 
2016-06-06 11:54:33 - ! [382] Logon to server 'SERVERNAME\INSTANCENAME' failed (DisableAgentXPs)
2016-06-06 11:54:33 - ? [098] SQLServerAgent terminated (normally)

Windows Application Log

SQLServerAgent could not be started (reason: Unable to connect to server 'A08SQL-EDI\EDI'; SQLServerAgent cannot start).

The agent starts and runs for about 30 seconds then dies with the above error. Anyone run into this issue? And do you know how to resolve it?

0

3 Answers 3

4

https://support.microsoft.com/en-nz/kb/3185365

ODBC issue for 13 version. please apply the 13.1

1
12

Problem resolved by adding an alias to the instance in the SQL Server Agent Configuration.

The alias was defined using the fully qualified domain name of the server. Example:servername.domain.biz\instance_name otherwise the agent will still error out and shutdown.

enter image description here

Also, the Agent was disabled in sp_configure

sp_configure 'Agent XPs', 1
go

reconfigure with override
go

So reenabled it.

3
  • 1
    This is a work-around; the actual solution is Illidan-zhu's answer dba.stackexchange.com/a/152741/29371 , and/or a subsequent CU/SP update from MS, which I'm currently testing.
    – NateJ
    Jan 11, 2017 at 1:17
  • CU2, CU3, and even SP1 still don't fix the problem. In fact, according to this article, SP1 overwrites the ODBC update (i.e. Illidan-zhu's answer). So, best bet is to ensure that, if using multiple named instances on a cluster, you keep that ODBC driver updated.
    – NateJ
    Jan 11, 2017 at 21:00
  • Where can I access that screen?
    – skan
    Apr 18 at 17:56
-1

This is fixed for me by following the below:

Check TCP/IP protocol in sql configuration manager has what port num in IP ALL. If there is a port mentioned in that then the sql server agent properties in ssms should have connection parameter as ,.

Restarting the services after setting these up fixed the issue/error for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.