Connection to Sql server being lost after a period of inactivity

Question

I updated my SQL Server 2005 db server with Windows 2003 SP2 and other windows updates and now other applications are loosing their connections to the SQL server after a period of inactivity. 

 

The ERP software and MSSS Managment Studio loose their connections if the connection sits idle for a couple of hours.  When in Management Studio I get the following error:

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

 

Has anyone seen this? Do I need to add SP1 for MS SQL 2005? 

 

Thanks-Dave

Answer 1

Check Auto_close property of Database. If its on switch it off

 

If this statement returns 1

 

select DATABASEPROPERTYEX ('db1','IsAutoClose' )

 

Then

 

EXEC sp_dboption 'db1', 'autoclose', 'False'

 

 

Madhu

 

Answer 2

I am also having this problem with just one instance of SQL 2005 x64 on Windows Server 2003 SP2.  AutoClose is set to false for all of the databases. I've enabled traceflags 3689 and 4029 but the error logs don't provide much help saying something like "client was disconnected".  I've also tried setting the SynAttackProtect registry key to 0.  Any other ideas?

 

Answer 3

Please check out the blog: http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx

 

HTH

Ming.

Answer 4

I have same problem too ..

 

Anyone figured out a solution for this ??

 

 

Please HELP!

 

Thanks

 

Answer 5

 

Same here... My system is Win 2003 x64 R2 server cluster with SQL 2005 SP2 (9.0.3054).

 

I've got applications spitting out this message from time to time. 

 

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

I can reproduce it by opening a window in Management Studio, go home for the day then come back the next day and execute any simple query i.e. select * from sysobjects. 

Answer 6

dhoops wrote:

I updated my SQL Server 2005 db server with Windows 2003 SP2 and other windows updates and now other applications are loosing their connections to the SQL server after a period of inactivity.

 

I have EXACTLY the same problem ..

 

Did you find any solutions yet ?

Answer 7

Not yet, no.  I have tried disabling TCP chimney offload as described here: http://support.microsoft.com/kb/942861.  I have the same network card mentioned in the article but this didn't fix the problem.  I also tried disabling SynAttackProtect: http://technet2.microsoft.com/windowsserver/en/library/910c8482-e5e5-4e2c-9ea4-11301ddfc4661033.mspx?mfr=true 

 

Next I am going to try this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q154628

 

All of these articles sound like the problem I am experiencing but none of the fixes work.  If anybody has found any other possible fixes then I'd be happy to try them out.

 

 

Answer 8

I've just spent the morning doing more research.  I believe the reason for the disconnection message can vary greatly!  In some cases, it could be the keep-alive mechanism causing the disconnect and in others it may be temporary routing issues or service restarts etc.  The message is reporting a generic TCP socket error, not a SQL error.  What I found particularly interesting is that Query Analyzer doesn't care if it gets disconnected, it will automatically reconnect when you run another query, while Management Studio returns the error and requires that you resubmit the query (again) before it reconnects. 

 

I found that I could consistently reproduce this error by using my laptop and VPN as a means of breaking & re-establishing the network connection.  Regardless of whether I was connecting to a SQL 2000 box or SQL 2005 box, on any OS, if I'd broken & re-established the VPN connection between resubmitting my simple select queries, Management Studio would complain with the error, requiring that I resubmit the query (again) while Query Analyzer would take the liberty of reconnecting (I'd get a new SPID) and running the query.  This supports the idea that it's not an entirely server configuration issue. 

 

My next test will not be finished until tomorrow.  I have opened query windows to 6 SQL Servers (3 2000, 3 2005) in both Management Studio & Query Analyzer using my desktop.  On, one of the 2005 servers, I've set both of the keep-alive values to 0.  Tomorrow I will resubmit simple select queries in all of the windows.  I expect that all 6 windows from Query Analyzer will return results and may even maintain the same SPID, while the Management Studio will probably show the error for all 6.  What I will be most interested in is if the SPIDS in Query Analyzer will change for all or some of the windows and if they only change for the 2005 servers, particularly the one where I set both of the keep-alive values to 0.  I'd also be surprised if the Management Studio query windows to the 2000 servers behave differently than the query windows to the 2005 servers.  I have a hunch that this problem is part client, part server, part network... and that the only cure-all is proper try/catch programming, which my developers don't want to hear.

 

I encourage all of you to run the same tests.

Answer 9

Well sure enough, I didn't remember to check the SPIDS before re-executing the queries so I don't know for sure which ones automatically grabbed new sessions.  I've recorded them now and will check again tomorrow.

 

Aside from that, here's the results :

 

None of the Query Analyzer windows complained when I re-ran the simple select left in the query windows.  I assume (will verify tomorrow) that connections to 2005 servers with the default keep-alive settings automatically got new SPIDS. 

 

On the Management Studio side, the connections to 2000 servers re-ran their simple select queries without a problem.  This confirms my suspicion that this problem is at least part server side and part client side.  I also believe that there's a part defined as any reason that might temporarily break a TCP connection.  The 2 query windows to 2005 servers with default keep-alive settings returned the error haunting us while the query window to the 2005 server where I'd zeroed out the keep-alive settings re-ran without a hitch.  I assume (will verify tomorrow) that it maintained it's SPID.

 

