Will SQL Diff backup lock table?

allenyyzhou

Recently, we faced a problem with sql diff backup. Our client is using NAV 2013 R2, we take sql diff backup everynight, sometimes some users still use NAV at that timing, so there will be table lock happens and the NAV Instance just restart automatically. We asked Microsoft, they said this is normal because sql backup will lock table. Just want to confirm here in Mibuso, does anyone faced the same problem before?

Phenno

As far as I know no, backups do not produce locks on any level. You can check more info here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/

But, backups can make load on server which will produce slower executions of some procedures executed from NAV (or elsewhere) that do make locks which, in turn, will last longer than usually.

Phenno

As far as I know no, backups do not produce locks on any level. You can check more info here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/

But, backups can make load on server which will produce slower executions of some procedures executed from NAV (or elsewhere) that do make locks which, in turn, will last longer than usually.

allenyyzhou

Thank you, Phenno

I think I agree with you, backup should not cause deadlock. Last week I updated CU to the latest one, maybe that will solve my problem.