MSSQL清除日志文件：实现一键操作

介绍

在使用Microsoft SQL Server (MSSQL)时，它会记录事务的日志，这些日志文件可能会不断增长，占据大量磁盘空间。清理这些日志文件是很重要的，否则可能出现磁盘空间不足的情况。本文将介绍如何通过一键操作清除MSSQL的日志文件。

一键清除日志文件

使用以下存储过程可以清除所有MSSQL数据库的日志文件：

USE master;
DECLARE @DatabaseName nvarchar(50)
DECLARE @LogPath nvarchar(500)
--设置日志路径，例如：D:\SQLServerLog
SET @LogPath = '  D:\SQLServerLog  '
DECLARE DatabaseCursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;')
    EXEC ('USE [' + @DatabaseName + '];')
    EXEC ('DBCC SHRINKFILE (N''' + @DatabaseName + '_log'' , 0, TRUNCATEONLY);')
    
    --将日志文件移动到备份文件夹
    EXEC ('DECLARE @LogName nvarchar(200); 
        SET @LogName = '''+ @DatabaseName + '_log.ldf''; 
        DECLARE @MoveCmd nvarchar(500); 
        SET @MoveCmd = ''MOVE N''''''''' + @LogPath + '''''''''' + @LogName + '''''''''' + '' TO N''''''''' + @LogPath + '\\Backup\\'''''''' + @LogName + '''''''''''; 
        EXEC sp_executesql @MoveCmd;');
    
    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor;

如何使用上述存储过程

将以下SQL脚本保存为.sql文件：

USE master;
DECLARE @DatabaseName nvarchar(50)
DECLARE @LogPath nvarchar(500)
--设置日志路径，例如：D:\SQLServerLog
SET @LogPath = '  D:\SQLServerLog  '
DECLARE DatabaseCursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;')
    EXEC ('USE [' + @DatabaseName + '];')
    EXEC ('DBCC SHRINKFILE (N''' + @DatabaseName + '_log'' , 0, TRUNCATEONLY);')
    
    --将日志文件移动到备份文件夹
    EXEC ('DECLARE @LogName nvarchar(200); 
        SET @LogName = '''+ @DatabaseName + '_log.ldf''; 
        DECLARE @MoveCmd nvarchar(500); 
        SET @MoveCmd = ''MOVE N''''''''' + @LogPath + '''''''''' + @LogName + '''''''''' + '' TO N''''''''' + @LogPath + '\\Backup\\'''''''' + @LogName + '''''''''''; 
        EXEC sp_executesql @MoveCmd;');
    
    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor;

在MSSQL Server Management Studio (SSMS)中，打开一个查询窗口，加载上述脚本文件并运行。此存储过程将清除所有数据库的日志文件并将其移动到备份文件夹（D:\SQLServerLog\Backup）。注意：在运行此脚本之前，请确保备份已完成并且所有事务已提交。

如何定期清除日志文件

为了自动清除日志文件并定期运行上述存储过程，可以在MSSQL服务器上设置一个自动任务。以下示例是在Windows Server 2012上使用SQL Server 2016设置自动任务：

打开SQL Server Management Studio并连接到MSSQL服务器。

在“对象资源管理器”中，右键单击“SQL Server 代理”并选择“新建维护计划...”。

在“新建维护计划”对话框中，输入计划名称并选择执行频率。

选择要包含在计划中的各种任务和步骤，例如备份和清理任务。

在“新建维护计划”对话框的“进程”选项卡中，添加一个新步骤并将其类型设置为“T-SQL” 组件。

在“T-SQL”选项卡的“命令”文本框中，输入存储过程的代码。确保以下内容包含在存储过程中：

USE master;
DECLARE @DatabaseName nvarchar(50)
DECLARE @LogPath nvarchar(500)
--设置日志路径，例如：D:\SQLServerLog
SET @LogPath = '  D:\SQLServerLog  '
DECLARE DatabaseCursor CURSOR FOR
    SELECT name FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;')
    EXEC ('USE [' + @DatabaseName + '];')
    EXEC ('DBCC SHRINKFILE (N''' + @DatabaseName + '_log'' , 0, TRUNCATEONLY);')
    
    --将日志文件移动到备份文件夹
    EXEC ('DECLARE @LogName nvarchar(200); 
        SET @LogName = '''+ @DatabaseName + '_log.ldf''; 
        DECLARE @MoveCmd nvarchar(500); 
        SET @MoveCmd = ''MOVE N''''''''' + @LogPath + '''''''''' + @LogName + '''''''''' + '' TO N''''''''' + @LogPath + '\\Backup\\'''''''' + @LogName + '''''''''''; 
        EXEC sp_executesql @MoveCmd;');
    
    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor;
        

保存维护计划并启用任务运行调度。

当任务运行时，它将执行上述存储过程并清除所有MSSQL数据库的日志文件。

结论

清理MSSQL的日志文件是很重要的，否则可能导致磁盘空间不足的问题。使用以上的存储过程可以清除所有数据库的日志文件，并将其移动到备份文件夹（D:\SQLServerLog\Backup）。可以在MSSQL服务器上设置一个自动任务来定期清除日志文件。