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

I am back with one more solution :).The problem was simple but the error made me thinking ..I was trying to do xp_readerrorlog on a small file .But my SPID hanged ..after some time I got this error :

SQL Server error in QA :
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 read somewhere that this error comes when SQL Server Agent failes to come up.Yes my agent was down.But I faied to understand what is the relation between running xp_readerrlog and SQL Agent not running .Still, I tried to run the agent and got the error ...So something is related to SQL Agent here and that something is that If SQL Agent is not running , I can run xp_readerrlog successfully (I will prove it wrong later).

I checked the application logs immediately and got these errors :

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 09/20/2010
Time: 18:24:22
User: N/A
Computer: abchaudh
Description:
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4d 27 00 00 0a 00 00 00 M'......
0008: 12 00 00 00 61 00 62 00 ....a.b.
0010: 63 00 68 00 61 00 75 00 c.h.a.u.
0018: 64 00 68 00 00 00 0e 00 d.h.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...


Event Type: Error
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 103
Date: 09/20/2010
Time: 18:24:45
User: N/A
Computer: abchaudh
Description:
SQLServerAgent could not be started (reason: Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 102
Date: 09/20/2010
Time: 18:24:52
User: N/A
Computer: abchaudh
Description:
SQLServerAgent service successfully stopped.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I also checked the SQL Agent logs :

SQL Agent logs :

2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [000] Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [382] Logon to server 'abchaudh\CORRUPT' failed (DisableAgentXPs)
2010-09-20 18:25:43 - ? [098] SQLServerAgent terminated (normally)

So, now I have 2 issues : SQL Agent is not running and xp_readerrorlog is timing out.

.If you see one of my older posts on "target machine actively refuses it" , you will have some information .

So I opened CLICONFG and found 3 incorrect aliases which were not using the right port ..

I removed them and SQL Agent came on line ...xp_read errorlog also started working ...

I stopped SQL Agent and stil everything was working ...
So the issue was that xp_readerrlog tries to connect to SQL Server but stucks due to the Alias pointing to incorrect port.

But this does not affect SQL Server service .To check why its not affecting SQL Server service , I disabled the sahred memory protocol and BANG....SQL Server connection failed ...

Happy learning ....