Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Question Unanswered: SQL Server has run out of LOCKS

    Hi,
    I got the exception "SQL Server has run out of LOCKS".
    After this exception occured, the records which were processed earlier are rolledback and the process went ahead inserting other records. So, now it contain only other records.

    Now
    1.how should we get back the records which were rolledback?
    2.What should to be followed to prevent this error.I am new to SQL Server,can any one please help me ,as it was very urgent.

    Cheers,
    Ravi

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    You probably can't recover the rolled back transaction, you must rerun a query to replicate them.

    You can configure the number of locks using sp_configure in advanced mode. See Books On Line. However your problem is more likely to be related to a lock escaltion problem, particularly if you're handling very large numbers of records through something like a cursor or a long winded stored procedure. Examine the SQL used and see if a TABLOCK or TABLOCKX hint is appropriate to prevent too many lower level (page or row) locks being raised during the excution of the query.

    Hope this helps.

Posting Permissions

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