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.
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 ???
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
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
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.
- Proposed as answer by Tom Li - MSFTModerator Thursday, October 28, 2010 7:11 AM
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...
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.
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 email@example.com thanks.
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