——————————-迴圈資料庫壓縮記錄檔——R

--迴圈資料庫壓縮記錄檔(假設記錄檔fileid=2)
create table #db(id int identity(1,1),name varchar(80),dbsize int,mdfsize int,ldfsize int)
insert into #db
 select a.name,SUM(b.size)*8/1024 as 'dbsize[MB]',
  SUM(case when b.type<>1 then b.size else 0 end)*8/1024 as 'datafilesize[MB]',
  SUM(case when b.type=1 then b.size else 0 end)*8/1024 as 'logfilesize[MB]'
 from sys.databases as a
  inner join sys.master_files as b on a.database_id=b.database_id
 where a.database_id>4
 group by a.name
 order by SUM(case when b.type=1 then b.size else 0 end)--按日誌從大到小排序
declare @min_id int,@max_id int,@tmp_db varchar(80),@s varchar(max)
select @min_id=min(id),@max_id=max(id) from #db where ldfsize>0--此處指定記錄檔大於多少M的
while @min_id<=@max_id
 begin
  select @tmp_db=name,@min_id=@min_id+1 from #db where id=@min_id
  set @s='BACKUP LOG '+@tmp_db+' with no_log'
  exec(@s)
  set @s='use '+@tmp_db+';dbcc shrinkfile(2)'
  exec(@s)
 end
 drop table #db