Frequent Random TCP Transport Errors Using SSMS

I'm using SSMS to connect remotely to a SQL Server 2005 SP3 instance,
running on Windows Server 2003 x64 SP2. The client is Windows XP SP3 and the
server is a two-node Windows cluster.

I connect to the database in SSMS and execute a query that returns one row.
I can execute it again and again and all is well. I leave it for a few
minutes and execute again. This time I get

"Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - An existing connection was forcibly
closed by the remote host.)"

I have installed MS Network Monitor V3.3 and can see that every 30 seconds
the client and server exchange TCP Keep Alive packets. So long as each
receives a Keep Alive ACK then the connection remains good. But after a
while I see that the client sends five Keep Alive packets in succession and
doesn't get an ACK to any of them. The server does not appear to send out
its Keep Alive packet either. If I then execute my query it gives the error
above without fail.

I have checked with others who also connect to the same SQL Server instance
and they do not get this problem. It's just me, and it didn't always do
this, although it has done for the past few months.

I tried running NetMon on the server to see if the Keep Alive packets get
through each time, but I haven't been able to detect them at all; possibly
because the SQL Server instance is on a Windows cluster and so has a virtual
IP address.

Can anyone suggest why this might be happening, or what other steps I can
take to get a better handle on it? I should add that I get odd behaviour
using RDP to another server on the same subnet, where the session often
freezes and then jumps back into life after 30-60 seconds. Again, no one
else seems to get this problem, so I'm quite happy to believe that it is a
problem at my end. I'd just like to understand why, so that I can fix it.

TIA

Charles

Anyone?


"Charles" <> wrote in message
news:...
> I'm using SSMS to connect remotely to a SQL Server 2005 SP3 instance,
> running on Windows Server 2003 x64 SP2. The client is Windows XP SP3 and
> the server is a two-node Windows cluster.
>
> I connect to the database in SSMS and execute a query that returns one
> row. I can execute it again and again and all is well. I leave it for a
> few minutes and execute again. This time I get
>
> "Msg 10054, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
>
> I have installed MS Network Monitor V3.3 and can see that every 30 seconds
> the client and server exchange TCP Keep Alive packets. So long as each
> receives a Keep Alive ACK then the connection remains good. But after a
> while I see that the client sends five Keep Alive packets in succession
> and doesn't get an ACK to any of them. The server does not appear to send
> out its Keep Alive packet either. If I then execute my query it gives the
> error above without fail.
>
> I have checked with others who also connect to the same SQL Server
> instance and they do not get this problem. It's just me, and it didn't
> always do this, although it has done for the past few months.
>
> I tried running NetMon on the server to see if the Keep Alive packets get
> through each time, but I haven't been able to detect them at all; possibly
> because the SQL Server instance is on a Windows cluster and so has a
> virtual IP address.
>
> Can anyone suggest why this might be happening, or what other steps I can
> take to get a better handle on it? I should add that I get odd behaviour
> using RDP to another server on the same subnet, where the session often
> freezes and then jumps back into life after 30-60 seconds. Again, no one
> else seems to get this problem, so I'm quite happy to believe that it is a
> problem at my end. I'd just like to understand why, so that I can fix it.
>
> TIA
>
> Charles
>
>

"Charles" <> wrote in message
> news:...
>> I'm using SSMS to connect remotely to a SQL Server 2005 SP3 instance,
>> running on Windows Server 2003 x64 SP2. The client is Windows XP SP3 and
>> the server is a two-node Windows cluster.
>>
>> I connect to the database in SSMS and execute a query that returns one
>> row. I can execute it again and again and all is well. I leave it for a
>> few minutes and execute again. This time I get
>>
>> "Msg 10054, Level 20, State 0, Line 0
>> A transport-level error has occurred when receiving results from the
>> server. (provider: TCP Provider, error: 0 - An existing connection was
>> forcibly closed by the remote host.)"
>>
>> I have installed MS Network Monitor V3.3 and can see that every 30
>> seconds the client and server exchange TCP Keep Alive packets. So long
>> as each receives a Keep Alive ACK then the connection remains good. But
>> after a while I see that the client sends five Keep Alive packets in
>> succession and doesn't get an ACK to any of them. The server does not
>> appear to send out its Keep Alive packet either. If I then execute my
>> query it gives the error above without fail.
>>
>> I have checked with others who also connect to the same SQL Server
>> instance and they do not get this problem. It's just me, and it didn't
>> always do this, although it has done for the past few months.

My guess is that you have some flaky network component somewhere. Searching
on "forcibly closed by the remote host", I found these two blogposts,
that may be informative:

http://blogs.msdn.com/sql_protocols/...ckprotect.aspx


http://blogs.msdn.com/sql_protocols/...e-chimney.aspx



--
Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland

Thanks for responding. The second of the links is very interesting. It led
me to a Microsoft page where it describes the problem I'm having, and says
that it particularly afflicts the Broadcom 5708 chipset, which our Dell
servers use in the NetXtreme II NICs. Dell have updated system BIOS, NIC
firmware and drivers, but I now have a dilemma. The servers are remote and I
can't physically get to them without a day's round-trip. I'm naturally very
wary of updating any of these items remotely because I might lose contact
and not be able to get it back.

