在SQL SERVER 2008 R2上测试通过,其它版本请自行测试,使用时无需要切换到对应的数据库上操作
----------------------
--创建 数据库日志清空
--编写 时间 2019-5-9
----------------------
--调用 Exec ShrinkLogBy2008R2 数据库名称
If Exists (select 1 from sysobjects where name = 'ShrinkLogBy2008R2' and type = 'P')
Drop PROCEDURE ShrinkLogBy2008R2
Go
CREATE Procedure ShrinkLogBy2008R2(
@DbName Varchar(100)
)
AS
Begin
Declare @FrontSize Int --收缩前大小
Declare @AfterSize Int --收缩后大小
--1.获取当前模式
Declare @RecoveryModel Varchar(20)
Select @RecoveryModel = recovery_model_desc From sys.databases where name = @DbName
--2.修改为Simple模式
if @RecoveryModel = 'FULL'
Begin
Exec ('ALTER DATABASE ' + @DbName + ' Set Recovery Simple')
End
--3.获取日志文件名
Declare @Sql NVARCHAR(200),@LogFileName Varchar(20)
Set @Sql = 'use ' + @DbName + ' select @LogFile = name,@Size = size from sys.database_files where type_desc = ''LOG'''
Exec Sp_ExecuteSql @Sql,N'@LogFile As VARCHAR(20) OUTPUT,@Size As Int OutPut',@LogFile = @LogFileName OUTPUT,@Size = @FrontSize OUTPUT
Print ('日志文件:' + @LogFileName)
--4.收缩日志文件大小(单位是M)
Exec('use ' + @DbName + ' DBCC SHRINKFILE (' + @LogFileName + ', 0)')
--5.修改为 FULL 模式
Exec ('ALTER DATABASE ' + @DbName + ' Set Recovery FULL')
--6.收缩后大小
Set @Sql = 'use ' + @DbName + ' select @Size = size from sys.database_files where type_desc = ''LOG'''
Exec Sp_ExecuteSql @Sql,N' @Size As VARCHAR(20) OUTPUT',@Size = @AfterSize OUTPUT
--7.返回结果
Select @DbName 数据库,@LogFileName 日志文件,@FrontSize 收缩前大小,@AfterSize 收缩后大小
End