Small and medium business | Business Central, N...

Error = The operation could not complete because a record was locked by another user. Please retry the activity.

(0) Share

Report

Posted on by A.Papaspyridis

Hi everybody,

the last two weeks many locking errors are occuring randomly, and the event viewer is showing the following: Error = The operation could not complete because a record was locked by another user. Please retry the activity.

Mainly it occurs when inserts are made in Change Log Entry Table, ie this morning the following happened:

EventTime: ‎2022‎-‎12‎-‎02T08:09:35.328113700Z
Message <ii>Exception happened during threshold Trace Tag. Execution Time = 10002 milliseconds
Error = The operation could not complete because a record was locked by another user. Please retry the activity.
Tag = 000007L
Message = Long running SQL statement
Task ID: 3
Statement: INSERT INTO "NAV2017".dbo."LIVE$Change Log Entry" ....
AppObjectType: CodeUnit
AppObjectId: 423
AL CallStack: "Change Log Management"(CodeUnit 423).InsertLogEntry line 49
"Change Log Management"(CodeUnit 423).LogModification line 20
ApplicationManagement(CodeUnit 1).OnDatabaseModify line 1
</ii>
ProcessId: 3184

As is referred in other similar community posts for this error, every NAV SQL statement with execution time over 10000 milliseconds may bring a lock in the record. Since the code hasn't been changed and a maintenance plan for rebuilding indexes runs every night, is there anything else we can do with the data(ie delete / archive change log records) or some NAV instance configuration change to happen in order the sql statements to do not take so long?

Any help will be much appreciated

Kind regards

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Error = The operation could not complete because a record was locked by another user. Please retry the activity.

Rebuild indexes might not be always the silver bullet. When you run rebuild indexes, usually two things happen: indexes are actually re-build and also statistics is updated. Statistics can be tricky.

Unmodified statistics may cause the performance degradation during the day-time after midnight index rebuild, as (roughly) by default it rebuilds when there are more than 20% changes in the row data. So if you have added/modified 200,000 rows to a table which has 1 million rows, statistics can still be old but the performance might degrade.

To fix this, there is a trace flag 2371 in SQL settings, available starting from SQL 2008 R2 SP1, which reduces this threshold and might boost your performance (use DBCC TRACEON (2371,-1) )

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 90,252 Super User 2025 Season 2 on at

Like (0)

Report

RE: Error = The operation could not complete because a record was locked by another user. Please retry the activity.

Hi, In BC, Microsoft added the following warning.

Tracking changes can impact performance, which can cost you time, and increase the size of your database, which might cost you money. To reduce those costs, consider the following:

- Use caution when choosing the tables and operations.

- Do not add ledger entries and posted documents. Instead, prioritize system fields such as Created By and Created Date.

- Do not use the All Fields tracking type. Instead, choose Some Fields and track only the most important fields.

Therefore, you may enable the detection of many fields. It is recommended to export these data from the database, and then try again after cleaning the relevant data in the Change Log.

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Gearoid on at

Like (0)

Report

RE: Error = The operation could not complete because a record was locked by another user. Please retry the activity.

Hi,

Seems like you have a SQL blocking issue on the "Change Log Entry" table. If your DBA runs the TSQL statement "sp_who2" at the time this blocking is occurring you may be able to identify the blocking connection and see what is it doing.

Check if there are any batch jobs executing in the NAV SQL database which might lock the "Change Log Entry" table.

Perhaps seme extra tables have been enabled for Change Logging in the last two weeks thus increasing the contention around the "Change Log Entry" table.

You mentioned that indexes are rebuilt every night by a maintenance plan. It may be worth checking if this maintenance plan is only rebuilding certain indexes (based on fragmentation level). If so, then other indexes which are not fragmented may not be rebuilt very often. For these indexes make sure that the index statistics are being refreshed (e.g. by another SQL maintenance plan). If any index statistics become stale it can cause slower queries and increased blocking.

If you increase the NAV instance configuration option "Data Cache Size" this will allow more RAM on the NAV Server for data caching purposes which can reduce the number of round trips to SQL Server and thus improve general SQL performance. This would only be an option if you have free RAM on the NAV Server.

Another option you could consider is increasing the lock timeout to a higher value than 10 seconds.

Was this reply helpful? Yes No