清空和截断数据库日志并收缩数据库

ALTER proc [dbo].[proc_sys_ClearDBLog]
as
begin
  set nocount on
  declare @dbname varchar(200)
  declare cur_db cursor for
    select [name] from master.dbo.sysdatabases where
      [name] not in( 'tempdb','northwind','model','master','msdb','Resource','VM2009', 'VM2009_BK', 'VM2010')
  open cur_db
  fetch next from cur_db into @dbname
  while @@FETCH_STATUS = 0
  begin
    --1.清空日志
    exec('DUMP TRANSACTION [' + @dbname + '] WITH  NO_LOG')
    --2.截断事务日志：
    exec('BACKUP LOG [' + @dbname + '] WITH NO_LOG')
    --3.收缩数据库文件(如果不压缩,数据库的文件不会减小
    exec('DBCC SHRINKDATABASE([' + @dbname + '])')
    fetch next from cur_db into @dbname
  end
  close cur_db
  DEALLOCATE cur_db
  set nocount off
end