4

I need to be able to iterate all tables and issue a TRUNCATE command, however, I don't want the loop to be held up by blocking. Essentially, I am looking for the best way to determine if a SCH-M lock can be obtained prior to issuing a truncate command.

Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0

Improve this question
1

2 Answers 2

Reset to default
8

The best way to test if a lock can be acquired is to try to acquire it.

Use LOCK_TIMEOUT to give up after waiting a specified time, or immediately if a conflicting lock is encountered, as you prefer. Use TRY...CATCH to handle any error and retry later.

Using the locks DMV is unreliable since it is a non-transactional view of internal lock structures. A conflicting lock can easily appear behind your scan. In any case, there's a delay between completing the test and trying to acquire Sch-M.

It's best to avoiding scanning the locks DMV as much as possible to avoid interfering with what is often a highly contended internal facility already.

Improve this answer
0
2

You could in theory check sys.dm_tran_locks to see if there is an existing lock, and otherwise wait and loop again.

DECLARE @startLoop datetime2 = SYSUTCDATETIME();
WHILE 1=1
BEGIN
    IF NOT EXISTS (SELECT 1
        FROM sys.dm_tran_locks l
        JOIN sys.tables t ON t.object_id = l.resource_associated_entity_id
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE l.resource_database_id = DB_ID()
          AND t.name = @tableName
          AND s.name = @schemaName
    )
    BEGIN
        EXEC sp_executesql @truncateCommand;
        BREAK;
    END;

    IF SYSUTCDATETIME() > DATEADD(second, 10, @startLoop)
    BEGIN
        PRINT 'Could not acquire lock for table ' + @tableName;
        BREAK;
    END;

    WAITFOR DELAY '00:00:02';
END;

But as noted by others, this can be unreliable as it's not guaranteed that someone won't pre-empt you in between checking and actually truncating. It also puts extra pressure on the lock manager.

If you have an identical table to this then you can use ALTER TABLE SWITCH and the WAIT_AT_LOW_PRIORITY option to fast-switch the table into another, then truncate that table. Note that this option does not require the above loop.

ALTER TABLE someTable SWITCH TO someTableStaging
    WITH (WAIT_AT_LOW_PRIORITY (
      MAX_DURATION = 1 MINUTES,
        ABORT_AFTER_WAIT = SELF        -- BLOCKERS is another option
    ) );

TRUNCATE TABLE someTableStaging;

Assuming this is related to your previous question on a partitioned table, you can do

ALTER TABLE someTable SWITCH PARTITION 10 TO someTableStaging
Improve this answer
2
  • 1
    Thanks for the comments and help. Even though this answered the question exactly as I imaged, I chose another route that did not require the query and essentially does the same thing, for what I am needing it for.
    – Ross Bush
    Commented Jul 11 at 12:32
  • 2
    Another good thing about WAIT_AT_LOW_PRIORITY is that it allows you to try to acquire the lock for a period of time without your pending Sch-M lock blocking other sessions. So you can WAIT_AT_LOW_PRIORITY for as long as you want, waiting for an idle instant to acquire the lock and switch out the table or partition.
    – David Browne - Microsoft
    Commented Jul 11 at 21:54

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