Skip to main content
Tweeted twitter.com/#!/StackDBAs/status/345724456032952321
deleted 121 characters in body; edited title
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

Transaction Log maint.maintenance when switching to Simple Recovery

Background: I

I recently inherited 50+ SQL Servers with 450+ databases. TheThe nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. AllAll 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 LOWlow priority ones that only need a nightly backup and a days worthwhere a day of data loss in the even of a disaster will not be a problem (Fax databases and things like that)is acceptable.

Question: I'm

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

Transaction Log maint. when switching to Simple Recovery

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 days worth of data loss in the even of a disaster will not be a problem (Fax databases and things like that).

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

Transaction Log maintenance when switching to Simple Recovery

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

Source Link
BamBamBeano
  • 231
  • 1
  • 3
  • 5

Transaction Log maint. when switching to Simple Recovery

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 days worth of data loss in the even of a disaster will not be a problem (Fax databases and things like that).

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