Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoid Transaction Logs volume from filling


Avoid Transaction Logs volume from filling

Author
Message
daniel_marques
daniel_marques
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129
Comments posted to this topic are about the item Avoid Transaction Logs volume from filling
Lee Linares
Lee Linares
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1408
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:
http://shaunjstuart.com/archive/2013/01/determining-if-a-full-backup-is-needed-for-a-log-or-differential-backup/#ixzz2zB0AglRz

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
daniel_marques
daniel_marques
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129
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:
http://shaunjstuart.com/archive/2013/01/determining-if-a-full-backup-is-needed-for-a-log-or-differential-backup/#ixzz2zB0AglRz

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.
sterling3721
sterling3721
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 250
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.
SQLQuest29
SQLQuest29
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 4316
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 :-D

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
daniel_marques
daniel_marques
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129
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!
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9816 Visits: 885
Thanks for the good info.
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9816 Visits: 885
This came in handy recently. Thanks again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search