CODEWITHSUNDEEP
sql-serversql-server-2008
Bharath
Bharath

Reputation: 31

SQLSERVER - LOCK error

I'm getting an error while running a proc. through SSIS.. Error:

[Execute SQL Task] Error: Executing the query "Exec clk.id_Process ? ,? ,?" 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.

I tried to trace out the problem by joining 

"sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st"

and found its getting strucked while updating a table

UPDATE ta        
        SET ta.id =
        CASE 
            WHEN ta.id=tmp.MergeoldId THEN tmp.MergenewId
            ELSE ta.id
        END,
        ta.Id2=
        CASE
         WHEN ta.Id2=tmp.MergeoldId THEN tmp.MergenewId
         ELSE ta.Id2
        END,
        ta.Id3=
        CASE
            WHEN ta.Id3=tmp.MergeoldId THEN tmp.MergenewId
            ELSE ta.Id3
        END
        --SELECT ta.id ,ta.Id2,ta.Id3, tmp.MergeoldId,*
        FROM tel.TranAssemble ta WITH (ROWLOCK)
        INNER JOIN clk.id_Process tmp WITH (NOLOCK) on (ta.id = tmp.MergeoldId OR ta.Id2=tmp.MergeoldId OR ta.Id3=tmp.MergeoldId )
        WHERE tmp.Id >= @MinId AND tmp.Id < @MINID +  @BatchSize

Any solution?

Upvotes: 0

Views: 3970

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

These issues are due to Memory pressure ,since The lock manager will not use more than 60 percent of the memory available to SQL Server.

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.

Normally SQL server uses locks for all queries (even selects) and a small amount of memory is used for each lock it holds(96 bytes).

Based on the above knowledge,you can start troubleshooting your issue using below queries

--this gives you currently running queries holding more locks
select * from sys.dm_exec_requests ec
cross apply
(SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks trn
where ec.session_id=trn.request_session_id
GROUP BY request_session_id 
)b

--this gives you locks for allsessions
SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks trn
where ec.session_id=trn.request_session_id
GROUP BY request_session_id

Upvotes: 2

Related Questions