SQL Lock and Memory Error

hi all, i have this error message when i tried to restore a backup containing 30 companies to SQL database. the error message is: 1204, “HY000”, [Microsoft][ODBC SQL Server Driver][SQL Server] The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. i have checked the microsoft website and it says something about increasing the no. of locks or something… but i just don’t get it.

You have to be the only user on the system in order to be able to preform a Navision backup from an installation using SQL server. Switch your database to single user modus and try again.

hi ingeb, the error happened when i was trying to restore a navision backup, not while performing a backup. in addition to that, during the process of restoration, i am the only user in the navision database.

hi jordan, but have u switched ur database to single user mode? although you are the only user in sql server, you would still need to set it to single user mode (i think).

Unless your hardware is very very weak, i don’t think the error your getting is due to low resources on your server. I have done a lot of backup/restore from SQL installations and have also seen this error a few times. In thoose situation it has helped me to go to single user mode on the server. I think it is worth a try.

apparently, i have a reply from navision, that says that the restoration of companies in requires a lot of locks on the tables. and a lot of resources are used in this process. but they cannot give me a definite no. of memory required for this. the h/w config of my server is 512MB W2K server with MS SQL. and i can only restore up to 28 companies. even if i were to break up the restoration process in 10 companies, the error still occurrs. i think having a lot of companies in Sql server in navision is a definite no.

When you place your database in single-user mode (you can use Enterprise Manager, or Attain File/Database/Alter-Options tab), SQL Server does not place locks since it knows you are the only connection to this database, so multi-user considerations are not relevant. Do this and you will not have a problem with lock resouurces.

hi cinkaid, i have tried that out and it works. but what is the effect to the system after i have disabled the single user mode? e.g. will renaming fields, modifying the table structure, add keys and other taxing database operations, give me the LOCK error again? of course, i am in the midst of trying it out myself. and will update you when i am done.

These other operations you mention are far less lock intensive than a multi-company restore. Firstly, most of them are on single-tables only, within a transaction transaction - so there are simply less locks being placed. I have never heard of a case where the server is out of locks for this, for reasonable hardware. A ‘Restore’ locks all tables being restored and must range-lock rows to ensure serializable transaction isolation, so there are many locks needed. It is very much a special case. Where possible, use SQL Server backup/restore. The Attain backup/restore is basically an upgrade/import/export capabilibty for the SQL version. It would be sensible in future versions for Attain to perform more course-grained locking during a restore which would also solve this problem. I expect this might happen soon.