T-SQL BEGIN TRANSACTION和COMMIT在WHERE循环中删除记录失败

Question

下面的代码用于按月循环遍历和删除事务。然而，它并没有按照我所期望的方式运行。我希望COMMIT发生在每次DELETE之后。

例程在日志空间耗尽并回滚ALL时失败被删除的事务。COMMIT没有按照我期望的方式运行。为了控制日志空间，我假设循环中的COMMIT将COMMIT每月删除一次，从而防止日志空间过度填充。

DECLARE @MaxPostDate AS DATE = '2021-01-31'
DECLARE @MinPostDate AS DATE = '2019-07-01'
DECLARE @NbrOfMonthsToMaintain As INT = 13 
WHILE @NbrOfMonthsToBeDeleted >= 0
BEGIN 
SELECT @NbrOfTransactions = COUNT(*) 
FROM [dbo].[Transaction] f
WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)
PRINT 'Delete ' + FORMAT(@NbrOfTransactions,'N0') + ' transactions between ' 
+ CAST(@StartingDeleteDate As VARCHAR(20)) + ' AND ' 
+ CAST(EOMONTH(@StartingDeleteDate) AS VARCHAR(20))

BEGIN TRANSACTION
DELETE f
FROM [dbo].[Transaction] f
WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)
AND f.[PostDate] <= @DeleteToDate
COMMIT TRANSACTION
INSERT INTO [app].[DataDeletionHistory](
[DataType] 
,[Description]
,[DateDeleted]
,[MonthDeleted]
,[RowsDeleted])
SELECT 'Transactions'    As DataType
,'Delete'             As [Description]
,GetDate()           AS DateDeleted
,@StartingDeleteDate AS MonthDeleted
,@NbrOfTransactions  AS RowsDeleted

SET @StartingDeleteDate = DATEADD(m,1,@StartingDeleteDate)
SET @NbrOfMonthsToBeDeleted = @NbrOfMonthsToBeDeleted - 1
END;

结果如下:

Delete 15,183,513 transactions between 2019-07-01 AND 2019-07-31
(15183513 rows affected)
(1 row affected)
Delete 14,999,308 transactions between 2019-08-01 AND 2019-08-31
(14999308 rows affected)
(1 row affected)
Delete 14,292,165 transactions between 2019-09-01 AND 2019-09-30
(14292165 rows affected)
(1 row affected)
Delete 15,365,025 transactions between 2019-10-01 AND 2019-10-31
(15365025 rows affected)
(1 row affected)
Delete 14,576,586 transactions between 2019-11-01 AND 2019-11-30
(14576586 rows affected)
(1 row affected)
Delete 15,316,013 transactions between 2019-12-01 AND 2019-12-31
(15316013 rows affected)
(1 row affected)
Delete 15,453,046 transactions between 2020-01-01 AND 2020-01-31
(15453046 rows affected)
(1 row affected)
Delete 14,408,066 transactions between 2020-02-01 AND 2020-02-29

Msg 9002, Level 17, State 4, Line 50
数据库'MyDatabase'的事务日志已满，原因是'ACTIVE_TRANSACTION'。

Msg 9002, Level 17, State 4, Line 1
数据库'MyDatabase'的事务日志由于'ACTIVE_TRANSACTION'已满。

留言3314,21层，3州，1线

Answer 1

检查一下，

DECLARE @MaxPostDate AS DATE = '2021-01-31'
DECLARE @MinPostDate AS DATE = '2019-07-01'
DECLARE @NbrOfMonthsToMaintain As INT = 13 
begin try
SET NOCount ON
SET XACT_ABORT ON
WHILE @NbrOfMonthsToBeDeleted >= 0
BEGIN 
--SELECT @NbrOfTransactions = COUNT(*) 
--FROM [dbo].[Transaction] f
--WHERE f.[PostDate] BETWEEN @StartingDeleteDate AND EOMONTH(@StartingDeleteDate)
set @StartingDeleteDate=EOMONTH(@StartingDeleteDate)


--BEGIN TRANSACTION
DELETE f
FROM [dbo].[Transaction] f
WHERE (f.[PostDate]>= @StartingDeleteDate AND f.[PostDate]<= @StartingDeleteDate)
--AND f.[PostDate] <= @DeleteToDate
--COMMIT TRANSACTION
@NbrOfTransactions =@@RowCount
PRINT 'Delete ' + FORMAT(@NbrOfTransactions,'N0') + ' transactions between ' 
+ CAST(@StartingDeleteDate As VARCHAR(20)) + ' AND ' 
+ CAST(@StartingDeleteDate AS VARCHAR(20))
INSERT INTO [app].[DataDeletionHistory](
[DataType] 
,[Description]
,[DateDeleted]
,[MonthDeleted]
,[RowsDeleted])
SELECT 'Transactions'    As DataType
,'Delete'             As [Description]
,GetDate()           AS DateDeleted
,@StartingDeleteDate AS MonthDeleted
,@NbrOfTransactions  AS RowsDeleted

SET @StartingDeleteDate = DATEADD(m,1,@StartingDeleteDate)
SET @NbrOfMonthsToBeDeleted = @NbrOfMonthsToBeDeleted - 1
END
end try
begin catch
if(@@ROWCOUNT>0)
Rollback;
Throw;
end catch

你也可以用Output Deleted来知道count(*)

你也可以使用tblTransaction的Primary Key进行批量删除或批量删除。

1的链接链接2链接3