Blocking A Truncate Statement

Arun Sirpal shows that the TRUNCATE command needs to take locks like any other data modification command:

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

Arun includes an image which shows what can block what, and also shows us an example.

Related Posts

Tempdb Blocking With Non-Clustered Columnstore Indexes

Ned Otter runs into a tricky issue: I have a client that used Itzik Ben-Gan’s solution of creating a filtered nonclustered columnstore index to achieve batch mode on a rowstore (in fact I proposed that the client consider it). They have an OLTP system, and often perform YTD calculations. When they tested, processing time was reduced by […]

Read More

Beautiful Deadlock Graphs And Tying RIDs Back To Object Names

Josh Simar shares a deadlock graph which I have entitled The Pit Of Despair: I can’t make heads or tails of that but I can tell you that seems like a really bad brawl for resources. It’s like a Jerry Springer show with a few extras thrown in. Since I knew that my graph wasn’t […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031