SQL Clone
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 130
Comments posted to this topic are about the item Avoid Transaction Logs volume from filling
Lee Linares
Lee Linares
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 1462
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 130
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 (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 262
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1302 Visits: 4325
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
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 130
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16188 Visits: 885
Thanks for the good info.
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16188 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