NAV backup locks tables in SQL

Dynamics 365 Business Central/NAV User Group Technical Forum
#1

I saw today an issue, on NAV SQL, where when they are creating a NAV backup (the nromal NAV backup from within NAV, not hotcopy, and not SQL backup), that its not possible to insert records.

In this particular case the Admin was creating a NAV backup, and a user was trying to create an Item, but got a message “You can not create the record because the Item Table is locked, please wait”. If the Admin aborts the Nav backup, then the Itme inserts no problem.

Is this normal? SQ

(PS this is not my job, and I am not going to fix it, someone else is working on that), I am just curious as to why a backup is locking the Item Table. And yes they do plan to move to SQL backups, so the issue will go away in the end.

#2

Hi David

That is normal, as soon as a table starts to be backed up it is locked until the backup has finished.

It is normal on SQL to use SQL backups as they are much quicker and easy to schedule and it does not lock the tables.

Paul Baxter

#3

Thanks Paul,

Sure the client is normally using SQL backup, but this morning they did a NAV backup whilst users were on line.

#4

David,

That feature it’s documented, Navision locks objects during backup to read only. It is one of the reasons why it’s recommend to use SQL Server backup tool.

From Navision SQL Option Document

#5

And it is also possible that when you are at 90% of the backup, you session becomes a deadlock-victim! [:’(] [:@]

I have already encountered this.

#6

Yes, it’s possible. Even during security synchronization using enhanced security model you can achieve that.
When that appends I normally open SQL Enterprise Manager check the locking user / process and kill it to continue with operation.

#7

OK, this makes sense. Originally the process was deteceted when a Dead lock was generated, but today when they tested again, they found it happened when doing a backup.

I was just curious to know what was going on.

Thanks everyone for the help.