none
Sql server 2008 R2 x64 / SSMS : Intermittent Connectivity Issue

    Question

  • I have read almost everything on this topic, applied each proposed solution, checked all conf settings but I am stil stuck with these frequent but intermittent connectivity issues.

    I moved our databases from a win 2003 + SQL Server 2005 x86 enterprise (where I had no connectivity issue) to a clean install of SQL server 2008 R2 x64 enterprise on Windows Server 2008 R2 x64. Enabled tcp/ip & named pipes, recreated the users (& fixed orphans), opened ports on firewalls client, hardware, even Win Server although it is disabled, applied the UC4 patches, tried various connection strings, created aliases (for x64 and x32), ran the different recommended tools (portQryV2...) to no avail.

    Most of the time, I can connect, with SSMS or via code with SqlConnection and execute queries. But frequently, I either lose the connection or cannot connect on the first call. Then it works on subsequent calls. In vb, the  code hangs on the call of the open method of the SqlConnection (and yes I properly close my connections once the queries have been executed). Setting the CommandTimeout has no effect. The code/SSMS freezes for 30/40 seconds.

    No error is showing up in the Profiler, Nothing in the SQL Server Logs. The client and the server are on different networks. I connect via TCP/IP(remote connection).

    I have found a few post where people were reporting intermittent connectivity issues with SQL Server 2008 R2 x64 & x64 clients... I wonder if this could be a bug.

    Anyway...

    Just tried to connect to the database via SSMS : 

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)" Error Number 10060

    I closed the messagebox, retried the same thing : success.

    Any idea ???

    Thanks.

    -------------------------------------------------------------

    Client : VS 2010, installed on top of SQL Srv 2008 R2 enterprise

    Server : Windows Server 21008 R2 & SQL Server 2008 R2 + patched with UC4

    • Microsoft SQL Server Management Studio 10.50.1746.0
    • Microsoft Analysis Services Client Tools 10.50.1746.0
    • Microsoft Data Access Components (MDAC) 6.1.7600.16385
    • Microsoft MSXML 3.0 6.0 
    • Microsoft Internet Explorer 8.0.7600.16385
    • Microsoft .NET Framework 2.0.50727.4952
    • Operating System 6.1.7600

    Tuesday, October 26, 2010 3:54 PM

All replies

  • I have disabled the Chimney TCP feature and set the KeepAlive setting to 900000 (15mn) instead of 30000 (30 sec) : no disconnection after a few hours. These workarounds seem to work but they are... workarounds. Any progress on the Microsoft front ?

    ---

    upd : these changes seemed to have a negative impact on the server performances

    • Edited by Xpou005 Thursday, October 28, 2010 9:30 PM follow up
    Wednesday, October 27, 2010 12:04 PM
  • What you are describing is almost always caused by the server being too busy to respond to new connections.  Check your CPU and RAM performance counters on the server.

     

    Wednesday, October 27, 2010 3:07 PM
  • Hi Tom, Thanks for the response.

    Our conf is : 2 X5550 Xeon 2.67 GHz (16 cores) and 32 GB RAM. 18 GB are used by SQL Server, CPU Usage 5 to 10%.

    I believe it is not a server performance related issue.


    Chimney TCP feature enabled again (I noticed no gain from disabling it)

    KeepAlive to 60000 - Intermittent disconnections are back again...

    Xavier

    Thursday, October 28, 2010 9:28 PM
  • I had the same problem. Finally I changed server Network Location from Public network(very "clever" choice from administrator:-)) to Work network and this worked.
    • Proposed as answer by GaryBarrett Tuesday, November 08, 2011 4:01 AM
    Sunday, November 07, 2010 12:21 PM
  • Gary

    I have been investigating the same intermittent issue here for months. Please advise details of where and how you changed the server Network Location from Public netowrk to Work network. Was this on the SQl server?

    My connection issue is from the webserver in a DMZ, via a firewall, to our internal (network) SQL servers.

    Friday, January 13, 2012 3:47 AM
  • Hi folks,

    We are seeing the same issue after upgrading our db server's OS to windows server 2008 64-bit. (plus we installed sql server 2008 R2 service pack 3 at the same time). What is the solution? Gary? Don? Anyone? Please email me at jason_daly@trimble.com thanks.

    Thursday, July 26, 2012 4:15 AM
  • Hi,

    Did anyone find a solution to this?  We are experiencing similar problems since upgrading from 2008 R2 to 2012.

    Any help welcome.

    Rich

    Wednesday, September 05, 2012 10:03 AM
  • We have a similar issue: sql server 2008 on Win 2008 r2. SSMS randomly fails to connect. No firewalls.

    SSMS connects without problems on other servers.

    We have traced IP traffic on the network and with wireshark. We have noticed some packets apparently get lost on the server at the interface of the server where SSMS is running. We now this since we have monitored traffico on the switch port as well.
    The issue is rather misterious, as of now.

    Packets sent/ received at the SSMS server (10.125.142.60) interface

    No.     Time        Source                Destination           Protocol Length Info
       3091 13.811332   10.125.142.60         10.125.226.60         TCP      66     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
       3545 16.810084   10.125.142.60         10.125.226.60         TCP      66     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
      10004 22.815308   10.125.142.60         10.125.226.60         TCP      62     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 SACK_PERM=1

    Packets sent/ received at the SSMS server (10.125.226.60) interface

    No.     Time        Source                Destination           Protocol Length Info
       3216 13.811274   10.125.142.60         10.125.226.60         TCP      66     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
       3217 13.811296   10.125.226.60         10.125.142.60         TCP      66     ms-sql-s > 62236 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
       3711 16.810011   10.125.142.60         10.125.226.60         TCP      66     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
       3712 16.812944   10.125.226.60         10.125.142.60         TCP      66     ms-sql-s > 62236 [SYN, ACK] Seq=0 Ack=1 Win=8192 Len=0 MSS=1460 WS=256 SACK_PERM=1
      10029 22.807125   10.125.226.60         10.125.142.60         TCP      62     ms-sql-s > 62236 [SYN, ACK] Seq=0 Ack=1 Win=65535 Len=0 MSS=1460 SACK_PERM=1
      10047 22.815211   10.125.142.60         10.125.226.60         TCP      62     62236 > ms-sql-s [SYN] Seq=0 Win=8192 Len=0 MSS=1460 SACK_PERM=1
      46780 34.807488   10.125.226.60         10.125.142.60         TCP      54     ms-sql-s > 62236 [RST] Seq=1 Win=0 Len=0

    Wednesday, September 12, 2012 6:38 AM
  • We apparently fixed the issue on our servers updating the (Broadcom) NIC driver to the latest version.

    Thursday, September 13, 2012 7:37 AM
  • Anyone else found this to be the case? I am fighting the same issue with SQL Server 2008 R2 on a Windows 2008 R2 Server (64 Bit).


     

    Dan Barry

    Friday, October 25, 2013 8:31 PM