Table Locking issue while converting the Database

SmitaDubai · 2014-10-20

Dear Everyone,

We are doing data migration from version 2009 R2 to 2013.

After step 1 now we are trying to do the database conversion i.e opening the 2009 database in 2013 .

I got the following error.

Microsoft Dynamics NAV Development Environment

The G/L Entry table cannot be changed because it is locked by another user.

Wait until the user is finished and then try again.

OK

Single user mode is already enabled before starting the process.

Our database size is almost 300 GB. (Server RAM is 16 GB)

I have done through few sites, for these kind of issues, people are suggesting for more RAM & space. Ideally how much RAM & space is suggested for this big Database . Any other setup to be done in SQL ? (like shrinking of log file etc etc ??)

Please suggest what has to be checked in SQL server & what needs to be done before we again start the process.

SmitaDubai · 2014-10-21

Anyone Please help us in this.

We stated the conversion again . But the same issue came after 12/ 13 hours.

We have created enough space on the server , also Upgraded the RAM to 32 GB.

What might be the cause ??

How can we speed us this process ?? (Conversion i.e. Altering table)

Regards,
Smita

Marije_Brummel · 2014-10-21

Since the database is in single user mode, the only thing that can start a blocking transaction is your own session.

Assuming it is a block, you can check if there is anything in the G/L Entry table from SQL that causes this. Sometimes people create triggers on tables to synchronise data externally.

Alternatively you can disable all indexes and SIFT on SQL server to make the transaction simpler and avoid the indexed views to cause an unexpected issue.

If the problem still exists, I recommend to install the blocking monitoring tools by Jorg Stryk or to measure blocking using SQL Server Profiler just to check if this is really a block, or if NAV is just returing a wrong error message.

Good luck, hope this helps a little.

SmitaDubai · 2014-10-21

Thank you for the reply.

OK. I will disable all the secondary keys in GL entry table and start the process again.

Is it ok if I do this from NAV ??

Is it something time out kind of issue ? As the process is taking so much of time Is this something system is getting hanged ?

As a last option I will check the blocking monitor. Thanks a lot

davmac1 · 2014-10-21

When you get stuck in the conversion process, then go step by step until you get where it is failing. By step here I mean functional steps - each table or group of tables being converted.
Are you failing in upgrade step 1 or step 2?
If it is failing in NAV code then you can turn on the debugger when you get to the piece that is failing and see what the code is doing.

Before you do this - as Mark wrote - make sure no one got creative with database table triggers.

SmitaDubai · 2014-10-21

Dear David,

Step 1 I have completed successfully. After Step 1, we need to convert the database. Means, we need to open 2009 database in 2013 application.

While doing this conversion, i.e. Altering tables, I am getting above error of G/L entry locked .

This conversion is running successfully for 12/14 hrs. After that I am getting this error.

In conversion process, I cant switch on the debugger .

Please suggest.

davmac1 · 2014-10-22

This is strange to get this during this step.
Is it taking 12 hours to do just the opening of 2009 with NAV 2013?

If this is the case, you must have something else going on.
Have you looked at SQL profiler?

Table Locking issue while converting the Database

Comments

Categories