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

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:

1. 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]

2. 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)