定时任务：SQLServer日志清理

1. 前言

SQLServer数据库是一个广泛应用于企业级系统的数据库，随着系统的运行，数据库中的日志文件会越来越大，如果不定期进行清理，会造成磁盘空间的浪费，甚至是影响系统的运行速度。本文将介绍如何使用SQL Server Agent创建一个定时任务，定期清理SQL Server数据库中的日志文件。

2. 创建定时任务

2.1 打开SQL Server Management Studio

在启动SQL Server Management Studio后，选择需要创建定时任务的数据库，点击右键看到“管理”选项，点击“新建管理任务”。

2.2 配置定时任务

在“新建管理任务向导”中，依次设置以下选项：

任务名称：在该选项中填写任务名称，例如“SQLServer日志清理”。

运行于步骤：在该选项中，选择何时运行该任务。通常选择“工作流主要程序”。

计划运行：在该选项中，设置该任务的运行时间和频率。可以按照需要定制任务运行的频率。

选择操作：在该选项中，选择数据库操作“Execute T-SQL Statement Task”。

编辑T-SQL语句：在该选项中，输入SQL语句进行日志清理操作。

3. SQLServer日志清理操作

3.1 查找日志文件

在SQLServer的数据目录下，有一个文件夹名为Log，里面包含了所有的事务日志文件。

USE master
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',N'NumErrorLogs'

运行以上代码可以查询当前SQL Server实例的错误日志文件个数，返回值即为文件个数。

3.2 清理日志文件

为了防止误删除，保留最近7个日志文件，之前的日志文件全部清除。

declare @Value varchar(50)
declare @LogFile varchar(200)
declare @Cmd varchar(4000)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogFileName', @LogFile OUTPUT
set @LogFile=RTRIM(@LogFile) + '.' 
declare @t table(LogDate datetime,ProcessInfo varchar(1000),Text varchar(max))
insert @t 
exec xp_readerrorlog 0, 1,'','',  'asc'
SET @Cmd='del "'+@LogFile+convert(char(8),@t[8].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
SET @Cmd='del "'+@LogFile+convert(char(8),@t[7].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
SET @Cmd='del "'+@LogFile+convert(char(8),@t[6].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
SET @Cmd='del "'+@LogFile+convert(char(8),@t[5].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
SET @Cmd='del "'+@LogFile+convert(char(8),@t[4].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
SET @Cmd='del "'+@LogFile+convert(char(8),@t[3].LogDate,112)+'*.log"'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT

4. 定时任务脚本示例

下面是一个简单的SQL Server Agent定时任务脚本示例，可以定期清理SQL Server数据库的日志文件：

USE msdb
GO
EXEC dbo.sp_add_job
    @job_name = N'SQLServer日志清理',
    @enabled = 1,
    @start_step_id = 1,
    @description = N'定时清理SQL Server日志文件'
 
EXEC dbo.sp_add_jobstep
    @job_name = N'SQLServer日志清理',
    @step_name = N'清理数据库日志文件',
    @subsystem = N'TSQL',
    @command = N'declare @Value varchar(50)
                 declare @LogFile varchar(200)
                declare @Cmd varchar(4000) 
                EXECUTE master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''Software\Microsoft\MSSQLServer\MSSQLServer'', N''ErrorLogFileName'', @LogFile OUTPUT
                set @LogFile=RTRIM(@LogFile) 
                SET @Cmd=''exec sp_cycle_errorlog''' 
                EXECUTE master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
                SET @Cmd=''exec sp_delete_backuphistory @oldest_date='' 2017-01-01 ''' 
                EXECUTE master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
                SET @Cmd=''declare @str varchar(100) SET @str=''BACKUP LOG AdventureWorks TO DISK=''D:\BACKUP\Database_AdventureWorks_tran.bak''''  
                EXECUTE (@str) ', 
    @database_name = N'master',
    @flags = 0
 
EXEC dbo.sp_add_jobschedule
    @job_name = N'SQLServer日志清理',
    @name = N'每日定时任务',
    @enabled = 1,
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 1,
    @freq_subday_interval = 0,
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 1,
    @active_start_date = 20190101,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 235959
 
GO

5. 总结

通过定时任务的方式清理SQLServer中的日志文件，可以及时释放空间，保证数据库系统的稳定运行。从这个例子中，我们可以看到SQLServer Agent的强大功能和操作日志清理的方法。如有需要，可以根据实际情况进行相应的规划和设置。