PRB: Calculating Memory Used By Locks in SQL Server

ID: Q81386

The information in this article applies to:

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.

Additional query words: 1204 bcp Windows NT 


Keywords          : kbother SSrvAdmin SSrvLock 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 12, 1999