Sql Server数据库alwayson集群收缩数据库日志

热衷于分享各种干货知识，大家有想看或者想学的可以评论区留言，秉承着“开源知识来源于互联网，回归于互联网”的理念，分享一些日常工作中能用到或者比较重要的内容，希望大家能够喜欢，不足之处请大家多提宝贵地意见，我们一起提升，守住自己的饭碗。

在 SQL Server 中，使用 Always On 可用性组时，无法像单机版本一样，把数据库的日志模式改为简单，只能是完整模式。为了缩减事务日志文件的大小，可以使用以下脚本。如要在生产环境中使用，请先在测试环境进行测试，谨慎使用。

1. 确保备份事务日志

首先，确保你已经备份了事务日志，因为在缩减日志之前，SQL Server 需要知道哪些日志可以安全地删除。以下是备份事务日志的示例脚本：

-- 备份事务日志
BACKUP LOG test TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\log_backup.trn' WITH NOFORMAT, NOINIT, 
NAME = 'test Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

说明：test 为你的实际数据库名称和备份路径。

2. 缩减事务日志文件

接下来，可以使用 DBCC SHRINKFILE
命令来缩减事务日志文件。

-- 找到日志文件的逻辑名称
USE test;
GO

-- 查询数据库文件信息
EXEC sp_helpfile;
GO

-- 请根据实际情况替换
DBCC SHRINKFILE ('test_log', 10);  #日志缩减至10M,大小大家可以自定义
GO

3. 监控和确认缩减

执行完缩减命令后，你可以再次使用 sp_helpfile
查看日志文件的大小，以确认是否成功缩减。

USE test;
GO
EXEC sp_helpfile;
GO

4.注意事项

• 定期备份：确保定期备份事务日志，以防止日志文件无限制增长。

• 缩减影响：频繁缩减日志文件可能会导致性能问题，因此应谨慎使用。

• 监控日志使用：定期监控日志文件的使用情况，确保其在合理范围内。

5.脚本内容

=============================================
USE [master]
GO
CREATE PROC [dbo].[ShrinkUserDatabasesLogFile]
AS
BEGIN
    DECLARE @DBNAME NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX);
    -- 临时表保存数据
    CREATE TABLE #DataBaseServerData
    (
        ID INT IDENTITY(1, 1),
        DBNAME NVARCHAR(MAX),
        Log_Total_MB DECIMAL(18, 1) NOT NULL,
        Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL
    );
    -- 游标
    DECLARE @itemCur CURSOR;
    SET @itemCur = CURSOR FOR 
    SELECT name 
    FROM SYS.[databases] 
    WHERE [name] NOT IN ('MASTER', 'MODEL', 'TEMPDB', 'MSDB', 'ReportServer', 'ReportServerTempDB', 'distribution')
      AND state_desc = 'ONLINE'  -- 确保数据库处于在线状态
    OPEN @itemCur;
    FETCH NEXT FROM @itemCur INTO @DBNAME;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'USE [' + @DBNAME + '];' + CHAR(10) +
        '
        DECLARE @TotalLogSpace DECIMAL(18, 1);
        DECLARE @FreeLogSpace DECIMAL(18, 1);
        DECLARE @filename NVARCHAR(MAX);
        DECLARE @CanshrinkSize BIGINT;
        DECLARE @SQL1 NVARCHAR(MAX);
        SELECT @TotalLogSpace = (SUM(CONVERT(DECIMAL(17, 2), size))  128) 
        FROM sys.database_files 
        WHERE type = 1;  -- 只考虑日志文件
        SELECT @FreeLogSpace = (SUM(size - FILEPROPERTY(name, ''SpaceUsed'')))  128.0
        FROM sys.database_files
        WHERE type = 1;
        SELECT @filename = name FROM sys.database_files WHERE type = 1;
        SET @CanshrinkSize = CAST((@TotalLogSpace - @FreeLogSpace) AS BIGINT);
        IF @CanshrinkSize > 0
        BEGIN
            PRINT ''Shrinking log file for database: ' + @DBNAME + ''';
            DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@CanshrinkSize + 1 AS NVARCHAR(MAX)) + ');
        END
        ELSE
        BEGIN
            PRINT ''No need to shrink log file for database: ' + @DBNAME + '';
        END';
        EXEC (@SQL);
        FETCH NEXT FROM @itemCur INTO @DBNAME;
    END 
    CLOSE @itemCur;
    DEALLOCATE @itemCur;
    DROP TABLE [#DataBaseServerData];
END;
GO
=============================================

文中的概念来源于互联网，如有侵权，请联系我删除。

欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。