Issues with SQL Azure connection through website using linq to sql

0
votes

The website we're hosting in Azure is regularly throwing exceptions when there are several users using the system at once (15-25 users) and retrieving, and posting data from and to the database. The exception has to do with the connection to the database.

We're using Azure SQL and Linq to SQL. I've checked the monitor of the database, and there's no report about failed connection and the amount of connections are well under what it should be able to handle.

I'm using a IoC container for making new instance of the datacontext and it's only one instance of the datacontext being initiated and are being used for querying all the tables.

I've digged into the eventlog and found the exceptions that are being thrown:

Event code: 3005 Event message: An unhandled exception has occurred. Event time: 02.04.2013 11:48:45 Event time (UTC): 02.04.2013 11:48:45 Event ID: 09157c3457904c99b2b57abb44bef857 Event sequence: 5911 Event occurrence: 74 Event detail code: 0

Application information: Application domain: /LM/W3SVC/1273337584/ROOT-1-130093075659455176 Trust level: Full Application Virtual Path: / Application Path: E:\sitesroot\0\ Machine name: RD00155D36AB20

Process information: Process ID: 2420 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE

Exception information: Exception type: SqlException Exception message: The service is currently busy. Retry the request after 10 seconds. Incident ID: {26F49B32-6DD8-4149-89FA-780AF4BD74D3}. Code: 32771 A severe error occurred on the current command. The results, if any, should be discarded. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 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.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Request information: Request URL: http://www.x.x/ Request path: / User host address: x.x.x.x User:
Is authenticated: False Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information: Thread ID: 20 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 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.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Custom event details:

Event code: 3005 Event message: An unhandled exception has occurred. Event time: 02.04.2013 11:46:56 Event time (UTC): 02.04.2013 11:46:56 Event ID: a8dc5c34109e4a7d9f1c74bb697d86b8 Event sequence: 5905 Event occurrence: 72 Event detail code: 0

Application information: Application domain: /LM/W3SVC/1273337584/ROOT-1-130093075659455176 Trust level: Full Application Virtual Path: / Application Path: E:\sitesroot\0\ Machine name: RD00155D36AB20

Process information: Process ID: 2420 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE

Exception information: Exception type: SqlException Exception message: 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.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 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.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.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Request information: Request URL: http://www.x.x/ Request path: / User host address: x.x.x.x User:
Is authenticated: False Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information: Thread ID: 20 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 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.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.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Custom event details:

Event code: 3005 Event message: An unhandled exception has occurred. Event time: 02.04.2013 11:20:11 Event time (UTC): 02.04.2013 11:20:11 Event ID: cbf42c3adf63405293a803cb75577076 Event sequence: 5652 Event occurrence: 71 Event detail code: 0

Application information: Application domain: /LM/W3SVC/1273337584/ROOT-1-130093075659455176 Trust level: Full Application Virtual Path: / Application Path: E:\sitesroot\0\ Machine name: RD00155D36AB20

Process information: Process ID: 2420 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE

Exception information: Exception type: SqlException Exception message: Database 'xxx' on server 'xxx' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '30b6d33f-4976-44b8-a586-8bfe2bc66182'. Login failed for user 'xxx'. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user) at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Request information: Request URL: http://mail.x.x/FindInspector/Companies/128?InspectorCompaniesViewModel-page=5&InspectorCompaniesViewModel-sort=Telephone-asc Request path: /FindInspector/Companies/128 User host address: 66.249.76.91 User:
Is authenticated: False Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information: Thread ID: 21 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user) at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression`1 predicate) at Holte.UK.Service.People.PersonServiceCRUD.GetCompanyIdByUserProfile(Object userID) at Holte.UK.MvcApplication.Application_PostAcquireRequestState(Object sender, EventArgs e) at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Custom event details:

2
linq-to-sqlazureazure-sql-database

2 Answers

2
votes

These are very common transient conditions in SQL Azure. I guess you have not implemented any retry logic in your code.

Take a look, read through and implement Transient Fault Handling in your application.

0
votes

From my understanding, this issue can be quite random if you get it or not. There are around 600 Azure SQL installations/instances running on one SQL server. The Azure installations are sharing all the resources, mdf and ldf file (this is why you have to contact support to restore your db) on that server. Therefore, if you are unlucky and end up on a server where someone uses heavy DB operations then this will affect the performance of your Azure SQL.

If you are unlucky and get the transient error a lot can another solution be to create a new Azure SQL and move your DB over there. Hopefully this will place your new Azure SQL on a different server and with less heavy DB operations from others also using the server.