More on SQL Server 2008 Connection Timeouts

December 15, 2009

A few more details on my current woes with SQL Server 2008. This is 64-bit Enterprise Edition, SP1 + CU5. We are getting severe, intermittent, but easily reproducible connection timeouts. This is when you attempt to connect to the instance, but the client eventually gets fed up of waiting and gives up, like this in Management Studio:-

 ssms_connection_timeout

This timeout is set by default in SSMS to be 15 seconds (plenty long enough to establish a connection to an instance on your LAN, Gigabit connected, fast server)…

connection_timeout_default_ssms

This error is slightly different when you generate it using SQLCMD:-

 connection_timeout

I am getting other symptoms of the same underlying issue, e.g. occasionally when trying to read the error log in a query window using xp_readerrorlog:-

 
xp_readerrorlog_error2

I have no resolution for this issue yet; will update if/when I do. Troubleshooting with Microsoft Premier Support has involved registry changes for things like Max Ports, chimneying off, SynAttackProtect (no benefit). We have also done network tracing using WireShark. I have done some testing with a custom Microsoft-supplied app, and also my own testing using SQLCMD. And before you asked, yes I have tried rebooting!! That’s not an easy thing to arrange when it’s a live 4-node cluster supporting 24X7 websites and applications… one of the (many) compelling reasons to have a passive node in your cluster, which you can use precisely for this kind of thing.

Wish me luck.. even the Director’s started asking for daily updates. Urgh!

About these ads

4 Responses to “More on SQL Server 2008 Connection Timeouts”

  1. Lindsay Says:

    Nice work Banksy

    • Lars Says:

      hi.

      did you find a solution for this topic ? we have the same problem as well on our side.

      Best Regards
      Lars

      • thomaspullen Says:

        Lars, I no longer work at the place where this issue existed. As far as I know, a solution was not found. Upgrading the OS from Windows 2003 Server to 2008 helped mitigate the problem, I believe, but it didn’t solve it completely. Good luck.

        Tom

  2. PL Says:

    Don’t know if you’ve checked but Sql Server Express is configured AUTOMATICALLY for Auto-Close set to TRUE! That way when the last handle goes out of scope, the DATABASE SHUTS DOWN. Change it in management studio
    db properties/options/Auto-Close

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s