none
Error Msg 1204 Database Engine cannot obtain a LOCK resource at this time SQL 2005

    Question

  • Hi,

    I'm running SQL 2005 ENT x64 on a Windows 2003 x64 SP2 16 GB RAM machine and is encounting the below error message while attempting to copy data. The data copy is the only process running on the server as the environment is currently off-line for maintenance. I've research the web and can't find any suggestions to point me in the right direction. Any help is appreciated - thanks.

    Msg 1204, Level 19, State 4, Line 1

    The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.


    Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends

    Monday, April 9, 2012 9:30 PM

Answers

  • Lock structure uses 128 bytes on 64 bit system and 64 bytes on 32 bit systems. So for 500M lock structures on 64bit system you need to have ~61Gb of RAM not even counting all overhead. By default SQL Server tries to escalate locks to the higher level (table or partition when enabled) automatically after ~5,000 locks acquired on the object. Although escalation would fail in case if there are any other locks on the table from the different session (SQL Server would try escalate locks again every ~1,250 locks but again, that  requires exclusive table access).

    So you have 2 options. First is obtaining exclusive table lock for insert. you can do it with TABLOCKX hint. Second option (which I would recommend) is to split 500M inserts into smaller batches and run them in separate transactions. 


    Thank you!

    My blog: http://aboutsqlserver.com



    Tuesday, April 10, 2012 7:48 PM

All replies

  • how did you copy the data?

    did you run the SQL Server in single user instance and you tried to connect from another process?

    Tuesday, April 10, 2012 6:22 AM
  • @MostInterestingMan

    Please check  MAX server memory option and apply Lock Pages in Memory policy to SQL User.

    And the following script will identify the transaction with the most locks, you could take the highest session id, and terminate it using the KILL command.

    SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC

     

    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, April 10, 2012 7:10 AM
  • Could you post your data copying query and the structure of the table that you are copying from / to and the associated indexes?
    Tuesday, April 10, 2012 3:32 PM
  • I was about to assolate the INSERT statement in my STORED PROCEDURE that was causing the error and ran it seperately but got the following error message below. The statement is inserting over 500 million rows - thanks.

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.


    Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends


    Tuesday, April 10, 2012 7:16 PM
  • Lock structure uses 128 bytes on 64 bit system and 64 bytes on 32 bit systems. So for 500M lock structures on 64bit system you need to have ~61Gb of RAM not even counting all overhead. By default SQL Server tries to escalate locks to the higher level (table or partition when enabled) automatically after ~5,000 locks acquired on the object. Although escalation would fail in case if there are any other locks on the table from the different session (SQL Server would try escalate locks again every ~1,250 locks but again, that  requires exclusive table access).

    So you have 2 options. First is obtaining exclusive table lock for insert. you can do it with TABLOCKX hint. Second option (which I would recommend) is to split 500M inserts into smaller batches and run them in separate transactions. 


    Thank you!

    My blog: http://aboutsqlserver.com



    Tuesday, April 10, 2012 7:48 PM