Error: Number (1204) Severity (17). Running out of locks RRS feed

  • Question

  • Hi,

    First of all, my apology. I'm not sure if this is the right SQL server forum. Moreover, this didn't even happen on SQL Server but Sybase 11.9. But I'm thinking that the same situation might happen on SQL Server and I can't find any Sybase forum to run this by some experts.

    I got the following error on a dev server:
    Error:  Number (1204) Severity (17). SQL Server has run out of LOCKS.  Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure SQL Server with more LOCKS

    The number of locks was set to 10,000.
    I set it to 15,000 and ran the same code: Same error.
    I set it to 20,000 and ran the same code: No error.
    I set it back to 15,000 and ran the same code: No error.
    I set it to 10,000 and ran the same code: No error.
    I set it to 5,000 and ran the same code: No error.

    It seems strange that it used not to work at 10,000 locks (repeatedlly) but once I set the number of locks to 20,000 once, then it worked with the number of locks as low as 5,000. Does anybody have any idea why this would happen?

    Another question is that when the number of locks is set to 10,000, I run sp_lock while the code is running and the number of rows returned was often over 10,000. Still, I didn't get the error. Anybody knows why?

    Did anybody ever run into a similar situation?

    Appreciate your help.

    Wednesday, September 13, 2006 7:07 PM

All replies

  • Hello,

    OK, first up, MSSQL hasn't been related to Sybase since 6.5, but anyway...

    Check your lock escalation, as the number and type (page/extent etc) of locks acquired will depend upon several factors.

    Myabe you should consider upgrading to Microsoft SQL Server?

    Thursday, September 14, 2006 1:01 AM