Although the updates look promising I think they will have to wait for now.
I might try disabling the chimney offload feature in the meantime.

Cheers

Charles


"Erland Sommarskog" <> wrote in message
news:Xns9C8BC2EEB17BCYazorman@127.0.0.1...
> "Charles" <> wrote in message
>> news:...
>>> I'm using SSMS to connect remotely to a SQL Server 2005 SP3 instance,
>>> running on Windows Server 2003 x64 SP2. The client is Windows XP SP3 and
>>> the server is a two-node Windows cluster.
>>>
>>> I connect to the database in SSMS and execute a query that returns one
>>> row. I can execute it again and again and all is well. I leave it for a
>>> few minutes and execute again. This time I get
>>>
>>> "Msg 10054, Level 20, State 0, Line 0
>>> A transport-level error has occurred when receiving results from the
>>> server. (provider: TCP Provider, error: 0 - An existing connection was
>>> forcibly closed by the remote host.)"
>>>
>>> I have installed MS Network Monitor V3.3 and can see that every 30
>>> seconds the client and server exchange TCP Keep Alive packets. So long
>>> as each receives a Keep Alive ACK then the connection remains good. But
>>> after a while I see that the client sends five Keep Alive packets in
>>> succession and doesn't get an ACK to any of them. The server does not
>>> appear to send out its Keep Alive packet either. If I then execute my
>>> query it gives the error above without fail.
>>>
>>> I have checked with others who also connect to the same SQL Server
>>> instance and they do not get this problem. It's just me, and it didn't
>>> always do this, although it has done for the past few months.
>
> My guess is that you have some flaky network component somewhere.
> Searching
> on "forcibly closed by the remote host", I found these two blogposts,
> that may be informative:
>
> http://blogs.msdn.com/sql_protocols/...ckprotect.aspx
>
>
> http://blogs.msdn.com/sql_protocols/...e-chimney.aspx
>
>
>
> --
> Erland Sommarskog, SQL Server MVP,
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>

Update:

I've tried turning off the TCP chimney offload and it makes no difference. I
still get the symptom where the client sends out Keep Alive packets every 30
seconds, and after a while the server stops responding to them.

Charles


"Charles" <> wrote in message
news:#...
> Hi Erland
>
> Thanks for responding. The second of the links is very interesting. It led
> me to a Microsoft page where it describes the problem I'm having, and says
> that it particularly afflicts the Broadcom 5708 chipset, which our Dell
> servers use in the NetXtreme II NICs. Dell have updated system BIOS, NIC
> firmware and drivers, but I now have a dilemma. The servers are remote and
> I can't physically get to them without a day's round-trip. I'm naturally
> very wary of updating any of these items remotely because I might lose
> contact and not be able to get it back.
>
> Although the updates look promising I think they will have to wait for
> now. I might try disabling the chimney offload feature in the meantime.
>
> Cheers
>
> Charles
>
>
> "Erland Sommarskog" <> wrote in message
> news:Xns9C8BC2EEB17BCYazorman@127.0.0.1...
>> "Charles" <> wrote in message
>>> news:...
>>>> I'm using SSMS to connect remotely to a SQL Server 2005 SP3 instance,
>>>> running on Windows Server 2003 x64 SP2. The client is Windows XP SP3
>>>> and
>>>> the server is a two-node Windows cluster.
>>>>
>>>> I connect to the database in SSMS and execute a query that returns one
>>>> row. I can execute it again and again and all is well. I leave it for a
>>>> few minutes and execute again. This time I get
>>>>
>>>> "Msg 10054, Level 20, State 0, Line 0
>>>> A transport-level error has occurred when receiving results from the
>>>> server. (provider: TCP Provider, error: 0 - An existing connection was
>>>> forcibly closed by the remote host.)"
>>>>
>>>> I have installed MS Network Monitor V3.3 and can see that every 30
>>>> seconds the client and server exchange TCP Keep Alive packets. So long
>>>> as each receives a Keep Alive ACK then the connection remains good. But
>>>> after a while I see that the client sends five Keep Alive packets in
>>>> succession and doesn't get an ACK to any of them. The server does not
>>>> appear to send out its Keep Alive packet either. If I then execute my
>>>> query it gives the error above without fail.
>>>>
>>>> I have checked with others who also connect to the same SQL Server
>>>> instance and they do not get this problem. It's just me, and it didn't
>>>> always do this, although it has done for the past few months.
>>
>> My guess is that you have some flaky network component somewhere.
>> Searching
>> on "forcibly closed by the remote host", I found these two blogposts,
>> that may be informative:
>>
>> http://blogs.msdn.com/sql_protocols/...ckprotect.aspx
>>
>>
>> http://blogs.msdn.com/sql_protocols/...e-chimney.aspx
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP,
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>>