MSSQL清理垃圾日志：轻松实现日志清理

1. 引言

随着业务的不断增长，大多数企业都会使用Microsoft SQL Server数据库来存储数据，以保证数据的完整性和安全性。然而，随着时间推移，日志文件也会不断增长，这会给服务器带来压力，因此需要对日志进行清理以释放磁盘空间和提高数据库性能。

2. MSSQL日志文件

2.1 什么是MSSQL日志文件

在MSSQL中，日志文件是用于跟踪所有数据库事务所做更改的一种机制。每当事务进行更改时，都会在日志文件中记录这一更改。如果出现故障或错误，此日志文件可以用于还原数据库到指定的时间点。

然而，随着时间推移，日志文件可能会变得非常大，从而占用大量磁盘空间，进而影响系统性能。

2.2 日志清理的必要性及影响

当日志文件不断增长时，会占用大量磁盘空间，导致服务器磁盘不足，从而影响系统性能。此外，该文件也会影响数据备份，因此，定期清理日志是维护数据库的重要步骤。

然而，删除某些日志文件可能会影响还原程序，在删除日志文件时应该非常小心。在删除之前，一定要创建数据库备份以确保可以还原到所需时间点。

3. MSSQL日志清理

3.1 删减日志文件的方法

在MSSQL中，可以通过以下方式清理日志文件：

备份日志文件

通过Back Up Log语句简化日志文件

通过保存日志文件的最长时间来限制日志文件大小

手动删减日志文件

3.2 备份日志文件

备份数据库是减少磁盘占用和提高性能的一种方法。在此过程中，MSSQL将把日志文件中的所有内容都包括在内。因此，这是一个非常好的清理日志文件的方法。

备份日志文件的语句如下：

BACKUP DATABASE DatabaseName 
TO DISK = 'D:\Backup\DatabaseName.bak' 
GO

此语句将备份整个数据库，包括当前的日志文件。

3.3 通过Back Up Log语句简化日志文件

使用Back Up Log语句可以将某些日志文件简化并减少磁盘使用空间。

BACKUP LOG DatabaseName 
TO Disk = 'D:\Backup\DatabaseName.trn' 
GO

此语句将备份当前日志的活动部分。备份完日志文件之后，您可以将备份文件（.trn）删除。

3.4 通过日志文件的保留期限清理日志文件

通过设置日志文件的保留时间来限制文件的大小。

USE DatabaseName 
GO 
--设置日志的自动删除，删除10分钟前的日志 
ALTER DATABASE DatabaseName 
SET RECOVERY SIMPLE 
GO 
--保留保留日志更改2小时 
BACKUP LOG StackOverFlow 
TO DISK='D:\Database\StackOverFlow.trn' WITH INIT 
GO 
--设置数据库恢复模式 
ALTER DATABASE StackOverFlow SET recovery FULL 
GO 
--创建日志清理作业 
USE msdb 
GO 
EXECUTE sp_add_job 
@job_name = N'Clear Transaction Log Job' 
GO 
EXECUTE sp_add_jobstep 
@job_name = N'Clear Transaction Log Job', 
@step_name = N'Clear Transaction Log Step', 
@subsystem = N'TSQL', 
@command = N'BACKUP LOG StackOverFlow TO DISK=''D:\Database\StackOverFlow.trn'' WITH INIT; ', 
@on_success_action = 3, 
@on_fail_action = 2 
GO 
EXECUTE sp_add_schedule 
@schedule_name = N'Clear Transaction Log Schedule', 
@freq_type = 4, 
@freq_interval = 1, 
@active_start_time = 200000, 
@active_end_time = 235959 
GO 
EXECUTE sp_attach_schedule 
@job_name = N'Clear Transaction Log Job', 
@schedule_name = N'Clear Transaction Log Schedule' 
GO 
EXECUTE sp_add_jobserver 
@job_name = N'Clear Transaction Log Job' 
GO 

此脚本将数据库的恢复模式设置为FULL，设置数据库日志自动删除，备份日志文件，然后创建清理日志的作业。在这个例子中，日志文件超过2小时则会被清理。

3.5 手动删减日志文件

虽然不是最优的方法，但是您也可以手动删除日志文件。但是，要小心删除哪些文件，以及何时删除文件。

3.5.1 手动删除单个日志文件

步骤 1: 查看所有数据库的日志文件并选择要删除的日志文件

SELECT * 
FROM sys.database_files 
WHERE type_desc = 'LOG' 
GO

步骤 2: 将数据库从当前连接的会话中脱机

USE master; 
GO 
ALTER DATABASE DatabaseName 
SET OFFLINE 
WITH ROLLBACK IMMEDIATE; 
GO 

此脚本将数据库设置为离线状态。

步骤 3: 手动删除日志文件

使用以下命令手动删除日志文件：

--手动删除日志文件 
EXEC master.sys.sp_executesql N'sp_cycle_errorlog'; 

步骤 4: 将数据库连接回当前的会话

USE master; 
GO 
ALTER DATABASE DatabaseName 
SET ONLINE; 
GO 

步骤 5: 检查新的日志文件是否创建

SELECT * 
FROM sys.database_files 
WHERE type_desc = 'LOG' 
GO 

3.5.2 删除多个日志文件

步骤 1: 获取所有数据库的日志文件并选择要删除的文件

SELECT * FROM sys.database_files WHERE type_desc ='LOG';

步骤 2: 使用以下语句将日志文件置为单用户模式

USE master 
ALTER DATABASE DatabaseName 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE 
GO 

步骤 3: 根据选择的文件名删除日志文件

ALTER DATABASE DatabaseName 
REMOVE FILE database_Log; 
GO 

这将删除名为database_Log的日志文件。

步骤 4: 将数据库恢复为多用户模式

ALTER DATABASE DatabaseName 
SET MULTI_USER 
GO 

4.结论

通过备份日志文件、简化log文件、根据保存日志的时间限制日志大小，以及手动删减日志文件等方式，可以清理MSSQL中的日志文件。但是，在执行任何操作之前，一定要创建数据库备份以确保可以还原到所需时间点，并对删除的文件进行记录。建议定期清理日志文件，以确保服务器正常运行。