SqlServer 定时清理1年前过期log日志数据

定时清理1年前过期log日志数据

 

select  min(createtime),max(createtime)  from dbo.Sys_LoggerOperate

SELECT count(1) FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE()) 

delete top (2000) FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE()) 

use ge_dblog
select min(createtime),max(createtime) from dbo.Sys_LoggerOperate

SELECT count(1) FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE())

SELECT TOP (2000) *
FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE())
ORDER BY createtime ASC

WITH ToClean AS (
SELECT TOP (2000) *
FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE())
ORDER BY createtime ASC
)
DELETE FROM ToClean;

SELECT TOP (10) *
FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE())
ORDER BY createtime ASC

 

SQL Server 代理 - 作业 - 新建作业 - clean log - 选择数据库 - 输入sql脚本命令

use ge_dblog

delete top (2000) FROM dbo.Sys_LoggerOperate
WHERE createtime < DATEADD(year, -1, GETDATE()) 

配置计划 - 执行间隔 10秒一次

 

——————————————————————————————————————————————————————————————————————————————

清理日志

USE LQ_TRADE
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE LQ_TRADE
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (JSTW_Log);
GO
-- Reset the database recovery model.
ALTER DATABASE LQ_TRADE
SET RECOVERY FULL;
GO