This is a strange one that has plagued me for a while now. When logging in to the second instance of SQL Server 2008 on one of our database servers, we get a timeout error:
Cannot connect to servername\mssqlserver2.
Additional information:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)")
(This is the error message when trying to connect with Microsoft SQL Server Management Studio; other tools experience the same error, but of course say it in different ways.)
Immediately re-attempting to log in works just fine, so whatever the cause it's ephemeral! This happens regardless of user or authentication type (both Windows and SQL Server authentication methods are supported on this instance). What's even weirder, though, is that the first instance on this server has never once demonstrated this problem.
Server is a Windows Server 2008 R2 virtual server, hosted in Microsoft Hyper-V (host is likewise Server 2008 R2). The server has 2GB of RAM, and seems to regularly be using 90% of that — could low memory be the cause of this issue? I could see this second instance — which is not used very often yet — being swapped out to disk, and then taking too long to load back into memory to respond in time to the connection request, but I'd rather have more than just my own hunch before I go scheduling a downtime for this server (the first instance is used regularly) and then just throwing extra resources at it in the blind hope that the problem goes away.
Best Answer
Solved!
Turns out the issue was a firewall one, although why it would work on the second attempt is still a mystery to me -- generally if a firewall is getting in the way, it stays in the way, it doesn't magically allow a second attempt right through...
Anyway, it turns out that the first instance was configured to listen on TCP port 1433, however the second instance was configured for "TCP Dynamic Ports" on 59196 (or something like that); what's so "dynamic" about a single port is beyond me, but whatever.
Adding a firewall rule to the Windows Firewall to allow connections to that port immediately resolved my problem. I then took it a step further, however: Instead of using this "dynamic" port, I changed it to use TCP port 1432 (1434 being used for administrative connections, or something like that), and modified the firewall rule to allow that port instead.
The port configuration can be found in SQL Server Configuration Manager --> SQL Server Network Configuration --> TCP/IP Properties --> IP Addresses; under this tab, I simply modified the settings in the IPAll section, ignoring the myriad IPn sections (which were all disabled anyway): blank the "TCP Dynamic Ports" entry, and enter 1432 for "TCP Port". Changing these settings does require that you restart your SQL Server instance.