Sql-server – Transaction Log maintenance when switching to Simple Recovery

backuprecoverysql servertransaction-log

Background:

I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are set to FULL recovery and the transaction logs have never been backed up. I've gone through all of the SQL Servers and identified low priority ones that only need a nightly backup and a where a day of data loss is acceptable.

Question:

I'm switching a lot of low priority databases to SIMPLE recovery mode from FULL. Will the existing transaction logs be truncated (when checkpoints are created)? Some of the existing transaction logs are 50-100GBs; what is the best approach in determining what I should shrink them down to for the purposes of moving forward? I obviously don't want to keep them that large. Or, will they shrink on their own over time (I don't think they will)?

Best Answer

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 LDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database

  2. Run in SSMS:

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)

  3. Modify the transaction log file size:

    ALTER DATABASE db_name
MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)
Related Question