Drop or truncate table is hanging and timing out

Question

I am trying to drop and create a table and it is timing out. The table has about 500k entries and I am able to do this on other tables without any issues. How can I view if there are any deadlocks on the table? Is there any way I can delete any locks? Thanks!

Answer 1

Hello Jay D,

Welcome to the Microsoft Q&A forum.

You can run the below query to find the session_id of any locks on your table

SELECT * FROM sys.dm_pdw_lock_waits
WHERE object_name in ('your_table')

then you can Kill the session using KILL 'Session_id'

I hope this helps.

Answer 2

This query can be used to check for locks on a table:

SELECT
    request_session_id AS SessionID,
    resource_type AS ResourceType,
    resource_database_id AS DatabaseID,
    DB_NAME(resource_database_id) AS DatabaseName,
    resource_associated_entity_id AS ObjectID,
    OBJECT_NAME(resource_associated_entity_id) AS ObjectName,
    request_mode AS LockType,
    request_status AS LockStatus
FROM
    sys.dm_tran_locks
WHERE
    resource_database_id = DB_ID('YourDatabaseName')
    AND resource_associated_entity_id = OBJECT_ID('YourTableName');