Expected conclusion (should be definitive tomorrow) : Zeroing out the keep-alive settings on the server will prevent this situation from occurring but cause us to forfeit the new 2005 features that are designed to sluff off abandoned connections for the sake of returning resources to the system.  Personally, I find that so unfortunate and therefore hope to find changes that can be made on the client side which will change the default behavior to do as Query Analyzer, automatically reconnect as needed.

 

I'm guessing and still seeking confirmation, that the difference is somewhere in TCP stacks used by .NET 2.0 applications and older applications... that this reluctance to re-connect automatically, is a feature/bug of .NET 2.0.  Could somebody please fix this or tell us how to change the default behavior!?.. 

 

Microsoft, are you there???

Answer 10

Another thing to try is the Network Monitor tool:

http://www.microsoft.com/downloads/details.aspx?familyid=18b1d59d-f4d8-4213-8d17-2f6dde7d7aac&displaylang=en

 

This should give more information about the cause of these dropped connections.

 

Answer 11

If it is true that this is a tool specific behavior based on handling exceptions thrown up when the server disconnects the client, then we will need to take it to the tools general forums.  They should be able to help you out there for this specific issue.

 

 

Answer 12

 

John,

 

I'm not sure what you mean by "tool specific"...  I'm still zeroing in on the scope of affected applications.  At the moment it seems related to .NET 2.0, but maybe it's an MDAC issue?..  All I know for sure are the specific symptoms that I've been able to reproduce with Management Studio and not Query Analyzer.  Nobody in my organization is complaining that Management Studio does this, but rather applications that have been developed internally.  Thus far, the list of affected applications are all .NET 2.0, but perhaps it's not as much the framework as MDAC or something else?  Something is common between our internally developed applications that experince this and Management Studio while different from Query Analyzer... does this make sense?

 

If someone knows how to phrase this question for the other forum and opens a thread there, please post the link.  Right now I'm not sure how I'd phrase the question for the tools general forum.  Would that forum's interest be specific to Query Analyzer & Management Studio and not so much our affected applications?

Answer 13

Happy Thanksgiving all!

 

The results of my test with respect to SPIDS as promised :

 

The Query Analyzer window again never complained.  The connections to SQL 2005 boxes with default keep-alive settings automatically got new SPIDS.  There was no message about the original connection having been broken.  The connection to the SQL 2005 box with keep-alive settings zeroed out kept the same SPID.  A connection to a SQL 2000 box that had been rebooted in between queries didn't complain, just got a new SPID.

 

The Management Studio windows : The connections to SQL 2005 boxes with default keep-alive settings displayed the message that brings us all together.  After resubmitting (again) it gets a new SPID & completes.  The connection to the server with keep-alive settings zeroed out kept it's SPID & didn't complain.  The connection to the SQL 2000 box that had been rebooted spit out the error message and required I resubmit (again), then it got a new SPID & completed.  The connections to the other SQL 2000 boxes didn't complain, kept the same SPIDS & executed.

 

This further supports my suspicions that it's a very multi-variable problem.  The keep-alive mechanism is just one of many reasons that may cause you to get this message.  In the end, it doesn't matter what causes the connection to be broken, Management Studio will complain rather than take the liberty of reconnecting with a new SPID & running.  If the client doesn't have the necessary components to participate in the keep-alive features then the server won't kill the connection thinking it's an abandoned connection.

 

Conclusion : While the server plays a part in this keep-alive feature, if the client isn't doing it's part, the server will do nothing.  Therefore it stands to reason that there could be a way to configure the clients to not participate in the keep-alive feature and therefore eliminate this reason for the broken connections however regardless, the newer connections i.e. Management Studio & .NET 2.0 applications show less resiliience against a broken connection for any reason.  The older type connections i.e. Query Analyzer and other pre .NET 2.0 aps seem to have a convenient default behavior of taking the liberty of reconnecting. 

 

Microsoft : Is there a way to configure .NET 2.0 applications to have this behavior of automatically reconnecting rather than spitting an error and giving up?

Answer 14

 

Dear all,

 

 

