Drop or truncate table is hanging and timing out

Jay D 25 Reputation points
2023-09-26T03:17:01.9866667+00:00

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!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,379 questions
{count} vote

Accepted answer
  1. Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
    2023-09-26T22:15:35.8+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator
    2023-09-26T03:27:59.91+00:00

    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');
    
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.