1. 简介
随着业务的扩大和数据量的增加,MSSQL数据库日志会逐渐增大,如果不定期清理就会占用过多的磁盘空间,导致出现磁盘溢出的情况。本文将介绍定时清理MSSQL数据库日志文件的小技巧。
2. 查看当前数据库日志占用情况
在进行数据库日志的清理之前,需要先了解一下当前数据库日志的使用情况。
2.1 查询日志占用大小
在SSMS(SQL Server Management Studio)中右键该数据库,选择“属性”,在弹出的对话框中选择“文件”选项卡,可以看到目前的日志文件信息,如下图所示:
注意:其中,Log used space列即为当前日志文件已占用的空间,单位是MB。
![image.png](https://cdn.nlark.com/yuque/0/2022/png/413941/1644203763627-23dd77c0-9d8c-4def-831a-df9fc78463e4.png#clientId=u5ff66e45-5712-4&from=paste&height=208&id=u05eb088e&margin=%5Bobject%20Object%5D&name=image.png&originHeight=416&originWidth=527&originalType=binary&ratio=1&size=27305&status=done&style=none&taskId=u7d68609a-5c29-4c7d-9d95-60b3ff156c0&width=263.5)
也可以通过以下脚本查询当前数据库日志已占用的空间:
use [database_name]
select name, size/128.0 [Size in MB],
cast(FILEPROPERTY(name, 'SpaceUsed')/128.0 as decimal(10,2)) [UsedSpace in MB],
(size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 [FreeSpace in MB]
from sys.database_files
where type_desc = 'LOG'
2.2 查询日志最近一次备份时间
在进行日志清理前,最好检查一下日志文件的最近备份时间。
可以通过以下脚本查询日志最近一次备份时间:
select b.name as BackupName, b.backup_start_date as BackupDate,
b.type as BackupType, b.compressed_backup_size as Size,
s.database_name, s.recovery_model_desc, s.log_reuse_wait_desc, s.log_reuse_wait,
(DATEDIFF(MINUTE, b.backup_start_date, GETDATE())) as ElapsedTimeInMinutes
from msdb..backupset b
inner join sys.databases s on b.database_name = s.name
where b.database_name = 'database_name' and b.type = 'L'
order by b.backup_start_date desc
如果最近一次备份时间距离当前时间过久,就需要尽快备份日志文件来释放空间。
3. 设置日志清理计划
在了解了数据库日志占用情况后,可以根据情况设置日志清理计划。
3.1 安排定时任务
在SQL Server Agent中添加一个新的定时任务,并指定清理日志的时间和操作脚本。
以下是一个示例脚本,用于清理指定日期之前的不再使用的日志文件(即已备份过的日志):
USE [database_name];
GO
DECLARE @fileName AS VARCHAR(200);
DECLARE @path AS VARCHAR(200);
DECLARE @cmd AS VARCHAR(200);
SELECT TOP 1 @fileName = name, @path = physical_name
FROM sys.database_files
WHERE type_desc = 'LOG';
SET @cmd = 'BACKUP LOG [' + DB_NAME() + '] TO DISK='''
+ @path + '_backup_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', '_') +'''';
BACKUP LOG [database_name] TO DISK = N'backup_device' WITH NOFORMAT, NOINIT, NAME = N'backup_name', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
注意:其中,@ path变量表示MSSQL日志文件所在的路径,@fileName变量表示MSSQL日志文件的文件名。DATEADD()函数用于指定保留的最近日期,例如此处设置为7天。
3.2 备份日志并清理日志
首先,需要将当前日志文件备份到指定的目录中。
BACKUP LOG [database_name]
TO DISK = N'backup_device'
WITH NOFORMAT, NOINIT, NAME = N'backup_name', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
接下来,执行以下代码进行日志清理:
DBCC SHRINKFILE('log_file_name', 0);
其中,'log_file_name'表示日志文件的名称。
注意: 在进行日志清理时,应该先备份当前日志文件,以防数据丢失。
4. 总结
本文介绍了定时清理MSSQL数据库日志文件的小技巧。通过设置日志清理计划,可以定期清理不再使用的日志文件,释放磁盘空间。同时,需要注意备份日志并且慎重进行日志清理,以避免数据丢失。