Connection problems with SQL Server in ASP.NET applications using out-of-process session state

Question

I have several ASP.NET applications deployed in a farm of 4 Windows 2003 machines. Each application uses a separate App Pool and Virtual Directory in IIS. They rely heavily on sessions which are persisted out of process on a single SQL Server 2000 (<sessionstate mode="sqlserver" ... />). Applications are compiled against .NET 3.0 but .NET 3.5 SP1 is installed on servers.

Each web server receives approximately 10 requests/second. Every once in a while I get some exceptions in logs:

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

or another:

System.Data.SqlClient.SqlException: 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.)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.WriteSni()
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
   at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

or yet another:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)

These errors occur a couple of times a day for a period of about 1-2 minutes and then disappear. Has anyone encountered such problems? What could you suggest me to do in order to further track down the problem? To me it looks more like network issues than application. Could it be some settings on the SQL Server which cannot handle so many concurrent connections? Any suggestions would be greatly appreciated.

UPDATE:

I've solved the problem by performing major updates to the application in order to reduce the number and size of the objects being stored in the session.

score 17 · Accepted Answer · answered May 01 '09 at 08:52

17

Transport level errors are often linked to the connection to sql server being broken ... usually network.

Timeout Expired is usually thrown when a sql query takes too long to run.

So I would troubleshoot the link to your Sql Server and then monitor to see what queries are timing out.

Sounds like a SQL job is running, backup? That might be locking tables or restarting the service.

answered May 01 '09 at 08:52

Chad Grant

44,326
9
65
80

Thanks for the tips @Deviant. I will check these and post the results. – Darin Dimitrov May 01 '09 at 09:12

score 4 · Answer 2 · answered Nov 25 '11 at 13:33

In my case the problem was related with host TCP configuration (virtual machine in VMWare). After quick research (some article found in Google and MSDN Blogs) I've turned off in system registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\synattackprotect (0) and HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\EnableTCPChimney (0). Now it works just fine. Of course this was only test machine (corporate network not visible from Internet) and I wouldn't do that on production environment ;-)

score 2 · Answer 3 · answered Nov 07 '11 at 16:05

2

It might be the case in which the length of your query exceeds the limitation of 65,536 * Network Packet Size (default 4KB).

answered Nov 07 '11 at 16:05

Ishaan Puniani

646
2
10
23

1

does that apply to query results or just to the query you send to the server? – paIncrease Sep 24 '12 at 20:54

score 1 · Answer 4 · edited Jul 23 '09 at 02:05

1

Set CommandTimeout = 120 in the connection string.

Try adding a Connect Timeout in the web.config:

<add key="DBConnection" value="server=LocalHost;uid=sa;pwd=;database=DataBaseName;Connect Timeout=200; pooling='true'; Max Pool Size=200"/>

Please reply back if this works....

edited Jul 23 '09 at 02:05

sth

222,467
53
283
367

answered Jul 22 '09 at 07:02

12

I have not given you a minus -1 because I believe you should know why people marked you down for this answer. Increasing timeout or even pool will not solve the underlying problem, it will only increase the scale of the failure if this error happens again. – Luke Dec 09 '11 at 06:46
1

Not agree completely. If you really need to perform a very big single command, like deleting thousands of rows in a single DELETE statement, you need to increase the Commandtimeout. Normally you should rethink the problem to avoid that kind of situations, but sometimes you need Commandtimeout higher than default 30 seconds, like in http://stackoverflow.com/questions/11747368/set-command-timeout-in-entity-framework-4-3 You should also look for another problems, like locks... – David Oliván Apr 16 '13 at 14:08
3

You can't set the `CommandTimeout` (i.e. max query execution time) in the connection string. In the connection string, you can only set the CONNECTION timeout (i.e. the max time to wait for a connection to be opened, which is prior to any query execution). – BateTech May 06 '14 at 19:10

Connection problems with SQL Server in ASP.NET applications using out-of-process session state

4 Answers4

Linked