SBX - Search With Button

SBX - Forum Post Title

Business Central On-Premises sync-navtenant error: the operation could not complete because a record was locked by another user

Microsoft Dynamics NAV Forum

technique asked a question on 16 Jan 2019 6:23 AM
My Badges

Question Status

Suggested Answer

I've just recently upgraded from CU1 to CU2 of Business Central on-premises and now I can't synchronise the schema via Powershell & the NAV RTC will not run at all. 

The error makes no sense as I've closed all sessions and other than the BC130 service nothing is accessing the database (other than my Powershell command).

The exact error is:

sync-navtenant : The operation could not complete because a record was locked by another user. Please retry the activity.

At line:1 char:1
+ sync-navtenant bc130
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Sync-NAVTenant], NavCommandException
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$bc130,Microsoft.Dynamics.Nav.Management.Cmdlets.SyncNavTenant

This worked fine in CU1 but I have to use CU2 so I have the latest objects and application for 'Making Tax Digital'.

In CU2 I can still compile the tables 'with validation' perfectly. 

But attempting to run the RTC produces the following error: 'The system is not accessible'.

This is a very frustrating show-stopper as I have no clue what is causing this & can't get past it.  PLEASE help!?

Reply
technique responded on 17 Jan 2019 5:55 AM
My Badges

SOLVED IT ...

When you open the old database with the new CU02 client and NAV performs the automatic database upgrade task it is enabling change tracking in SQL on the following tables...

Add-in

Object Metadata

NAV App Object Metadata

NAV App Tenant App

NAV App

NAV App Publish Reference

Debugger Breakpoint

By resetting these ie. turning off Change Tracking I have solved the issue.

This must be a bug with Cumulative Update 2 as this doesn't occur with CU1 and I can't see why MS would make such a (stupid) deal breaking change... !

Reply
ekeukens responded on 18 Jan 2019 4:13 AM

I have exactly the same problem. Even if I upgrade to CU1 first and then restore the database to CU2 this problem occurs even if there's no database migration between CU1 and CU2.

You're wright, this must be some bug in CU2.

Can you tell me what you mean by [Change Tracking]? Do you mean change log functionality within the functionality, or do you mean some property on Nav Server.

Reply
technique responded on 18 Jan 2019 7:58 AM
My Badges

It’s within SQL.  If you google it there are scripts that let you view the property & scripts to turn it on or off, using SQL Server Management Studio.  If you can’t find let me know & I will post something here for you.

I noticed CU3 was released yesterday so I’d be interested to know if this issue is just CU2 or CU2 & beyond...

Reply
ekeukens responded on 18 Jan 2019 8:19 AM

I found it, but this property seems not to be my problem. Most of my database have this problems, only in 1 database sync runs OK. But in all databases this SQL property is the same.

I will post it in a yammer group to check/ask MS.

If I upgrade a database from 10.00 to 13.00 I don't have any problems in CU0/CU1. If I install CU2 I have problems (in migrating 10.00 to 13.00, but also in databases which were upgraded OK using CU1).

I will download CU3 and test..

Reply
Michel van den Heijkant responded on 24 Jan 2019 11:22 AM

Did anyone get an answer from Microsoft or have a solution, because this also happens to me when upgrading from Nav 2018W1 CU6 to Dynamics OnPremise W1 CU2.

What I read about disabling change tracking on some tables, didn't or can't work because whe using nav-synctenant it fails because it detect that change tracking is disabled.

So I have to enable change tracking , and I'm back where i started.

Reply
gopapsi responded on 26 Jan 2019 4:53 PM

I have the same issue in CU3. It's stitll not solved.

Reply
gopapsi responded on 27 Jan 2019 9:16 AM

I also tried to disable change tracking in the few tables that had it turned on, but Synch Tenant complains that tracking is off for those tables then. I will send MS a support request.

Reply
Michel van den Heijkant responded on 28 Jan 2019 12:14 PM
Suggested Answer

Received an answer from microsoft, and got it working again:

"The way we saw how to work around this, is simply if you can make sure, with a fresh backup of this databse after converting it in the new version, and directly compile and synchronize system tables before doing anything else, sometimes also the sync with force is the ultimate solution here. Once you do this with the system table first, the rest should not be an issue."

So after converting, compile the system tables with force first before continuing your upgrade script.

But be aware, when doing the actual upgrade with "Start-NavDataUpgrade -ServerInstance <instancename> " , I received several "a record was locked by another user" errors. I had to resume the Upgrade several times, and also remove all add-in records.

Check why the upgrade faildes with Get-NavDataUpgrade -ServerInstance <instancename> -ErrorOnly. And also try to resume the upgrade from the developer environment.

Reply
ekeukens responded on 28 Jan 2019 1:13 PM

