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