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.