1

I'm trying to run a stored procedure. In the SP I create two table variables (by using 3 CTE's). Then join the two tables and INSERT INTO an existing table. I keep getting the error CANNOT OBTAIN A LOCK RESOURCE. Any ideas how I can fix this? Is it the way I wrote my SP?

ALTER PROCEDURE [dbo].[CreateDailyAttribution]
-- Add the parameters for the stored procedure here
@day1 varchar(10)
,@day2 varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @tableUnrealized as table
(
    POFFIC varchar(3)
    ,PACCT varchar(5)
    --,PATYPE varchar(3)
    ,PSDSC1 varchar(100)
    ,ShortDesc varchar(100) 
    ,ChangeInOTE decimal(18,0)
    ,tMinus1_Qty integer
    ,tMinus2_Qty integer
    ,Sector varchar(50)
);

DECLARE @tableRealized as table
(
    POFFIC varchar(3)
    ,PACCT varchar(5)
    --,PATYPE varchar(3)
    ,PSDSC1 varchar(100)
    ,ShortDesc varchar(100) 
    ,Realized decimal(18,0)
    ,Sector varchar(50)
);
solarissf
  • 1,199
  • 2
  • 23
  • 58

1 Answers1

2

The error you're getting indicates that the server doesn't have enough memory, either because not enough physical memory is available, not enough memory is allowed for use by the server, or other processes are using too much memory and SQL doesn't have enough breathing room.

Your query looks like it could potentially use a lot of memory - it might be possible to tune it (one simple thing might be using #temp tables rather than @table variables and see if that relieves some memory pressure (#temp tables will go into the tempdb and hit the disk, whereas table variables may be stored in memory). It's really hard to say though without knowing more about your data and deeper analysis of your queries.

MSDN has some additional advice for this error:

Explanation

SQL Server cannot obtain a lock resource. This can be caused by either of the following reasons:

  • SQL Server cannot allocate more memory from the operating system, either because other processes are using it, or because the server is operating with the max server memory option configured.
  • The lock manager will not use more than 60 percent of the memory available to SQL Server.

User Action

If you suspect that SQL Server cannot allocate sufficient memory, try the following:

  • If applications besides SQL Server are consuming resources, try stopping these applications or consider running them on a separate server. This will remove release memory from other processes for SQL Server.
  • If you have configured max server memory, increase max server memory setting.

If you suspect that the lock manager has used the maximum amount of available memory identify the transaction that is holding the most locks and terminate it.

The following script will identify the transaction with the most locks:

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

Take the highest session id, and terminate it using the KILL command.

Dan Field
  • 20,885
  • 5
  • 55
  • 71