I just created a clustered index on a 1MM row table (page and row locking turned on). I now try to run queries and I get this error:

"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."

I will need to adjust the page/ row locking settings later, but in the meantime I need to drop the index because I have users querying the table. I tried to drop it, but I received the same error. Any recommendations to drop the index for good? Thank you.

EDIT 1: Based on Remus answer below, I ran the following script to enable dynamic allocation of memory for locks.

EXEC sp_CONFIGURE 'show advanced options', 1 RECONFIGURE
EXEC sp_configure 'locks', 0 RECONFIGURE

Then, I restarted the server.

  • 4
    run DBCC MEMORYSTATUS and attach the output. – Remus Rusanu Dec 22 '12 at 17:23
  • @Remus I appreciate it, I will check it now. I could remove the index but I am getting the same LOCK error when sending a simple query to the table. Any clues? Many thanks for the help. – Bruno Ligutti Dec 22 '12 at 17:33

Follow the How to troubleshoot SQL Server lock related problems and errors steps.

There are millions of tables with clustered indexes out there that do not run into lock exhaustion issues. There is something more at play here.

|improve this answer|||||
  • +1. Nice answer. My guess is the last option...be interested to find out the actual reason... – Mitch Wheat Dec 23 '12 at 0:19
  • @Remus I really appreciate your help. It was actually #1, and I will post an answer on how I could solve it. Many thanks again. – user16471 Dec 24 '12 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy