TRUNCATE WITH PARTITION Constantly Fails on 1 Out of 620 Tables

Question

There is a table that seems to have constant updates. There is also a stored procedure that runs periodically and iterates through tables and attempts to truncate a partition of data using the command below:

TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10))

The looping procedure always gets locked up on the same table, the table with constant updates fails even when data is not being updated in the partition targeted for deletion.

The calling process can't be updated. Is there a trick to bind the updates to a partition outside of modifying the query or some other way to force non changing partitions to not be blocked/locked?

Will a page lock cause truncate with partition to fail? Like could two partitions share a single page and attempting to delete with partition(2) fails because some process is updating data in partition 1 that 2 happens to be sharing a page with?

CREATE TABLE [dbo].[Table]
(
      [TableID] INT NOT NULL
      ...
    , [SourceID] NVARCHAR(4000) NOT NULL
    , CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
    , CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID)
)
ON psSourceID(SourceID)
GO

ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO)
GO

The TRUNCATE command is being blocked by a session running updates against the table. In lower environments a blocking process was never hit after many trials so the assumption was that WITH PARTITION would only place meta data locks by partition, not the entire table.

Answer 1

As Dan Guzman mentioned, a truncate requires exclusive access to the entire table. It appears this is the case even when truncating a specific partition, as opposed to just requiring exclusive access per partition. The hope was that updates and inserts could continue to run concurrently against a table in certain partitions while a quick TRUNCATE TABLE WITH PATITION could remove large swaths of data from the same table in a "non-active" partition.

The only work around is to loop all tables and attempt to determine if there is a long running transaction for each one. Those tables where exclusive access can be obtained, the truncate with partition method can be utilized, while those with exclusive locks either need to be reported so a retry can be performed, or as last resort issue a delete statement.

The truncate with partition removes over 400,000,000 records in under a second, that is why it is important to attempt to utilize that method over the delete.