MSSQL 清理日志

USE[master]
 GO
 ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
 GO
 ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE --简单模式
 GO
 USE 要清理的数据库名称
 GO
 DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M，可以自行指定
 GO
 USE[master]
 GO
 ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
 GO
 ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL --还原为完全模式
 GO

--清理所有数据库
IF NOT EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'DatabaseLogSize') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
CREATE TABLE [DatabaseLogSize]
(
[dbname] [nvarchar](100) NULL,
[dbid] [int] NULL,
[log_logical_name] [nvarchar](100) NULL,
[logsize] [decimal](30, 2) NULL,
[logused] [decimal](30, 2) NULL,
[status] [int] NULL
) ON [PRIMARY]
END
    ELSE
    BEGIN
DELETE FROM [DatabaseLogSize]
END
    GO

--借助dbcc sqlperf(logspace)将数据插入上面新建的表
INSERT INTO [DatabaseLogSize]
([dbname],[logsize],[logused],[status])
EXECUTE('dbcc sqlperf(logspace)')
GO

UPDATE [DatabaseLogSize]
SET
[dbid]=T_DB_ID.[dbid]
FROM [DatabaseLogSize]
INNER JOIN
(
SELECT [dbid], [name]
FROM master..sysdatabases
) AS T_DB_ID
ON [DatabaseLogSize].[dbname]=T_DB_ID.[name]
GO

UPDATE [DatabaseLogSize]
SET [log_logical_name]=T_DB_LOGNAME.[name]
FROM [DatabaseLogSize]
INNER JOIN
(
SELECT [name], database_id
FROM sys.master_files
WHERE type = 1
) AS T_DB_LOGNAME
ON [DatabaseLogSize].[dbid]=T_DB_LOGNAME.database_id
GO

CREATE TABLE #LogNeedShrink
(
[dbname] NVARCHAR(100)
,
[log_logical_name] NVARCHAR(100)
)
GO

INSERT INTO #LogNeedShrink
SELECT [dbname], [log_logical_name]
FROM [DatabaseLogSize]
WHERE [logsize]>50
--收缩超过50MB大小的日志文件
GO

WHILE (SELECT COUNT(1)
FROM #LogNeedShrink)>0
BEGIN
DECLARE @CurrentDbName NVARCHAR(100)
DECLARE @CurrentlogName NVARCHAR(100)
SELECT TOP 1
@CurrentDbName=[dbname], @CurrentlogName=[log_logical_name]
FROM #LogNeedShrink
DECLARE @ShrinkScript NVARCHAR(1000);
SET @ShrinkScript=N'
USE[master] '+N'
ALTER DATABASE '+QUOTENAME(@CurrentDbName,N'[')+' SET RECOVERY SIMPLE WITH NO_WAIT '+N'
ALTER DATABASE '+QUOTENAME(@CurrentDbName,N'[')+' SET RECOVERY SIMPLE '+N'
USE '+QUOTENAME(@CurrentDbName,N'[')+' '+N'
DBCC SHRINKFILE (N'''+@CurrentlogName+N''' , 2, TRUNCATEONLY)'+N'
USE[master] '+N'
ALTER DATABASE '+QUOTENAME(@CurrentDbName,N'[')+' SET RECOVERY FULL WITH NO_WAIT '+N'
ALTER DATABASE '+QUOTENAME(@CurrentDbName,N'[')+' SET RECOVERY FULL '
    print(@ShrinkScript)
EXEC sp_sqlexec @ShrinkScript
DELETE FROM #LogNeedShrink WHERE [dbname]=@CurrentDbName AND [log_logical_name]=@CurrentlogName
END
GO

DROP TABLE #LogNeedShrink
GO
​