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
Categories: