日志压缩----备份、分离、删除日志、附加文件

/*--调用示例
exec p_compdb 'FERMS_Iran'
--*/
use master   --注意：该存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_compdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[p_compdb]
GO

create proc p_compdb
 @dbname sysname,    --要压缩的数据库名
 @bkdatabase bit=1,    --在分离日志的步骤中,可能会损坏数据库,可以选择是否自动备份数据库
 @bkfname nvarchar(260)='' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间
as

 --1.清空日志
 exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')

 --2.截断事务日志：
 exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')

 --3.收缩数据库文件(如果不压缩,数据库的文件不会减小
 exec('DBCC SHRINKDATABASE(['+@dbname+'])')

 --4.设置自动收缩
 exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')

 ----后面的步骤有一定危险,请确保做好了数据库备份再选择下列的步骤
 --5.分离数据库
 if @bkdatabase=1
 begin
  if isnull(@bkfname,'')=''
   set @bkfname=@dbname+'_'+convert(varchar,getdate(),112)+replace(convert(varchar,getdate(),108),':','')+'.bak'
   select 提示信息='备份数据库到SQL 默认备份目录,备份文件名:'+@bkfname
   exec('backup database ['+@dbname+'] to disk='''+@bkfname+'''')
 end

 --进行分离处理
 create table #t(fname nvarchar(260),type int)
 exec('insert into #t select filename,type=status&0x40 from ['+@dbname+']..sysfiles')
 exec('sp_detach_db '''+@dbname+'''')

 --删除日志文件
 declare @fname nvarchar(260),@s varchar(8000)
 declare tb cursor local for select fname from #t where type=64
 open tb
 fetch next from tb into @fname
 while @@fetch_status=0
 begin
  set @s='del "'+rtrim(@fname)+'"'
  exec master..xp_cmdshell @s,no_output
  fetch next from tb into @fname
 end
 close tb
 deallocate tb

 --附加数据库
 set @s=''
 declare tb cursor local for select fname from #t where type=0
 open tb
 fetch next from tb into @fname
 while @@fetch_status=0
 begin
 set @s=@s+','''+rtrim(@fname)+''''
 fetch next from tb into @fname
 end
 close tb
 deallocate tb
 exec('sp_attach_single_file_db '''+@dbname+''''+@s)
go