Error while upgrading Nav 2009 Sp1 to Nav 2013(SQL database)

Hi All,

When i click on transfer data in Nav 2009 SP1 to Nav 2013 upgrading process,getting this following error:

The following SQL Server error or errors occurred when accessing the Item Ledger Entry table:

1204,“HY000”,[Microsoft][ODBC SQL Server Driver][SQL Server]The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

  • Only one user using the database

  • Server restarted but giving same error

Kindly tell me the solution for the above issue.

Thanks & Regards,

Baba Guru.

This error is usually due to insufficient memory - to maintain locks, SQL Server needs RAM. Since such an upgrade is engaging a hell of locks, you need to have PLENTY of RAM!

What are the SQL Server specifications?

Thanks for the reply Mr.Stryk…would you please tell me how much ram is sufficient for 280 GB database?

How much RAM currently your SQL Server have ??

I am using SQL 2008 R2 64 bit Enterprise Edition

4 GB Ram

Are you doing up gradation on Server machine ???

Yes…am doing up gradation server machine…but no one using the database except me…

With just 4GB RAM the SQL Server would probably have 1.6 GB available (at maximum); this is definitely insufficient …

IMHO never ever install a SQL Server with less than 8GB; 16 or 32 GB should be considered as reasonable sizes (32GB RAM is Windows x64 STANDARD limit); with large databases and high transaction volume even more (e.g. 64GB = SQL STANDARD limit) …

One way to avoid this “not enough memory to create locks” problem is to place the database into “Single user” mode. A database in “single user” mode does not issue locks. It doesn’t need to since there are no other users.

But, as Stryk says, you still want more memory for other reasons.

Hi Mr.Stryk,

I have upgraded my server with 16 GB RAM for 280 GB database and kept that database in single user mode.but giving same issue.Would you please tell me the solution.

Thanks & Regards,

Baba Guru.

Try to take the backup and restore it in a new database and then perform your upgrade procedure…

this could work…

Anil.

Hi Anil,

I already performed what you suggested me.

Thanks * Regards,

Baba Guru.

I am not sure if this is possible for you… but i can help you online. i wanna take a session with you.

my skype ID : anilinecta

Anil.

Anil,

Kindly post your answers on forum itself rather than taking session for everyone …

This will help others also.

Hi All,

I have done right click on SQL server instance–>Properties–>Memory–>Under “server memory options”–>I modified the value of Maximum server memory(in MB) as 12288 from 4096.By changing the value any result will there?

Kindly tell me the solution…

As Mr.Stryk said that by keeping 64 GB RAM,we can solve the issue.Would you please confirm me by only upgrading 64 GB RAM in server can solve the issue?

Thanks & Regards,

Baba Guru.

Thanks Amol,

I agree with your suggestion… i only take session if i have to dig more deeper with the errors…

however i will try to follow your suggestion for the maximum cases.

Thanks once again

Anil.

Hi All,

Present my server have 20 GB Physical RAM and SQL server Maximum server memory(in MB) changed to 20480(As mentioned in previous post).Still i am getting the same error as

"**The following SQL Server error or errors occurred when accessing the Item Ledger Entry table:**1204,“HY000”,[Microsoft][ODBC SQL Server Driver][SQL Server]The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions."

Here I attached the Video File consists of CPU and RAM performance while running the transfer data in Nav 2013 Up gradation process. Kindly download this video and tell me the solution.

http://209.61.192.223/Navision/SRV-85-HV-Video.rar

Thanks & Regards,

Baba Guru.

Hi Baba,

Stryk didn’t say that adding 64 GB will solve your problem. He said that 64 GB is the maximum amount of RAM you can use with SQL.But he did suggest that you try with 32 GB. Although nobody can guarantee you that it works!

If you want somebody to “tell you the solution” then you should hire a SQL performance expert (like Stryk). Then he will solve your problem and tell you the solution. But then you’ll have to pay him. Here on DUG your co-members love to help you for free, point you in the (maybe) right direction, but don’t expect anyone just to “tell you the solution”. You have to do the work. [:)].

Amol,

Thanks for the suggestion… i take the suggestion only if i feel that i need to dig more deeper in to the problem or may be debug it…

i agree that the answers shud be posted in the forum…

but there are always few exceptions… solution to the problem is more important than protocols. [:)]

Anil.