0

There is a 3 node SQL Server Failover Cluster with 3 instances running on same node at the moment. SQL Server version is 2012 with SP3.

At a particular time of the day at 4 PM daily, SQL Agent hangs and following error comes in SQL Agent Log:

2019-12-17 20:17:53 - + [260] Unable to start mail session.

2019-12-17 20:17:53 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

2019-12-17 20:17:53 - + [408] SQL Server XXXXXXXX is clustered - AutoRestart has been disabled

2019-12-18 16:20:30 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]

2019-12-18 16:20:30 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

2019-12-18 16:20:30 - ! [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]

2019-12-18 16:20:30 - ! [382] Logon to server 'XXXXX\XXXXXXX' failed (ConnUpdateStartExecutionDate)

2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]

2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]

2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

2019-12-18 16:20:31 - ! [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]

2019-12-18 16:20:31 - ! [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]

Also if I query sp_readerrorlog through ssms, following error comes:

Msg 22004, Level 16, State 1, Line 0 Failed to open loopback connection. Please see event log for more information.

Msg 22004, Level 16, State 1, Line 0 Error log location not found.

I have found the same symptoms in the support page of Microsoft: https://support.microsoft.com/en-in/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error

But the above issue in the Microsoft Support Page is for SQL Server 2016, where the resolution is to update the ODBC Driver 13.1

But in my case it is SQL Server 2012, and in ODBC Data Source > Drives - no ODBC version is to be found other than Native Client.

This issue is occurring only on one of the instance among the total 3 clustered instances on the server.

  • 1
    What does the SQL Server Error Log show for that time? – David Browne - Microsoft Dec 18 '19 at 15:18
  • Cannot open the error log through SSMS, nor through query. When checked at the physical location file, no entry is in the error log file after 4 PM. Only after restart, it works normally. But will occur again at the same time. – Shaurya S Dec 18 '19 at 15:23
  • Is there a shutdown message in the error log before 4 PM? – David Browne - Microsoft Dec 18 '19 at 16:39
  • No, no shutdown messages. Nothing is shutting off. SQL Server is running fine, application are able to connect. Only issue is with SQL Agent, which goes in hang state. Jobs running get hanged. And in Agent Logs can find the above error messages. Symptoms are same as given in Microsoft support page. – Shaurya S Dec 18 '19 at 18:19
  • What happens on your network at 4PM? Maybe some maintenance? – Alex Dec 18 '19 at 20:45
0

We had similar issues on one of our clusters and found that "Allow the computer to turn off this device to save power" was turned on for the primary NIC. Id try turning that setting off on any/all NICs. I posted as "a possible" answer so I could post the picture:

enter image description here

| improve this answer | |
  • If this was the case then other two instance should also have gone down. – Learning_DBAdmin Feb 27 at 5:31
  • @Learning_DBAmin Only if multiple nodes have power saving enabled. – Sting Feb 27 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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