I could not read my error log on one of my local SQL Servers, when I executed the following code:
I received the below:
Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.
When trying to read the error log via SSMS (SQL Server Management Studio) I would get this.
I then started to dig around and look at the physical log files (for this instance files located at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log) to see if it would give me any clues. I came across this message which told me everything that was wrong!
2017-10-25 10:46:02.48 Logon Could not connect because the maximum number of ‘5’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: ]
I then queried sys.configurations.
SELECT * FROM sys.configurations WHERE name = 'user connections'
SERIOUSLY? IT WASN’T ME! I then used sensible values and restarted SQL Server.
USE master GO EXEC sp_configure 'user connections', '0'; RECONFIGURE WITH OVERRIDE; SELECT * FROM sys.configurations WHERE name = 'user connections'
No loopback message anymore.