PRB: Calculating Memory Used By Locks in SQL Server

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SYMPTOMS

When configuring Microsoft SQL Server version 4.2 or SQL Server version 4.2 for OS/2, the default value for the number of locks is 5000, with a maximum value of 50,000. Leaving this parameter set to 5000 is sufficient for most applications. However, if SQL Server requires more locks than what it is configured for, an error message will be displayed similar to the following:

   SQL Server has run out of LOCKS. Re-run your command when there
   are fewer active users, or ask your System Administrator to
   reconfigure SQL Server with more LOCKS.
   (Msg 1204, Level 19, State 2).

CAUSE

This error may occur because of a large number of transactions running concurrently, or because of one or more transactions that are inserting, updating, or deleting a large number of rows. The error may also occur when a user is bulk copying a large number of rows into SQL Server, because the data pages into which those rows are being inserted will need to have locks on them until the transaction is completed.

WORKAROUND

If this error is encountered, the parameter for the locks can be increased using sp_configure. Each lock requires 32 bytes of memory, which is allocated statically when SQL Server is started. Therefore, the value for LOCKS should be set high enough to avoid running out of locks. Note however that the memory allocated to LOCKS reduces the amount of memory available for the SQL Server cache.

With SQL Server version 4.2, the locks may be set to a maximum of 500,000.