SQL SERVER- Msg 22004, Level 16 – xp_readerrorlog – Failed to Open Loopback Connection. Please See Event Log for More Information

6 years ago

Many DBAs use sp_readerrorlog or xp_readerrorlog to read ERRORLOG files in SQL Server. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

While I was working with a client they showed me below error while they were running xp_readerrorlog.

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

When we opened ERRORLOG in management studio, we got below error.

This was a clustered instance of SQL Server 2016.

When I searched on the internet, there were many solutions at many blogs:

TCP Alias: We need to create a TCP alias in the configuration manager, which should point to the right IP address, port.

SQL SERVER – Clustered Instance Online Error – SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]

Host File: To do this, we need to open below file in notepad C:\WINDOWS\system32\drivers\etc\hosts

In this file, we need to make below entry.

127.0.0.1 localhost server_name

I already have detailed checklist for common causes.

SQL SERVER – FIX: ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:)

WORKAROND/SOLUTION

For my client, none of the above worked. I found Microsoft Knowledge Base article 3185365 which explains the similar issue. As per article there is a bug in ODBC 13 which makes the SQL Server 2016 failed to run xp_readerrorlog in side by side scenario where there are multiple different instances on the box.

So, what worked for us was uninstalling ODBC 13 and installing ODBC 13.1 from below link https://www.microsoft.com/en-us/download/details.aspx?id=53339

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

SQL Error Messages, SQL Log, SQL Server

SQL SERVER – Msg 3013 – Cannot Create Worker Thread. BACKUP LOG is Terminating Abnormally

SQL SERVER – SQLCMD Error – Failed to Load Resource File SQLCMD.rll

9 Comments. Leave new

Vadim Mushkatin
January 10, 2018 5:21 pm

Thank you Pinal !!!
Great solution!

Reply
- Pinal Dave
  January 26, 2018 4:06 am
  
  Thanks Vadim
  
  Reply
Carlos Pivato
February 6, 2018 8:54 pm

Hi Guys,

I got the same problem installing SQL Server 2016 SP1 CU7 on MSFC. The ODBC Driver version on CU7 is 13.1.4466. After a few hours searching the solution, I found HERE! I’m uninstall the version 13.1.4466 and installing 13.1.4413. This’s working like a charm! :P Thanks!!!

Reply
- Pinal Dave
  February 15, 2018 7:16 am
  
  Thanks for sharing your situation and solution.
  
  Reply
Andreas
March 9, 2018 6:09 pm

Thank you Pinal and Thank you Carlos,
we had the same Problem which could be fixed with your solutions.

MSFC with four Instances from version 2012 to 2016 side by side. After we applied 2016 CU7 we could not expand SQL Server Logs in SSMS for the 2016 instance and log backpus which took less then 10s before needed 1h30m to complete (for about 300KB). Now the log backup is finisched in about 7 seconds.

Again, thanks for your post.
Best Regards
Andreas

Reply
Michelle Mei
March 13, 2018 11:26 am

For my SQL Server 2017, I have uninstall the version 14.1.0000 and installing 13.1.4413 but the problem still exists. Could someone help me out?

Reply
Michelle Mei
March 13, 2018 11:37 am

Error log says that… [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

Reply
- Pinal Dave
  April 2, 2018 5:38 am
  
  https://blog.sqlauthority.com/2018/02/23/sql-server-unable-start-sql-server-agent-error-drivers-sqlallochandle-sql_handle_env-failed/
  
  Reply
Manisha
July 19, 2018 11:07 pm

I got this error when my agent was down as it was configured to run under Local system and “NT AUTHORITY\SYSTEM” was not having sysadmin rights.
After giving proper rights, agent got started and was able to query “sp_readerrorlog”

Reply