May 15, 2014 at 5:37 pm
Comments posted to this topic are about the item Avoid Transaction Logs volume from filling
July 2, 2014 at 7:21 am
Daniel,
Thanks for taking the time to share this script. I have a question though.
Wouldn't your transaction logs backups that are performed AFTER you shrink the logs and return to FULL recovery model fail unless you first perform another FULL database backup ?
If you try to make a log backup in this situation, I believe the transaction log backup will fail with a "no current database backup" error
My understanding is that when you change from SIMPLE to FULL recovery the database still behaves as if it is still in SIMPLE mode (sometimes called "pseudo-simple") until a full backup is performed.
Shaun Stewart has an article about this problem here:
Paul Randall also has an excellent article this at:
http://www.sqlskills.com/blogs/paul/new-script-is-that-database-really-in-the-full-recovery-mode/
Thanks again.
Lee
October 23, 2014 at 8:35 am
Lee Linares (7/2/2014)
Daniel,Thanks for taking the time to share this script. I have a question though.
Wouldn't your transaction logs backups that are performed AFTER you shrink the logs and return to FULL recovery model fail unless you first perform another FULL database backup ?
If you try to make a log backup in this situation, I believe the transaction log backup will fail with a "no current database backup" error
My understanding is that when you change from SIMPLE to FULL recovery the database still behaves as if it is still in SIMPLE mode (sometimes called "pseudo-simple") until a full backup is performed.
Shaun Stewart has an article about this problem here:
Paul Randall also has an excellent article this at:
http://www.sqlskills.com/blogs/paul/new-script-is-that-database-really-in-the-full-recovery-mode/
Thanks again.
Lee
Hello Lee,
Sorry for the late anwser, but as been a while since i come here.
When your model is in FULL mode you always need a full backup of your database before a TS backup, or else you gonna get an error. After a shrink and returning to FULL yes, fisrt database backup second transaction log backup and the respective log truncate, only the you can make or not a shrink to your log.
January 27, 2016 at 7:42 am
Alter recovery model to simple then back to full breaks the log backup chain, it invalidates all existing tlog backups for that database. Potential data loss may violate RPO.
January 27, 2016 at 2:11 pm
Since i have this problem in a regular basis, i decided to make a simple script to minimize this problem.
Why not address the root of problem ? Is your database autogrowth not tuned ? You need to find out why your transaction log is growing out of control ?
What you are suggesting is not a good practice that you perform on regular basis.
HTH 😀
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 28, 2016 at 8:55 am
Thank you for letting me know that!
As you know the TL are truncated after a backup(in full recovery mode)... and when you work in a big company and you dont control the backups? and they are always failing? I think you understant!
February 25, 2016 at 4:48 pm
Thanks for the good info.
April 27, 2016 at 7:21 am
This came in handy recently. Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply