sqlserver2008+日志收缩sql语句命令

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

--查看日志大小（所有的）
dbcc sqlperf(logspace)

--在清理MSSQL[数据库]日志时，提示找不到[数据库]日志文件：有时日志名不一致或还原的其它库会出现这种情况

USE [数据库]
select * from sys.database_files

 

exec xp_readerrorlog 1,1,NULL,NULL,'2023-06-05','2023-10-30','desc'

 

 

 

 

 

 

 

 

 

 

 

 

USE master;
GO

SET NOCOUNT ON;

DECLARE
@DBName SYSNAME,
@SQL NVARCHAR(4000),
@LogFileName SYSNAME;

-- 游标遍历用户数据库，排除系统库
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE
state_desc = 'ONLINE' -- 只处理在线数据库
AND name NOT IN ('master','model','msdb','tempdb','resource') -- 排除系统库
AND is_distributor = 0; -- 排除分发库

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
-- 获取该库的日志逻辑文件名
SELECT @LogFileName = mf.name
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE db.name = @DBName AND mf.type_desc = 'LOG';

IF @LogFileName IS NOT NULL
BEGIN
-- 收缩日志到5MB，注意：实际最小会到文件初始大小，不能小于初始创建大小
SET @SQL = N'
USE [' + @DBName + N'];
DBCC SHRINKFILE ([' + @LogFileName + N'], 5);
';
PRINT N'正在收缩数据库：' + @DBName + N' 日志文件：' + @LogFileName;
EXEC sp_executesql @SQL;
END

FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO