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).


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,
    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.


Also confirmed via sp_whoisactive.


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



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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s