SQL Server – Blocking a Truncate

The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?

What am I talking about? When you issue a truncate it takes a Sch-M lock and it uses this when it is moving the allocation units to the deferred drop queue. So if it takes this lock and you look at the locking compatibility matrix below you will see what can cause a conflict (C).

matrixLock

So another workload that takes either a Sch-M or Sch-S lock means a conflict. So I will show you how your truncate is blocked by a select statement with a nolock hint which essentially takes a Sch-S lock.

The proof lies within sys.dm_tran_locks.


SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = 8

 

Here you see the SPID 57 being the truncate command in a WAIT request status.

schm_s

Also confirmed via sp_whoisactive.

Theblocks

You probably knew this already but it is nice seeing it with examples.

 

Advertisements

2 thoughts on “SQL Server – Blocking a Truncate

  1. Pingback: Dew Drop - August 1, 2018 (#2778) - Morning Dew

  2. Pingback: Blocking A Truncate Statement – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.