Team Foundation Server (TFS) Version 16.131.28226.3 Transaction Log Growth due to TBL_Content Delete

Question

Hi,

We are experiencing transaction log growth against our TFS_DefaultCollection database, have looked to see the cause and notice there a delete happening against the database which looks to cause the growth.

TSQL used:

DELETE  tbl_Content
                    FROM    #filesToDelete d
                    INNER LOOP JOIN tbl_FileMetadata fm WITH (FORCESEEK(IX_tbl_FileMetadata_ResourceId(PartitionId, ResourceId)))
                    ON      d.ResourceId = fm.ResourceId
                    INNER LOOP JOIN tbl_Content c WITH (FORCESEEK(PK_tbl_Content(PartitionId, ResourceId)))
                    ON      c.PartitionId = fm.PartitionId
                            AND c.ResourceId = fm.ResourceId
                    WHERE   d.Id BETWEEN @counter AND @counterEnd
                            AND fm.PartitionId = @partitionId
                            AND fm.DeletedOn IS NOT NULL
                    OPTION (OPTIMIZE FOR (@partitionId UNKNOWN, @counter = 1, @counterEnd = 100))

Is this the most efficient way to remove data? We have regular 1hr transaction log backups and daily diffs/weekly FULLs.

Any help is much appreciated.

Regards

Ian