HOW TO RELEASE OR REMOVE LOCK ON A TABLE SQL SERVER

Source: www.exacthelp.com HOW TO RELEASE OR REMOVE LOCK ON A TABLE SQL SERVER

We can free or release or remove or delete or check all locks on the sql server 2008,2012 and 2014 objects like table etc by killing the process id which is keeping the locks on the objects like tables and views. 

For example, Let us assume there is table named tblStudent.  Any user or application has executed following sql statements:

BEGIN TRANSACTION

DELETE TOP(1) FROM tblStudent

While he missed to commit or to rollback the above transaction.

If we will try select the recodes without NOLOCK,

SELECT * FROM tblStudent

It will not able to get the object tblStudent since it has locked.

Solution:

Step 1: Get the request session id by executing following sql statement:

SELECT

OBJECT_NAME(P.object_id) AS TableName,

Resource_type,

request_session_id

FROM

sys.dm_tran_locks L

join sys.partitions P

ON L.resource_associated_entity_id = p.hobt_id

WHERE   OBJECT_NAME(P.object_id) = ‘tblStudent’

We will get the output something like this:

TableName Resource_type request_session_id
tblStudent RID 54
tblStudent RID 54
tblStudent RID 55
tblStudent PAGE 55
tblStudent PAGE 54
tblStudent RID 54
tblStudent RID 54
tblStudent RID 54
tblStudent RID 54
tblStudent RID 54
tblStudent RID 54

Step 2: Kill the request session id which has kept lock on the table tblStudent. Assume its request_session_id is 54. Execute following query:

KILL 54

We can check or get closer look or see if or find all blocking or locking on databse tables by following script:

SELECT

blocking_session_id AS BlockingSessionID,

session_id AS VictimSessionID,

(SELECT [text] FROM sys.sysprocesses

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE spid = blocking_session_id) AS BlockingQuery,

[text] AS VictimQuery,

wait_time/1000 AS WaitDurationSecond,

wait_type AS WaitType,

percent_complete AS BlockingQueryCompletePercent

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE blocking_session_id > 0

Sample output:

BlockingSessionID VictimSessionID BlockingQuery VictimQuery WaitSecond WaitType CompletePercent
54 60 BEGIN TRANSACTION  DELETE TOP(1) FROM tblStudent SELECT * FROM tblStudent 655 LCK_M_S 0
Advertisements