SQL数据库日志文件收缩

USE [master]
GO
ALTER DATABASE LoganPlanSystem SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE LoganPlanSystem SET RECOVERY SIMPLE   --简单模式
GO
USE  [LoganPlanSystem]
GO
--收缩日志文件为2G = 2000M
SELECT * FROM sys.database_files--获取日志文件
DBCC SHRINKFILE (N'lc_mds_fc_log' , 2000, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE LoganPlanSystem SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE LoganPlanSystem SET RECOVERY FULL  --还原为完全模式
GO

优化成存储过程

USE [ZhongNanMDS_Formal]
GO
/****** Object:  StoredProcedure [dbo].[ShrinkLogFile]    Script Date: 2020/6/29 13:29:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Chad.Cao
-- Create date: 2020/06/29
-- Description:	收缩日志文件
-- EXEC [dbo].[ShrinkLogFile] 'ZhongNanMDS_Formal_PrePhase2',2000
-- =============================================
ALTER PROCEDURE [dbo].[ShrinkLogFile]
	@DatabaseName VARCHAR(2000)--数据库名字
	,@FileSize INT--收缩大小，单位M
AS
BEGIN
	PRINT '简单模式'
	EXEC('ALTER DATABASE '+@DatabaseName+' SET RECOVERY SIMPLE WITH NO_WAIT')
	EXEC('ALTER DATABASE '+@DatabaseName+' SET RECOVERY SIMPLE') 

	PRINT '获取日志文件'
	DECLARE @name VARCHAR(50) 
	SELECT @name=[name] FROM sys.database_files WHERE type=1
	PRINT '收缩日志文件'
	DBCC SHRINKFILE (@name, @FileSize, TRUNCATEONLY)

	PRINT '还原为完全模式'
	EXEC('ALTER DATABASE '+@DatabaseName+' SET RECOVERY FULL WITH NO_WAIT')
	EXEC('ALTER DATABASE '+@DatabaseName+' SET RECOVERY FULL')  
END