时间戳

首页 > 解决方案 > 限制 SQL Server 事务日志

sql-server - 限制 SQL Server 事务日志

问题描述

我目前有一个 SQL 脚本，它获取具有特定名称的所有表并从中删除记录。

这些表中有数百万条记录，因此即使恢复设置为 SIMPLE，事务日志也会不断增长。我将删除放在一个事务中，它一次只删除大约 50000 条记录。任何人都可以提出一些不让事务日志不受控制地增长的方法。下面是我正在运行的 SQL 的粗略示例。

DECLARE delete_cur CURSOR FOR   
SELECT DISTINCT Name FROM Sys.tables WHERE name like 'ToDelete_%'

OPEN delete_cur
FETCH NEXT FROM delete_cur
INTO @tableName  
WHILE @@FETCH_STATUS = 0 AND @timeToStop = 0
BEGIN
    SELECT @RowCount = NULL, @MaxID = NULL, @MinID = NULL, @ID = NULL, @TableRowCount = 0               

    IF NOT Exists (Select 1 from ArchiveInfoAuditTables WHERE Name = @tableName)
    BEGIN
        SET @params = N'@MaxID_Out DECIMAL(18,0) OUT, @MinID_Out DECIMAL(18,0) OUT, @RowCount_Out DECIMAL(18,0) OUT';

        SET @SQL = 'SELECT @RowCount_Out = COUNT(ID), @MaxID_Out = MAX(ID), @MinID_Out = MIN(ID) FROM ' + @tableName
        print @SQL
        EXEC sp_executesql @SQL, @params, @RowCount_Out = @RowCount OUT, @MaxID_Out = @MaxID OUT, @MinID_Out = @MinID OUT;          
        PRINT @tableName + ' Row Count: ' + CONVERT(VARCHAR, @RowCount) + ' Max ID: ' + CONVERT(VARCHAR, @MaxID)  + ' Min ID: ' + CONVERT(VARCHAR, @MinID)      

        SET @params = N'@ID_Out DECIMAL(18,0) OUT, @Date DATETIME';

        SET @SQL = 'SELECT TOP 1 @ID_Out = ID FROM ' + @tableName + ' WHERE AuditTimeStamp < @Date ORDER BY ID DESC'
        print @SQL
        EXEC sp_executesql @SQL, @params, @ID_Out = @ID OUT, @Date = @JanOfCurrentYear          

        INSERT INTO DeleteInfo (Name, StartDeletingFromID, MaxID, MinID, NumberOfRows)
            VALUES (@tableName, @ID, @MaxID, @MinID, @RowCount)
    END
    ELSE 
    BEGIN
        SELECT TOP 1 @ID = StartDeletingFromID FROM DeleteInfo WHERE Name = @tableName          
    END

    IF (@ID IS NULL)
    BEGIN 
        PRINT 'No Record needs to be deleted for Table: ' + @tableName
        GOTO Fetch_Next
    END 

    WHILE 1 = 1
        BEGIN
            BEGIN TRANSACTION
                SET @params = N'@RowCount_Out DECIMAL(18,0) OUT, @NumOfRowsToDelete_Out BIGINT, @ID_Out DECIMAL(18,0)'
                SET @SQL = 'DELETE TOP (@NumOfRowsToDelete_Out) FROM ' + @tableName + ' WHERE ID <= @ID_Out ;' 
                + 'SELECT @RowCount_Out = @@RowCount'

                PRINT @SQL 
                EXEC sp_executesql @SQL, @params, @RowCount_Out = @TempRowCount OUT, @NumOfRowsToDelete_Out = @NumOfRowsToDelete, @ID_Out = @ID

                SET @TableRowCount += @TempRowCount
                SET @TotalRowCount += @TableRowCount
            COMMIT TRANSACTION
            CHECKPOINT;

            SET @MSG = 'Deleted ' + CAST(@TableRowCount AS VARCHAR) + '. ' + CONVERT(varchar, @TimeElapsed) + ' elapsed.'
            RAISERROR (@MSG, 0, 1) WITH NOWAIT  
            IF @TempRowCount < @NumOfRowsToDelete BREAK;
        END

    Fetch_Next:
        PRINT '/******************************************************************/'
        FETCH NEXT FROM delete_cur   
        INTO @tableName         

END

END_HERE:
    CLOSE delete_cur;  
    DEALLOCATE delete_cur;

标签： sql-servertsqlsql-deletearchive

解决方案

您可以限制日志的大小（这里我们将大小限制为 512 MB）：

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)

为备份数据库创建维护作业并缩小日志

您可以使用这个简单的命令来缩小日志文件

DBCC SHRINKFILE (DataFile1, 512)

推荐阅读