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

  • 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.



    Wednesday, November 13, 2019 12:12 PM