Transaction Log maintenance when switching to Simple Recovery

Question

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)?

Answer 1

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)
   

Answer 2

I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)?

In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval (advanced server config setting) or if the log becomes 70% full.

Unless you have some long running transactions occuring that will delay log truncation, an automatic checkpoint will truncate the unused portion of the T-log.

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.

If you have the database recovery model set to FULL for those database with 50-100GBs of T-logs, then you have to start doing frequent T-log backups. Remember in Full recovery model, once a log backup chain has been established, even the automatic checkpoints wont cause the log to truncate.

As a last resort, you can truncate the log file and then immediately take a full backup and then start taking T-log backups so that you can do point-in-time recovery if a disaster happens.

Or, will they shrink on their own over time(I don't think they will)?

As @TomTom pointed out, its a manual operation.

Read up :

• Manage the Size of the Transaction Log File
• HOW TO SHRINK THE SQL SERVER LOG - Excellent Write up.
• How do checkpoints work and what gets logged - By: Paul Randal

Answer 3

Many of the question s can not be answered by us. How long is a piece of string?

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.

As long as they need to be. I suggest NO shrinking. Trunacate logs, come back in a week and see how much space is used, THEN decide. But YOU have to answer this one.

The nightly backups are roughly 8TB and needless to say, we're using more disk space than we'd like.

So why turn them to simple? I mean, seriously.

All of the databases are set to FULL recovery and the transaction logs have never been backed up.

A little logic will tell you that igf you truncate them ONCE, then you will likely use a LOT less space for backing them up anyway. The result may be that you can well keep them in full recovery mode. Try that out first. If they rally are low volume etc. then the log backups wil lbe a LOT smaller in the future.

I've gone through all of the SQL Servers and identified LOW priority ones that only need a nightly backup and a days worth of data loss in the even of a disaster will not be a problem (Fax databases and things like that).

Yes. That is until you end up in court and get your ass handed for not having critical legal documents. Do you know that the fax logs may well be part of what you are required to keep for years as business relevant information? It is like that in my jurisdiction (10 years). If you are a U stock company there may be similar surprised (SOX). Failure to do so makes it VERY bad in court if you want to prove you did not get a fax. Or did send one. No one cares whether this happened monthly back and you have more recent logs - you fail the law requirements. Make sure this is signed off by someone VERY high, because your business not critical may be your firing reason.

Or, will they shrink on their own over time(I don't think they will)?

No. And they should not do that. Log re-sizing is a manual operation except for low volume databases.