Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Location
    India
    Posts
    50

    Unanswered: ASE has run out of LOCKS

    Hi,

    Now a days, on test server i am getting below error message, on daily basis. I have reconfigured number of locks to additional 50%, but the next day, I again receive the same error. This looks to be recurring issue.

    Could you please help me in identifying the source of this problem? How do we monitor this?

    Error:

    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.
    Last edited by Neevarp; 10-19-09 at 10:34.

  2. #2
    Join Date
    Feb 2007
    Location
    India
    Posts
    56
    Hi,
    Please note down the timings from ASE error logs. If it is occuring at same time for each day. At that particular time, run sp_monitorconfig "number of locks" and also please try to select * from syslocks and also select * from sysprocesses where suid!=0.sp_lock output will show the spid also. run dbcc traceon(3604) and dbcc sqltext(spid collected from sp_lock output). you will come to know which stmt is using more lock here. Run this job for every 10 mins. So that you will come to know which query is using more locks analyse them. other wise sp_monitorconfig will give exact utilization value. try to increase that value.
    Regards,
    Naveen.
    Sybase DBA-Consultant

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Look at the lockcount by using sp_configure option. This will tell you the number of locks assgined by ASE server.

    (or)


    run sp_lock to identify which query helds more locks.

  4. #4
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Hi,

    Thanks for the info. This helped me in analyzing & resolving the issue. How do we estimate what should be the count of no. of locks to be configured on a ASE. i.e is there any hard rule for locks in sybase?

  5. #5
    Join Date
    Apr 2008
    Posts
    7
    Hi ,
    U can use sp_monitorconfig "number of locks" --- the o/p will contain one column named Max_used .Set the number of locks to 10% more thn this value.
    u can also use sp_helpconfig "number of locks","value" to knw how much memory will be needed to increase the parameter to this value...
    I thnk estimating hw much number of locks should be set depends upon testing done.

  6. #6
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Actually, the number of locks required to be configured for the server is primarily dependent on the quality of transaction design. Large poorly designed xacts will results in large no of locks required, times the no of updating connections. And that result sin the server being lock-bound (processing locks instead of data!) Small well-designed xacts require small no of locks. Some systems can support 2,000 users with 20,000 locks configured; others run out of 400,000 locks with only 200 users. The consequences of poor code affects every area (locks; tran log size; resource contention; replication).
    Last edited by Derek Asirvadem; 11-10-09 at 13:10.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •