收缩数据库日志

GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('usp_p_delDBLog'))
BEGIN
DROP PROC dbo.usp_p_delDBLog
END
GO
CREATE PROC usp_p_delDBLog(
@DBLogSise AS INT =0
)
/*
* 功能：收缩当前数据库日志文件
* 参数 @DBLogSise：日志文件收缩至多少M 默认收缩到最小
*/
AS
BEGIN
IF @DBLogSise<0
BEGIN
RETURN
END

--查询出数据库对应的日志文件名称
DECLARE @strDBName AS NVARCHAR(500)
DECLARE @strLogName AS NVARCHAR(500)
DECLARE @strSQL AS VARCHAR(1000)

SELECT
@strLogName=B.name,
@strDBName=A.name
FROM master.sys.databases AS A
INNER JOIN sys.master_files AS B
ON A.database_id = B.database_id
WHERE A.database_id=DB_ID()

SET @strSQL='
--设置数据库恢复模式为简单
ALTER DATABASE ['+@strDBName+'] SET RECOVERY SIMPLE;
--收缩日志文件
DBCC SHRINKFILE ('''+@strLogName+''' , '+CONVERT(VARCHAR(20),@DBLogSise)+');
--恢复数据库还原模式为完整
ALTER DATABASE ['+@strDBName+'] SET RECOVERY FULL '

exec(@strSQL)
END
GO