我们的TFS_DefaultCollection数据库正在经历事务日志增长,已经查看了原因,并注意到该数据库正在发生删除,这似乎导致了增长。
使用的TSQL:
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))
这是删除数据的最有效方法吗?我们有定期的1小时交易日志备份和每日差异/每周FULL。但每小时备份后,看不到事务日志减少。
我真的不希望缩小日志文件以减小大小,该过程每天上午9点触发并运行几个小时。
非常感谢您的帮助。