Best Way to Determine if a Table has a Long Running Transaction

Question

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

Answer 1

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.

Answer 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