LOCK error after creating a clustered index

Question

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.

Answer 1

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

• make sure locks option is 0, see Keep the Locks Configuration Option Default Value
• make sure lock escalation disabling trace flags are not in place, see Lock Escalation
• check your memory consumption, run DBCC MEMORYSTATUS, see How to use the DBCC MEMORYSTATUS command to monitor memory usage
• make sure you do not have open transactions with many locks, see sys.dm_tran_locks and sys.dm_tran_database_transactions

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.