Sql-server – How to check if an SQL table is locked for querying

sql serversql-server-2008tsql

For whatever reason one specific table in the database started to not to complete any query results on a specific table. It says 'executing query…' and not completes.

Like:

select * from foo

Other tables are returning rows.

How/where I can check if a table is locked?

Best Answer

Check this sample

    select  
    object_name(P.object_id) as TableName
    , resource_type
    , resource_description
    , request_mode
    , CASE REQUEST_MODE
                    WHEN 'S'        THEN 'Shared'
                    WHEN 'U'        THEN 'Update'
                    WHEN 'X'        THEN 'Exclusive'
                    WHEN 'IS'       THEN 'Intent Shared'
                    WHEN 'IU'       THEN 'Intent Update'
                    WHEN 'IX'       THEN 'Intent Exclusive'
                    WHEN 'SIU'      THEN 'Shared Intent Update'
                    WHEN 'SIX'      THEN 'Shared Intent Exclusive'
                    WHEN 'UIX'      THEN 'Update Intent Exclusive'
                    WHEN 'BU'       THEN 'Bulk Update'
                    WHEN 'RangeS_S' THEN 'Shared Range S'
                    WHEN 'RangeS_U' THEN 'Shared Range U'
                    WHEN 'RangeI_N' THEN 'Insert Range'
                    WHEN 'RangeI_S' THEN 'Insert Range S'
                    WHEN 'RangeI_U' THEN 'Insert Range U'
                    WHEN 'RangeI_X' THEN 'Insert Range X'
                    WHEN 'RangeX_S' THEN 'Exclusive range S'
                    WHEN 'RangeX_U' THEN 'Exclusive range U'
                    WHEN 'RangeX_X' THEN 'Exclusive range X'
                    WHEN 'SCH-M'    THEN 'Schema-Modification'
                    WHEN 'SCH-S'    THEN 'Schema-Stability'

        ELSE NULL
        END AS REQUEST_LOCK_MODE

FROM   sys.dm_tran_locks   AS L
       join sys.partitions AS P 
        on L.resource_associated_entity_id = p.hobt_id

REF: http://sqlblog.foxraven.com/2012/01/check-to-see-if-table-is-locked.html

Related Topic