I think I finally got a solution (as for Dave's case, and mine as well) by downloading and installing the "Microsoft .NET Framework 3.0 Redistributable Package"

 

Here's a direct link: http://www.microsoft.com/downloads/details.aspx?FamilyID=10CC340B-F857-4A14-83F5-25634C3BF043&displaylang=en

 

 

Server has been two days operating naturally with no SQL Server 2005 loses of client connections, and no errors reported.

 

My server info:

OS: Microsoft Windows Server 2003 SP2

SQL: Microsoft SQL Server 2005 SP2

 

 

Good Luck to all.

 

 

Regards.

Answer 15

So you put the .NET Framework 3.0 Redistributable Package on the SQL Server, the clients or both?

Answer 16

I just installed .NET Framework 3.0 Redistributable Package on the server side operating system. No extra clients configurations needed.

 

Regards

Answer 17

Thanks. It was worked for me. but i didn't know how it work? what the ralation?

Answer 18

I haven't posted on this for a while because I've been waiting and watching to see how some changes I've made are panning out over time.  I will post back later when I have more organized information but the outlook is good so far!  We were facing a few issues simultaneously and distinguishing between them was clouding perception of cause & effect.  Here's a summary of the 3 things I've changed.

1. SynAttackProtect
2. KeepAliveTime
3. TCP Chimney Offload

You can find more information at the following links.  After extending the KeepAliveTime to 24 hours and disabling SynAttackProtect & TCP Chimney Offload, I believe the problem is gone.  I am not sure yet if only one of those changes was required to accomplish this and intend to find out, however I still have some monitoring and other issues to pursue.  I wish everyone else luck.  I will post more later.

• http://www.sqlservermart.com/resources/SynAttackProtect.aspx
• http://technet2.microsoft.com/windowsserver/en/library/910c8482-e5e5-4e2c-9ea4-11301ddfc4661033.mspx?mfr=true
• http://blogs.msdn.com/sql_protocols/archive/2006/04/12/a-special-gne-general-network-error-messages-when-running-sql-server-after-installing-service-pack-1-for-windows-server-2003-and-tcp-registry-key-synattackprotect.aspx
• http://support.microsoft.com/kb/942861

-B

 

P.S.  I believe that the SynAttackProtect is the solution to a separate problem I was seeing where an in house developed application was sporadically crashing & returning the error "[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error.  Check your network documentation."  At first I thought this & the dropped connections were related but I now believe they are an entirely separate issue.  I believe extending the KeepAlive in the registry fixed the dropped connections after periods of inactivity and the TCP Chimney Offload probably wasn't needed.  I will seek to confirm this in the new year.

Answer 19

 

Disabling the TCP chimney offload feature did the trick for me but then I do have the same model of network adapter that this article says is affected: http://support.microsoft.com/kb/942861.  If anybody uses these particular network adapters then I would suggest you try disabling this feature.  Otherwise, this symptom seems to be associated with several different problems so you just need to find the solution that works for you.

 

Answer 20

For anyone who might experience this in the future, I can reproduce the disconnection 100% of the time with the following query:

 

Code Snippet

update a

set tiPurge = 1

from [import table] a

inner join KeywordScrub k on k.vcText like k.vcKeyword

 

 

Import table contains 4M rows. <5% of a table is successfully joined to KeywordScrub.

KeywordScrub contains 2975 rows. One index on the Keyword column, datatype varchar(255).

 

Because I was in mortal danger, I figured out that I could batch the update above so that I'm grabbing 200 rows at a time from the KeywordScrub table. Doing so prevented the disconnection problem.

 

We are still implementing some of the changes mentioned in previous posts to see if they help.

 

Example of smaller batch sizes:

 

Code Snippet

CREATE procedure spuKeywordScrub
 @table varchar(255)
as
begin
 set nocount on

 declare @iPartition int,
   @iStartID int,
   @iMaxID int,
   @iRowCount int,
   @iTopID int,
   @vcSQL varchar(8000)
 
 -- maximumid; needed for loop
 select @iTopID = max(iKeywordID)
 from KeywordScrub

 

 -- records to process at a time in the keywordscrub table
 set @iPartition = 200

 

 -- get range
 set @iStartID = 1
 set @iMaxID = @iStartID + @iPartition-1

 

 -- work on the current range
 while @iMaxID < @iTopID + @iPartition-1
 begin
  -- to get into the loop
  set @iRowCount = 1

  while @iRowCount > 0
  begin

   begin tran keywordscrub

   set @vcSQL = 'update a'
   set @vcSQL = @vcSQL + ' set tiPurge = 19'
   set @vcSQL = @vcSQL + ' from [' + @table + '] a'
   set @vcSQL = @vcSQL + ' inner join KeywordScrub k on a.vcText like k.vcKeyword'
   set @vcSQL = @vcSQL + ' where k.iKeywordID >= ' + convert(varchar(10), @iStartID) + ' and '
   set @vcSQL = @vcSQL + '       k.iKeywordID <= ' + convert(varchar(10), @iMaxID) + ' and '
   set @vcSQL = @vcSQL + ' tiPurge is null'

   exec (@vcSQL)

   select @iRowcount = @@ROWCOUNT
   commit tran keywordscrub

   set @iRowCount = 0  
  end

  -- increase partition
  set @iStartID = @iStartID + @iPartition
  set @iMaxID = @iStartID + @iPartition-1
 end
 set rowcount 0
end

 

 

Answer 21

bubthezombie6,

 

If I had to guess, I'd say you're more likely to find the problem is the TCP Chimney Offload because I don't think that the KeepAlive setting would be tripped by a running query.  I might be wrong however.  If you have a Broadcom NetXtreme II NIC, you should give it a shot as I recall that NIC was specifiaclly cited in the KB on TCP Chimney Offload and from what I understand on that specific problem, it's not got as much to do with number of connections or lack of activity as the NIC just isn't good at TCP Chimney Offloading.

 

Please post back what you find works!