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.