CODEWITHSUNDEEP
sqldatabasesql-server-2012
Pushkar Jaju
Pushkar Jaju

Reputation: 639

How to unlock table in SQL Server 2012?

I created table in SQL Server 2012 and when I execute 

select * from tableName

it is taking a long time and some time returns no result.

Currently it has only 1 row. After searching I know it is being locked so please help how to unlock it or drop it?

Upvotes: 18

Views: 95223

Answers (2)

dfortun
dfortun

Reputation: 764

Get the SPID of what is locking the table and kill it, see below

    SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL], 
            Status,command,wait_type,wait_time,wait_resource, 
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time


DBCC opentran()

exec sp_who2 68
exec sp_lock 68
kill 68

Upvotes: 6

Pushkar Jaju
Pushkar Jaju

Reputation: 639

Thank you Guys.. It is resolved.

I fired below query

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) = 'P1Chronolog_IncidentActivityUpdates'

and killed that respective session by

Kill session_ID

Upvotes: 43

Related Questions