Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 23 '12 at 0:38

This question came from our site for professional and enthusiast programmers.

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

1 Answer 1

up vote 9 down vote accepted

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.

share|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. –  Bruno Ligutti Dec 24 '12 at 2:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.