patternsqlMinor
Transaction Log maintenance when switching to Simple Recovery
Viewed 0 times
simplelogswitchingmaintenancerecoverytransactionwhen
Problem
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
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)?Solution
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 :
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
Context
StackExchange Database Administrators Q#44545, answer score: 9
Revisions (0)
No revisions yet.