Before switching from FULL
to SIMPLE
recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE
should be OK. If this is not the case, stay with FULL
.
To shrink the LDFLDF
file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput8 Steps to better Transaction Log throughput
Wait for the time when there's low activity on the database
Run in SSMS:
DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)
DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)
Modify the transaction log file size:
ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)
ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)