November 21, 2008 at 4:08 pm
Hi,
In our maintenance plan for each database, we're doing a nightly full backup, but I've noticed this isn't truncating the transaction logs. I thought this was how it worked in SQL 2000 where the full backup does truncate the logs, but it's not doing it in SQL 2005 on my end anyway.
So in the maintenance plan, I added a Shrink Database Task to run after the Full backup, but would it be better to put this before the full backup? Or is the full backup supposed to be truncating the logs? It seems like in MS SQL 2000 the maintenance plan asked if you wanted to truncate the logs when you setup a full backup, but I'm not seeing this in 2005 so I assume these have to be setup as separate steps.
Just checking... Thanks,
Sam
November 21, 2008 at 5:10 pm
You shouldn't be shrinking your database unless you had an unusually large transaction go through. If you shrink the logs they will grow right back to the same size. To my knowledge a backup has never truncated the transaction log, it does mark the virtual log files that have been written to disk as available so the log will wrap.
Also, if you are not doing transaction log backups throughout the day you should change the recovery model to simple.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 8:23 pm
Hmm... so is it the transaction log backup that truncates the log file? The log file can't grow indefiniately and something has to scale it down. I guess I always thought the full back up did this.
The database I'm working with has a data file of around 5 gigs and we do a backup nightly. The system will be going live next week at which time I'll setup trans log backups every 6 hours, but I've only been doing full backups since the database was setup months ago. I take it this is why the trans log was close to 45 gigs.
If we do a trans log backup 4 times a day I assume this will keep the trans log file size down?
Thanks --
Sam
November 22, 2008 at 7:02 am
Exactly.
Are you sure that the business can afford to lose 6 hours worth of data if something goes wrong? If you are just backing up the TX Log to keep the size down you should use the simple recovery model. Otherwise you need to determine how often to do log backups based on the amount of data the business can lose in a disaster. I would normally do at a minimum hourly TX log backups and at least 1 differential throughout the day.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 22, 2008 at 12:12 pm
full backup has never truncated the tran log. not in 2005, not in SQL 2000.
samalex (11/21/2008)
If we do a trans log backup 4 times a day I assume this will keep the trans log file size down?
Yes, but I'd suggest more often than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2008 at 3:50 am
samalex (11/21/2008)
Hi,In our maintenance plan for each database, we're doing a nightly full backup, but I've noticed this isn't truncating the transaction logs. I thought this was how it worked in SQL 2000 where the full backup does truncate the logs, but it's not doing it in SQL 2005 on my end anyway.
So in the maintenance plan, I added a Shrink Database Task to run after the Full backup, but would it be better to put this before the full backup? Or is the full backup supposed to be truncating the logs? It seems like in MS SQL 2000 the maintenance plan asked if you wanted to truncate the logs when you setup a full backup, but I'm not seeing this in 2005 so I assume these have to be setup as separate steps.
Just checking... Thanks,
Sam
i'm reading some confusion here. Just to state that truncating the log doesnt shrink it, they are separate actions. If the log grows, it grows for a reason. Ascertain the ideal size and leave it at that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply