ASE has run out of LOCKS - Export/Import or SPAM/SAINT Error

Sometimes will face the below errors while doing Export/Import, SPAM/SAINT or upgrade actvity in SAP with Sybase ASE.

You will face dump like below,

Or if you check DB log (SID.log), You can find the below error messages,

ASE 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 ASE with more LOCKS.
Error: 1204, Severity: 17, State: 2 occurred for User ''. Client IP address is 'Unknown'.
server  SQL Text: [no text]

Solution :-

We need to increase the number of locks configured in Sybase DB.
So first check the current lock status in DB using below commands,

1> use master
2> go
1> sp_helpconfig 'number of locks'
2> go

This will give the current status like below,

Minimum Value   Maximum Value   Default Value   Current Value   Memory Used  Unit   Type
--------------- --------------- --------------- --------------- ------------- ------ -------
            1000      2147483647           10000           10000        2532  number dynamic
Configuration parameter, 'number of locks', will consume 4910K of memory if configured at 20000.
Changing the value of 'number of locks' to '20000' increases the amount of memory ASE uses by 2378 K.
(return status = 0)

The default value is 10000
So to overcome this issue we can change the value very large over current setting using below command.

1> use master
2> go
1> sp_helpconfig 'number of locks', <New Value>
2> go

Example :-

Restart not required for this activity, So once we changed the value we can repeat the activity.
Once the activity completed we can change the lock value to older one or maintain normal value.

Thanks for reading, Please share your valuable feedback.  

Share this

Basis Genie