Hi,

For the last several month I'm getting following error from Azure SQL. It can happen several times per day or per week. It's really unpredictable.

2015-01-15 02:41:20,352 (null) [12] ERROR XXX.Service.MailerDaemonSvc.Infrastructure.MailerDaemon - Unhandled exception has occured!

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: 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.). Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: 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.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host

   --- End of inner exception stack trace ---

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()

   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()

   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()

   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   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.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 108

   --- End of inner exception stack trace ---

   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 120

   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String& failureErrorText) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\EntityMaterializerBase.cs:line 154

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 607

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 4271

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 2206

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClassd6.<FetchEntityCollection>b__d5() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 224

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 517

   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 224

   at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery[TCollection](IDataAccessAdapter adapter, IEntityQueryInternal query, TCollection collectionToFill) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QuerySpec\AdapterSpecific\AdapterExtensionMethods.cs:line 376

   at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery(IDataAccessAdapter adapter, IEntityQueryInternal query) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.2\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QuerySpec\AdapterSpecific\AdapterExtensionMethods.cs:line 399

   at XXX.Data.LLBLGenDataRepositoryQuerySpec`1.<>c__DisplayClass1`1.<QueryCollectionViaFactoryWithAdapter>b__0(QueryFactory qf, TDataAccessAdapterBase adapter) in c:\ZZZ\XXX\XXX\XXX.Data\LLBLGenDataRepositoryQuerySpec.cs:line 38

   at XXX.Data.LLBLGenDataRepositoryQuerySpec`1.QueryViaFactoryWithAdapter[T](Func`3 selector) in c:\ZZZ\XXX\XXX\XXX.Data\LLBLGenDataRepositoryQuerySpec.cs:line 29

   at XXX.Data.LLBLGenDataRepositoryQuerySpec`1.QueryCollectionViaFactoryWithAdapter[T](Func`2 selector) in c:\ZZZ\XXX\XXX\XXX.Data\LLBLGenDataRepositoryQuerySpec.cs:line 35

   at XXX.Data.MailerJobRepository.GetSubmittedForProcessing(Nullable`1 numberOfRecordsToFetch) in c:\ZZZ\XXX\XXX\XXX.Data\Data Repositories\MailerJobRepository.cs:line 65

   at XXX.Business.Managers.MailerManager.GetSubmittedForProcessing(Nullable`1 numberOfRecordsToFetch) in c:\ZZZ\XXX\XXX\XXX.Business.Managers\Managers\MailerManager.cs:line 273

   at XXX.Service.MailerDaemonSvc.Infrastructure.MailerDaemon.DoWork() in c:\ZZZ\XXX\XXX\XXX.Service.MailerDaemonSvc\Infrastructure\MailerDaemon.cs:line 46

This is coming from Windows Service applications, they periodically poll for new jobs (every 15-20 seconds), website doesn't have heavy load atm, so I assume it is also prone to this. I found that LLBLGen supports "Transient Error Recovery" and that was a huge relief that you guys are handling at least some Azure failures (gigantic thanks for that!!!) I'm planning to implement a partial class for DataAccessAdapter and utilize SqlAzureRecoveryStrategy. So I have a couple of questions:

1) reading that manual page - is my understanding correct that in a partial class I will just "new SqlAzureRecoveryStrategy" inside CreateRecoveryStrategyToUse?

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace XXX.LLBLGen.Data.SqlServerDBSpecific
{
    public partial class DataAccessAdapter
    {
        protected override RecoveryStrategyBase CreateRecoveryStrategyToUse()
        {
            return new SqlAzureRecoveryStrategy();
        }
    }
}

and that should do the treak for all adapter instances and thread safe?

2) Will the exception mentioned above be handled by that strategy? I don't see it in the list http://www.getcodesamples.com/src/A122E27E/5586EA7F, the closest one is 10053, but in my case it says that remote has closed the connection, not local. If it won't be handled I'd appreciate if you happen to know the error code for it so I can inherit from SqlAzureRecoveryStrategy and handle it.

3) I'm using SaveEntity, SaveEntityCollection, UpdateEntitiesDirectly, DeleteEntity, DeleteEntitiesDirectly and TransactionScope. Is there anything I should know, i.e. which ones will not be covered by the strategy or will behave differently etc.

Thank you very much. Hoping to get the detailed answer

Thank you!

Findev wrote:

1) reading that manual page - is my understanding correct that in a partial class I will just "new SqlAzureRecoveryStrategy" inside CreateRecoveryStrategyToUse?

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace XXX.LLBLGen.Data.SqlServerDBSpecific
{
    public partial class DataAccessAdapter
    {
        protected override RecoveryStrategyBase CreateRecoveryStrategyToUse()
        {
            return new SqlAzureRecoveryStrategy();
        }
    }
}

and that should do the treak for all adapter instances and thread safe?

Yes, that is enough to make the strategy to be used in all DataAccessAdapter instances.

Findev wrote:

2) Will the exception mentioned above be handled by that strategy? I don't see it in the list http://www.getcodesamples.com/src/A122E27E/5586EA7F, the closest one is 10053, but in my case it says that remote has closed the connection, not local. If it won't be handled I'd appreciate if you happen to know the error code for it so I can inherit from SqlAzureRecoveryStrategy and handle it.

To be honest, I'm not sure, as there is no info about the error code in your exception message. However, it looks like it's covered by case Error 10053 (SQL Error Code: 10054). The best way would be to inspect the exception when it occur. If you can't reproduce it in a controlled environment, write all the exception information (not just the message) in your own log, to evaluate them later (and write your recovery strategy). As I see, this is not a timeout nor a sql error, but something in the middle, in the communication. To handle this you could create your own strategy that inherits from SqlAzureRecoveryStrategy. Then you must override IsTransientException, then use the base.IsTransientException + your own checks, or simply rewrite it all.