USE [master]
GO /****** Object: StoredProcedure [dbo].[pro_Shrink_Log] Script Date: 2019/8/16 16:56:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: xlc -- Create date: 2019.08.16 -- Description: 收缩数据库日志 -- ============================================= ALTER PROCEDURE [dbo].[pro_Shrink_Log] @dbName NVARCHAR(200) AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(200); DECLARE @logname NVARCHAR(200); DECLARE @dbRecovery TINYINT; -- 获取日志文件名 -- N 将内容转为unicode 中文也不会出现乱码 -- 获取日志文件名 SET @sql= N'USE [' + @dbName +'];' + Char(13) + Char(10) + 'SELECT TOP 1 @logname = NAME FROM SYS.DATABASE_FILES WHERE TYPE = 1'; EXECUTE sp_executesql @sql, N'@logname NVARCHAR(200) output',@logname output; IF @logname IS NULL BEGIN PRINT '未找到日志文件:' + @sql; RETURN; END -- 获取当前恢复模式 SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) + 'SELECT TOP 1 @dbRecovery = recovery_model FROM sys.databases where name =''' + @dbName + ''''; EXECUTE sp_executesql @sql, N'@dbRecovery TINYINT output',@dbRecovery output; -- 1、设置模式为简单模式 SET @sql = N'USE MASTER;'+Char(13) + Char(10) +'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE WITH NO_WAIT'; EXECUTE sp_executesql @sql; SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE'; EXECUTE sp_executesql @sql; -- 2、收缩文件 SET @sql = N'USE [' + @dbName + N'];' + Char(13) + Char(10) + 'DBCC SHRINKFILE (N''' + @logname + ''', 1, TRUNCATEONLY)'; EXECUTE sp_executesql @sql; -- 3、设置模式为完全 IF @dbRecovery = 1 begin SET @sql = N'USE MASTER;'+Char(13) + Char(10) +'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL WITH NO_WAIT'; EXECUTE sp_executesql @sql; SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY FULL'; EXECUTE sp_executesql @sql; end PRINT '数据库:' + @dbName + ' 日志文件:' + @logname + ' 收缩完成'; END
建议将存储过程放在 master中执行。
执行方式:
EXEC pro_Shrink_Log test
执行结果: