Overview:
Significance: many small teams lack Database Admin resource to babysit SQL databases; therefore, certain transaction logs would grow to be oversized and cause performance issues with SQL Server. Here are the comparisons:
SIMPLE: the SQL Server Transaction Log would automatically truncate on every transaction being committed if Database is configured with the Simple recovery model. This also happens when a Checkpoint operator is triggered.
FULL: the transaction log (LDF) will grow continuously. It will be cleared when a backup of the transaction log triggered (e.g. BACKUP LOG dbname TO DISK = ‘DB_LOG.bak’.
BULK LOGGED: minimal log space usage mode. The transaction log files would clear during a backup operation. There is no automatic log truncation prior.
Practical T-SQL Commands
-- Get Recovery Model of All Databases:
SELECT Name,DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]
FROM master.dbo.sysdatabases
-- Sample Output
Name Recovery Model
master SIMPLE
tempdb SIMPLE
model SIMPLE
msdb SIMPLE
TestDB1 FULL
TestDB2 SIMPLE
-- Check log sizes
DBCC SQLPERF (LOGSPACE);
GO
-- Sample output
Database Name Log Size (MB) Log Space Used (%) Status
master 2.242188 39.89547 0
tempdb 71.99219 20.26587 0
model 7.992188 17.64418 0
msdb 28.80469 5.994033 0
TestDB1 71.99219 5.225176 0
TestDB2 7.992188 29.86315 0
-- Get logical name of log file
SELECT name,physical_name FROM sys.master_files WHERE type_desc = 'LOG'
-- Shrink database log
USE [TestDB1]
GO
DBCC SHRINKFILE (N'TestDB1_LOG' , 0, TRUNCATEONLY)
GO
-- Shrink log by performing backup
BACKUP LOG [TestDB1] TO T:\TestDB1_log.bak
-- Set recovery model to SIMPLE and shrink log to 8GB
USE [TestDB1]
ALTER DATABASE [TestDB1] SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([TestDB1_log], 8192);
GO
-- Set recovery model to SIMPLE and shrink log to 1MB
USE TestDB1
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TestDB1
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB
DBCC SHRINKFILE (TestDB1_log, 1);
GO
-- Optional: reset the database recovery model to FULL mode
ALTER DATABASE TestDB1
SET RECOVERY FULL;
GO
-- Change recovery model to FULL
ALTER DATABASE [TestDB1] SET RECOVERY FULL
GO
-- Truncating Data File - NOT log file
USE TestDB1;
GO
SELECT file_id, name -- obtain file_id prior to truncation
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);
-- Set all databases recovery model to SIMPLE and truncate all logs
declare @db_name nvarchar(124)
declare cursor cursor FOR
SELECT name AS DBName
FROM sys.databases
where name NOT IN ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor
FETCH NEXT FROM cursor INTO @db_name
WHILE (@@FETCH_STATUS=0)
BEGIN
exec ('declare @logname nvarchar(124)
USE [' + @db_name + ']
SELECT @logname = name FROM sys.database_files where type = 1
ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
DBCC SHRINKFILE (@logname , 0, TRUNCATEONLY)')
ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
FETCH NEXT FROM cursor INTO @db_name
END
CLOSE cursor
DEALLOCATE cursor
