Database Engine cannot obtain a LOCK resource at this time Error

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  June 27, 2012 at 3:55 pm

  #262904

  When I execute a Stored Procedure I get the following error and it fails every time that I run it.

  [Execute SQL Task] Error: Executing the query "EXEC dw.usp_merge_into_dw_Receivable_Items" failed with the following error: "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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  THis just started happening.

  If I run it on another server it does not fail.

  I bumped up the memory to 16GB. No one else is on the Server.

  When I execute sp_who_2, I noticed that the process that is executing the SP return 18 records for the SPID that is executing the SP.

  This is a relatively new server.

  Any thoughts?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  June 27, 2012 at 11:59 pm

  #1506460

  easy ones first:

  non-default configuration for "locks"?

  SELECT * FROM sys.configurations AS c WHERE name = 'locks'

  another spid using tons of locks?

  SELECT request_session_id, COUNT(*) FROM sys.dm_tran_locks AS dtl GROUP BY request_session_id