4 декабря 2009 г. 20:22We are getting some exceptions when connecting from an Azure web role to a SQL Azure database:
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)
I posted a whole thread on the Windows Azure forum, but so far no solution has been suggested:
Exceptions connecting to SQL Azure: "An established connection was aborted by the software in your host machine"
This does not happen on every call, but it happened over 70 times last week as our QA team was testing our web role.
Both the web role and the SQL Azure database are in the same data center.
We did set the maxconnection to 96 for connection pooling.
We use .netTiers (http://nettiers.com/) for our ORM, it's similar to nHibernate.
I have a gut feeling (which I can't prove) that this exception is occurring due to connection pooling.
My guess is that the connections in the pool timeout at 10 minutes while SQL Azure times out at 5 minutes.
I don't know how to change the timeouts in either, so I thought I'd ask here first: is there any way to increase SQL Azure's connection timeout? In an on-premise SQL Server, I'd right click on the server in SSMS - but the timeout is not an option for a SQL Azure database using SSMS 2008 R2.
Here's the full exception -
MYMETHOD() Exception : System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at
System.Data.SqlClient.TdsParserStateObject.WriteSni() 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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at
MYPROJECT.DataAccessLayer.Utility.ExecuteReader(Database database, DbCommand dbCommand) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Utility.cs:line 361 at
MYPROJECT.DataAccessLayer.SqlClient.SqlMycAccountUserProviderBase.GetPaged(TransactionManager transactionManager, String whereClause, String orderBy, Int32 start, Int32 pageLength, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer.SqlClient\SqlMycAccountUserProviderBase.generated.cs:line 638 at
MYPROJECT.DataAccessLayer.Bases.EntityProviderBaseCore`2.GetTotalItems(TransactionManager mgr, String whereClause, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Bases\EntityProviderBaseCore.generated.cs:line 241 at
MYPROJECT.DataAccessLayer.Bases.EntityProviderBaseCore`2.GetTotalItems(String whereClause, Int32& count) in C:\vss\MYPROJECT\MYPROJECTBusiness\main\MYPROJECT.DataAccessLayer\Bases\EntityProviderBaseCore.generated.cs:line 228 at
MYPROJECT.DeployManager.ClientResponseFacade.MYMETHOD(MYPROJECTPointClientRequest initialRequest) in C:\vss\MYPROJECT\MYPROJECT.Deploymanager\Main\ClientResponseFacade.cs:line 156
5 декабря 2009 г. 9:05
Your guess is correct -- an idle connections in the pool is cleaned up after about 8 minutes but the SQL Azure closes idle connections after 5 minutes. There is a 3-minute window between SQL Azure closing an idle connection and before the pool ejects the the idle connection where the connections in the pool is stale. If your application picks up the stale connection from the pool in that 3-minute then you get the "transport-level " error.
We are working on a fix to increase the SQL Azure 5-min timeout to resolve the issue.
I'll post something to the forum as soon as the fix is in.
Sorry for the pain.
28 марта 2010 г. 22:41Any update! :0) thanks!
29 марта 2010 г. 3:16
The SU1 update of the service was deployed on Feb-17 and as of that date the "Idle timeout" has been increased to 30-minutes. For more details about SU1 please see: http://blogs.msdn.com/sqlazure/archive/2010/02/17/9965464.aspx