Error "The Instance of The SQL Server Database Engine Cannot Obtain a LOCK Resource at This Time" in Archer

[KB-Sync1]

Article Number

000033959

Applies To

Product(s): Archer
Version(s): All Versions
Primary Deployment: On Premises

---

Description

When working in the Administrative workspace, an unexpected error occurs with a log reference ID.

The Below error is logged in Archer W3WP logs.

<ApplicationData>
        <TraceData>
            <DataItem>
                <TraceRecord Severity="Error" xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord">
                   
                    <UserId>xxx</UserId>
                    <LogReferenceId>XXXXXX-XXXXXX-XXXX</LogReferenceId>
                    <Description>The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.</Description>
                    <AppDomain>xxxxxxxxxxxxxxxxxx</AppDomain>
                    <Exception>
                        <ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx</ExceptionType>
                        <Message>The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.</Message>
                        <Source>.Net SqlClient Data Provider</Source>
                        <ContextData>
                            <HelpLink.ProdName>Microsoft SQL Server</HelpLink.ProdName>
                            <HelpLink.ProdVer>12.00.4213</HelpLink.ProdVer>
                            <HelpLink.EvtSrc>MSSQLServer</HelpLink.EvtSrc>
                            <HelpLink.EvtID>1204</HelpLink.EvtID>
                            <HelpLink.BaseHelpUrl>http://go.microsoft.com/fwlink</HelpLink.BaseHelpUrl>
                            <HelpLink.LinkId>20476</HelpLink.LinkId>
                        </ContextData>
                        <StackTrace>   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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&amp; dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean&amp; moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean&amp; more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at ArcherTech.Kernel.DataSource.Db.LevelLayoutSectionBaseDataSource.Get(IList`1 tabList)
   at ArcherTech.Kernel.Brokers.LevelLayoutBroker.GetSections(IList`1 tabList)
   at ArcherTech.Kernel.Brokers.LevelLayoutBroker.Load(IEnumerable`1 levelLayoutIds)
   at ArcherTech.Kernel.Providers.CacheProviderHelper.Get[T,K](CacheProvider cacheProvider, SessionContext sessionContext, String cacheName, IEnumerable`1 ids, GetDomainList`2 getNonCachedObjects, GetDomainKey`1 getDomainKey, Func`2 getCacheLookupKey, Func`2 getUnavailableDomains, Func`2 ValidDomain)
   at ArcherTech.Kernel.Providers.CacheProviderHelper.Get[T,K](SessionContext sessionContext, String cacheName, IEnumerable`1 ids, GetDomainList`2 getNonCachedObjects, GetDomainKey`1 getDomainKey, Func`2 getLookupCacheKey, Func`2 getUnavailableDomains, Func`2 getFilteredDomains)
   at ArcherTech.Kernel.Providers.CacheProviderHelper.Get[T,K](SessionContext sessionContext, String cacheName, IEnumerable`1 ids, GetDomainList`2 getNonCachedObjects, GetDomainKey`1 getDomainKey)
   at ArcherTech.Kernel.Providers.CacheProviderHelper.Get[T](SessionContext sessionContext, String cacheName, IEnumerable`1 ids, GetDomainList`2 getNonCachedObjects)
   at ArcherTech.Kernel.Brokers.LevelLayoutBroker.Get(IList`1 levelLayoutIds, Boolean checkLevelPermission)
   at ArcherTech.Kernel.Brokers.EventBroker.PermissionLayouts(IList`1 layoutIds)
   at ArcherTech.Kernel.Brokers.EventBroker.GetEventActionsByLayoutInternal(IEnumerable`1 layoutIds, Boolean checkPermissions)
   at ArcherTech.Kernel.Managers.EventManager.GetEventActionsByLayout(SessionContext sessionContext, IEnumerable`1 layoutIds)
   at SyncInvokeGetEventActionsByLayout(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</StackTrace>
                    </Exception>
                </TraceRecord>
            </DataItem>
        </TraceData>
    </ApplicationData>
</E2ETraceEvent>

Cause

The SQL Server is configured to a fixed number of locks (e.g. 5,000)

Resolution

Set the Locks to 0 (zero) in Microsoft SQL Studio Manager. This allows the server to dynamically set the number of locks to the amount is required by the Database.

Set Locks to Dynamic Allocation

1. In Object Explorer, right-click the server and select Properties.
2. Click on the Advanced node.
3. Under Parallelism, Change the Locks option to 0 (zero).
4. Click OK.

0EMVM000005LDVd.png

Notes

Reference information is available on Microsoft website:
https://support.microsoft.com/en-au/kb/2199576
https://msdn.microsoft.com/en-us/library/aa337440.aspx