6

We have a proc that runs Truncate Table inside an Snapshot transaction. This seems to be causing a LOCK_M_S lock that blocks the sys view sys.partitions.

Is there a convenient work around for this? I like the efficiency of not taking excess logs that happens with truncate, but don't want to lock up my sys.partitions.

I'm happy to post the code upon request, but I'm pretty sure this is some behavior of Truncate being inside a Snapshot transaction. that i'm just unaware of.

Improve this question
3
  • 2
    If the TRUNCATE blocks sys.partitions from being accessed, then I don't think that the problem is the truncate - a DELETE * FROM should have the same effect. The problem is that it is inside a transaction, and that the pending actions on the table are blocking the sys.partitions view... somehow. As a question, have you tried the statement SELECT * FROM sys.partitions WITH(NOLOCK)
    – Laughing Vergil
    Commented Aug 21, 2019 at 17:23
  • @LaughingVergil, WITH(NOLOCK) is actually still blocked... I'll post the blocking query
    – Jamie Marshall
    Commented Aug 21, 2019 at 18:51
  • As another thing to check - apparently, the sys.partitions view can lock/block, but it depends on what columns are selected. Try limiting the columns selected from the view, and see if that works.
    – Laughing Vergil
    Commented Aug 21, 2019 at 20:34

1 Answer 1

Reset to default
3

Locking for truncate and delete is different. As for locking TRUNCATE behave more like 'ALTER TABLE' than DELETE.

From Microsoft documentation:
TRUNCATE TABLE (Transact-SQL)
"When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row".

As you can see, there is a schema lock on the whole table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.