Andrew Fraser DBA

Shrink Transaction Logs in all every database SQL Server

SQL Server transaction logs need backed up regularly, or they will grow and grow forever, eventually filling up all available disk space. That is best done in a maintenance plan. Unfortunately you have to remember to set that up post install, it doesn’t happen out of the box by default (why doesn’t it? Surely it would be easy for Microsoft to create a standard maintenance plan at install time?).

Oracle does’t have this problem, its redo logs are a fixed size and are automatically archived off by default

If you have a SQL Server that has been left unattended like this for a long time, you may want to shrink down the over-large transaction log files in addition to setting up a maintenance plan – although that can cause fragmentation so you might also choose to leave it be.

To shrink use commands like these:

USE [master]
GO
ALTER DATABASE [mydb] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [mydb]
GO
DBCC SHRINKFILE (N'mydb_log' , 64)  -- shrink to 64mb here which is very small, you might want to increase that.
GO
USE [master]
GO
ALTER DATABASE [mydb] SET RECOVERY FULL WITH NO_WAIT
GO

I recently had that problem on a server more than 100 databases, so I used a variant of these scripts from Bacon Bits to fix all in one operation.

Check all transaction log files sizes with:

select
    d.database_id,
    d.name,
    d.recovery_model_desc,
    mf.name [file_name],
    mf.size * 8 / 1024 [size_in_mb],
    d.log_reuse_wait_desc
from sys.databases d
join sys.master_files mf
    on d.database_id = mf.database_id
where d.recovery_model != 3 -- exclude simple
    and mf.type = 1 --0 is data, 1 is log
order by mf.size desc

And generate a T-SQL script with the required shrink commands:

-- shrink logs to 8mb each, temporarily placing databases in simple recovery mode (= at risk) during the process
use [master]

set nocount on

declare @name sysname,
    @file_id int,
    @sqlcmd varchar(max)

DECLARE db_mps_simple_logs_cur CURSOR FOR
select d.name, mf.file_id
 from sys.databases d
 join sys.master_files mf
   on d.database_id = mf.database_id
where d.recovery_model != 3 -- exclude simple recovery 
  and mf.type = 1 -- 0 is data, 1 is log
  and d.name not in ('master','tempdb','msdb','model')  -- exclude these databases
  and mf.size * 8 / 1024 > 128 -- only run against logfiles of at least 128mb

open db_mps_simple_logs_cur
fetch next from db_mps_simple_logs_cur into @name, @file_id

while @@fetch_status = 0 begin

    set @sqlcmd = 'use master ; alter database ' + QUOTENAME(@name) + ' set recovery simple with no_wait ; '
       + 'use ' + QUOTENAME(@name) + '; checkpoint; dbcc shrinkfile ( ' + cast(@file_id as varchar) + ', 64 ) ; '  -- 64mb size, may be overkill
       + 'use master  ; alter database ' + QUOTENAME(@name) + ' set recovery full with no_wait ; '
    -- exec ( @sqlcmd )
    PRINT @sqlcmd
    fetch next from db_mps_simple_logs_cur into @name, @file_id
end

close db_mps_simple_logs_cur
deallocate db_mps_simple_logs_cur
go

Full database backups should be taken before doing this, and again after, because switching into simple recover mode even temporarily breaks the recovery log sequence.