1

All,

I need some assistance. We have a reporting SQL server (upgraded from old server) that keeps (randomly) giving the below error message. We found the RAM was set to high (the memory was being pegged daily) - we corrected this. We also compared settings with old server to the new - both settings for connection timeout are set to unlimited. We ran a monitor session from our sonic wall - we did not see any dropped packets etc. Does anyone have any other idea?

Connection Timeout Expired. The timeout period elapsed during

the post-login phase. The connection could have timed out while waiting for

server to complete the login process and respond; Or it could have timed out

while attempting to create multiple active connections. The duration spent

while attempting to connect to this server was - [Pre-Login] initialization=5;

handshake=8; [Login] initialization=0; authentication=4; [Post-Login]

complete=14006; (.Net SqlClient Data Provider)

3 Spice ups
2

Quick google search

3

That error message that you’re seeing, where is that coming from? Is that coming from within the SQL Server logs, or is being returned by an application that you’re trying to connect to the SQL Server with?

If the answer is the latter, could you try connecting directly to SQL Server from that remote machine (either using Management Studio or sqlcmd from the command line)? I’d be interested to know if you can successfully make a direct connection to SQL Server from one of its native tools, rather than connecting via a program.

4

Rockn,

Thank you for the reply. I did some google searching yesterday. We found the string to check the time out period - which is currently set to unlimited - 0. Could that be the issue?

I also found an entry talking about if the memory was pegged - it could cause the time out also. We corrected the memory issue.

The DBA’s are saying its a network issue - but we have not found any issues on the network side.

5

JJJ - I’d give a shot to what I mentioned regarding making a more straightforward connection to the server using sqlcmd or something. If that tool is able to connect, it obviously wouldn’t be a network issue.

6

Have the DBAs checked the SQL Server logs to see if there are any failed login attempts being recorded?

7

Gerard,

Here is some more information.

There is a package running in visual studio - it connects to several SQL servers. It has issues logging into this particular server. The DBA also mentioned that he has issues connecting with management studio.

However, we ran a monitor on our sonicwall - we did not see anything about connection timeout or login issue.

8

SQL Server (by default) doesn’t time out login attempts. The client does. SSMS or whatever client you are using is timing out the connection attempt. Change the login timeout parameter. In SSMS, you can do this by clicking on Options on the connect dialog and modifying the connection timeout property (default is 15 seconds).

9

This is very basic, and I’m sure you’ve tried it, but can you ping/tracert from the problem machine to the sql server?

10

Is the network card on the problem server giving problem? Run a ping test and see if there are timeout.

11

Did anybody found the solution for this problem?

12

Use the IP address, not the server name. Some sort of DNS issue in your environment.

13

We had a situation where our sql server, a vm, went down with the ESX host it was on.

The vm rebooted, but TCP 1433 would not let us connect with the posted error.

We confirmed that all TCP settings were as intended in SQL Server Configuration, but ultimately the resolution was to change the IP of the server. Weird right?

My only thought is that something at the VMWare level was causing traffic to be dropped, but had no view into that platform as it was in the ‘cloud.’

I don’t know if is useful, but I hope someday it helps someone else.

Other notes are that SQL started fine, nothing in error log, with agent disabled we could access sql via pipes and udp 1434.