Not sure if this is the solution. I'm aware of compiling systems tables. I wonder why they say "sometimes also the sync with force is the ultimate solution here", because we deliver 1 standard upgrade script for all databases/customers. So ther emust be 1 fixed procedure.

To me it looks there's a difference in CU1 and CU2 (and up). I'm waiting for more comments of MS team on yammer..

Reply
gopapsi responded on 28 Jan 2019 1:17 PM

I agree. I tried doing force and I still get the errors. I am also waiting for MS support.

Reply
Michel van den Heijkant responded on 28 Jan 2019 1:26 PM

It's too bad if it doesn't work for you. otherwise, just give it a try. I didn't'have full confidence if this would work, but it did.

Don't depend on it to be resolved quickly.

Using theire quote : "Parallel to this we are trying to invest more in our repro, and see if we can involve our product team on it soon"

It doesn't look like it's  having theire utmost priority, because of the given quickfix.

Reply
Bart van Beek responded on 9 Feb 2019 4:17 AM

Same problem here!

- CU01 = No Problem

- CU02 and CU02 has the Sync-NAVTenant problem:

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

 

What I tried:

1. Compile System Tables in Development Client : ID=2000000000.. twice

First time I get the same error: 

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

Second time I don't get this error.

But the Sync-NAV-Tentant still give this error.

Also restart and wait a long time does not fix this error

2. Disable Change Tracking as suggested on mibuso

But than I get this error:

Sync-NAVTenant : The following SQL error was unexpected:Change tracking is not enabled on table 'dbo.Object Metadata'.

3. Sync with option

-Mode ForceSync

-CommitPerTable

4. Sync with CU01

Then I don't get the error, after sync it and change back to CU02/CU03 I got the same error again. So we cannot go live with it


...nothing helps me.

Please Microsoft can you fix this problem, not with a workaround but a solid repeatable fix.

Reply
Rene Gayer (MVP) responded on 11 Feb 2019 3:43 AM
My Badges
Suggested Answer

Hi all, I tried all your ideas, no success. For me this helps:

www.dynamicsblog.at/sync-navtenant-the-operation-could-not-complete-because-a-record-was-locked-by-another-user-please-retry-the-activity

("My solution" at the end of the post)

/Rene

Reply
ekeukens responded on 11 Feb 2019 4:08 AM

It seems I've struggled into some kind of work around.

It is related to the compile system tables (see above). In my case I need to run this twice (and ignore error messages in the first attempt).

We always used compiling system tables in upgrade procedure (but only the [later] option).

The second thing I needed to do was add [NavServerName] and [NavServerInstance] in the compile cmdlet. We never used these in this process (even if instance was required in the past, because if there's a server running it shouldn't be necessary). So it looks like this sequence (create/start server and force twice with adding instancename) is working towards a synchronized schema (see example below, green lines is script running OK):

I've asked Microsoft if it is OK to use Force twice at customer database upgrades.. Waiting for answer..


 

Reply
Rene Gayer (MVP) responded on 11 Feb 2019 4:31 AM
My Badges

Forcing schema-sync depends on data. You can force as often you like, as you are aware of what happens with data..

However, good to know that your steps works for you. :)

I recompiled more than one time, with PowerShell and with the client. I also replaced all system tables to be sure everything is fine. I started the schema-sync more than one time  and a lot more :(

However, it seems like there is some issue around it depending on data. I was in touch with Microsoft today, and they are aware of the issue. They don't know yet what is the reason.

But if we have some workarounds, like yours and mine we can continue :)

btw Update 4 is coming soon :)

Rene

Reply
Michel van den Heijkant responded on 28 Jan 2019 12:14 PM
Suggested Answer

Received an answer from microsoft, and got it working again:

"The way we saw how to work around this, is simply if you can make sure, with a fresh backup of this databse after converting it in the new version, and directly compile and synchronize system tables before doing anything else, sometimes also the sync with force is the ultimate solution here. Once you do this with the system table first, the rest should not be an issue."

So after converting, compile the system tables with force first before continuing your upgrade script.

But be aware, when doing the actual upgrade with "Start-NavDataUpgrade -ServerInstance <instancename> " , I received several "a record was locked by another user" errors. I had to resume the Upgrade several times, and also remove all add-in records.

Check why the upgrade faildes with Get-NavDataUpgrade -ServerInstance <instancename> -ErrorOnly. And also try to resume the upgrade from the developer environment.

Reply
Rene Gayer (MVP) responded on 11 Feb 2019 3:43 AM
My Badges
Suggested Answer

Hi all, I tried all your ideas, no success. For me this helps:

www.dynamicsblog.at/sync-navtenant-the-operation-could-not-complete-because-a-record-was-locked-by-another-user-please-retry-the-activity

("My solution" at the end of the post)

/Rene

Reply

SBX - Two Col Forum

SBX - Migrated JS