NAV 2015 data upgrade locks table even when stopped

rsaritzky

Hi all,

During a NAV2015 Data Upgrade, one of the steps is to update any fields linked to the "User Setup" table with Domain\Userid instead of just Userid. I received an error message that a field in one of our custom tables was too short. This field was overlooked when I updated all the userid-related fields to the new-standard length of 50.

I went ahead and changed the length of the field in the table, but I could not synchronize the data because NAV thinks that the table is locked by another process. I've stopped the service tier, but SP_WHO in SQL Management Studio still shows several SQL processes with the database open. And I can't restart the Data Upgrade until a synchronize data step on my custom table is completed (Data Upgrade reports an error when I start, saying a synchronize data step is required - which makes sense.)

I know from a prior test that if I stop SQL Server and restart, the database will have to go through a recovery, which will take about 4 hours - something I would prefer not to do.

Has anyone encountered this problem? Has anyone tried just killing the SQL process SPID's that appear to have the database locked? And if so, any negative impacts on the data recovery process?

Thanks

Ron

JoergR

Hi, you can try sp_who2 to get a little more details on the SPIDs.

also, consider that only SPIDs >50 are considered to be USer-related connections (they could be killed without great pain if your server is working correctly). Everything below is system-interal stuff, e.g. maintenance routines or listeners. i would not kill those manually.

i dont know if it is working with service tiers but normally you would switch a DB to single-user mode first.

regards

Phenno

If table is locked then it is locked by some process. That process is probably rollback of original transaction that failed for a reason you mentioned. Be aware that rollback can last for a while. If you restart SQL it will do rollback or recovery anyway so try to let it finish instead of restart.

You could check on SQL state of the process by issuing "kill spid with statusonly" which will give you estimate of rollback status. Spid is a process id which is holding lock.

JoergR

Hi, you can try sp_who2 to get a little more details on the SPIDs.

also, consider that only SPIDs >50 are considered to be USer-related connections (they could be killed without great pain if your server is working correctly). Everything below is system-interal stuff, e.g. maintenance routines or listeners. i would not kill those manually.

i dont know if it is working with service tiers but normally you would switch a DB to single-user mode first.

regards

rsaritzky

Joerg,

Thanks

Ron

rsaritzky

Followup - got another error regarding locks. The data upgrade (Step 2) failed with an error "'Array dimensions exceeded supported range". It is a large database with 28,650,000 ledger entry dimension records, but only 53 unique dimension sets. I wanted to try to restart the process, but now I'm getting an error that a record in the Item table is locked.

If I stop the service tier, the process then thinks the data upgrade hasn't started, but I can't re-start at the beginning because of the current tenant state "OperationalWithSyncPending". I know from experience that if I restart SQL, the database will go into recovery mode and take a long time to recover.

I'm not sure whether to try a "sync" or just kill SQL and let the database recover from that point.

Any ideas?

Thx

Ron