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.

 

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 )

Google+ photo

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

Twitter picture

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

Facebook photo

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

w

Connecting to %s