Hi everyone,

My application is built on top of Umbraco v8, and I have written additional queries to fetch data from Umbraco tables (especially umbracoAccessRule and umbracoAccess) from Microsoft SQL database for the app business logics.

Currently, the application gets SQL exception "Execution Timeout Expired" from the DB intermittently. When the exception happens, the DB server still has enough resources (RAM, CPU) to handle it and the sleeping processes in DB are still waiting for next commands from the application.

The queries are executed using Scope.Database. I guess that this issue could be because of a connection leak or transaction locks.

        using (var scope = this.Scope.CreateScope())
        {
            var sql = new Sql()
            .Select("*")
            .From("umbracoAccess a")
            .LeftJoin("umbracoAccessRule ar")
            .On("a.id = ar.accessId")
            .Where("a.nodeId = @nodeId", new { nodeId });

            var result = scope.Database.FetchOneToMany<MasnetAccessDto>(accessDto => accessDto.Rules, sql)
                .SingleOrDefault();

            scope.Complete();
            return result;
        }

Error logs:

2023-10-05 23:13:04,601 [P2452/D5/T49] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (2f9319a2). System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 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 Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at NPoco.Database.ExecuteReaderHelper(DbCommand cmd) at NPoco.Database.ExecuteDataReader(DbCommand cmd) ClientConnectionId:fe4fdabc-bab0-4537-af1d-f03a08cc9cdb Error Number:-2,State:0,Class:11 2023-10-05 23:13:04,632 [P2452/D5/T49] DEBUG Umbraco.Core.Persistence.UmbracoDatabase - At: at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo) at System.Environment.get_StackTrace() at Umbraco.Core.Persistence.UmbracoDatabase.OnException(Exception ex) at NPoco.Database.OnExceptionInternal(Exception exception) at NPoco.Database.ExecuteDataReader(DbCommand cmd) at NPoco.Database.<QueryImp>d__1641.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at NPoco.Database.FetchOneToMany[T](Expression1 many, Sql sql) at Masnet.Web.DataModel.Persistence.Repositories.MasnetAccessRepository.GetMasnetAccessDto(Int32 nodeId)

Can anyone give me advice for this issue?

Thanks