17

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pushkar Jaju
  • 569
  • 1
  • 4
  • 11
  • 3
    What process is locking it? Run your select *, then as it's waiting run 'exec sp_who2'. The BlkBy column will show your select statement being blocked by another process number. To clear that other process you want to run 'KILL 32' (for process 32 as an example) – Rich Benner May 11 '16 at 08:25
  • Hi Rich Benner, I have killed session using below approach and now when i fire select * query it giving me result.Is it the right approach ? Won't it cause any issue in future ? – Pushkar Jaju May 11 '16 at 08:30
  • Depends entirely on the session that you killed. You could take a look at either editing the code that was causing locking so that it doesn't take a lock or you could look at isolation levels within SQL which would allow you to get around this. – Rich Benner May 11 '16 at 08:34
  • Ok Thank you Rich Benner. – Pushkar Jaju May 11 '16 at 08:36
  • Hello Rich, Today I again same problem with another table.This tables has been created 6 back , never face any issue in past.Could you please suggest me the temporary solution ? – Pushkar Jaju May 13 '16 at 07:24
  • see my first answer, run sp_who2 and see what process is causing your problem. – Rich Benner May 13 '16 at 08:12
  • @RichBenner I executed 'exec sp_who2' and got few rows which contains my dbName.Please see below example. 580 - SPID 0 - EDID sleeping - status UserName - LoginName FRCVD2161 - hostname IOSMOC_DB - dbName AWAITING COMMAND -cmd 0 - request_id Please suggest – Pushkar Jaju May 13 '16 at 08:34
  • if you've got blocking then check the BlkBy column. It will show the process that is being blocked and the SPID of the process that is blocking it. You need to see what that SPID is doing and kill it if that's what you want to do. – Rich Benner May 13 '16 at 08:38
  • @RichBenner Sorry but how can i get to know what that SPID is doing ? because that particular row shows me db name and status or may be currently none of the table is locked so i am not able to see ? – Pushkar Jaju May 13 '16 at 08:45
  • try running 'DBCC INPUTBUFFER(61)' with 61 replaced with your SPID – Rich Benner May 13 '16 at 09:35

2 Answers2

37

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pushkar Jaju
  • 569
  • 1
  • 4
  • 11
  • 1
    Just a note to everyone finding this now: while you CAN just kill a lock like this, you really need to find out what's locking it and WHY. The point of locks is to prevent nasty bugs (look up "race conditions") so simply killing the locks with no idea what they are for is a bad idea. – MGOwen Jun 24 '20 at 01:21
5

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
dfortun
  • 704
  • 6
  • 7