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