4

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.

  • 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 Aug 21 at 17:23
  • @LaughingVergil, WITH(NOLOCK) is actually still blocked... I'll post the blocking query – Jamie Marshall Aug 21 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 Aug 21 at 20:34
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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