set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[p清除日志]
@databasename varchar(100) -- 数据库名称
as
begin
declare @execsql nvarchar(max), -- 执行语句
@logfilename varchar(100) -- 日志逻辑文件
select @execsql='select @filename=name from '+@databasename
+'.dbo.[sysfiles] where fileid=2' -- 查询文件名
-- select 1,@execsql,@logfilename
exec sp_executesql @execsql,N'@filename varchar(100) output ',@logfilename output
-- select 1,@logfilename
select @execsql='use '+@databasename
+' dump transaction '+@databasename+' with no_log '
+' DBCC SHRINKFILE ('+@logfilename+') ' -- 清除脚本
-- select @execsql
exec sp_executesql @execsql -- 清除
end
/*
exec [p清除日志] 'master'
*/