Joseph Michael Pesch
VP Programming

SQL Script to Shrink Database Transaction Log Files

by 26. October 2012 15:53

This script will operate on all databases that are ONLINE.  Will set recovery mode to SIMPLE temporarily shrink the log file then set recovery mode back to FULL.  In the case where the database recovery mode is already SIMPLE it will simply shrink the log file and leave the recovery mode set to SIMPLE.

declare @db varchar(255)
, @rcm varchar(25)
, @log varchar(255)
, @sql varchar(max) 

create table #tmp (db varchar(255), rcm varchar(25), lg varchar(255))
declare cur cursor for
select name, recovery_model_desc
/* convert(varchar(25), databasepropertyex(names, 'RECOVERY')) */
from sys.databases
where name not in('master', 'tempdb', 'model', 'msdb', 'distribution')
and state_desc = 'ONLINE' /* Skip anything OFFLINE */
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm
  if @@FETCH_STATUS <> 0 break
  set @sql = 'use [' + @db + '] insert into #tmp'
      + ' select ''' + @db + ''', ''' + @rcm + ''''
      + ', name from sys.sysfiles where fileid = 2'
  exec(@sql) 
end
deallocate cur

declare cur cursor for
select * from #tmp
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm, @log
  if @@FETCH_STATUS <> 0 break
  if @rcm = 'FULL' /* When FULL we will set simple then back to FULL */
    select @sql = 'use [' + @db + ']'
      + ' alter database [' + @db + '] set recovery simple with no_wait'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
      + ' alter database [' + @db + '] set recovery full with no_wait'
   else /* When already SIMPLE just shrink and leave it alone */
    select @sql = 'use [' + @db + ']'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
  exec(@sql)
end

drop table #tmp

Tags:

SQL Server

Comments are closed