December 4, 2014 at 1:47 pm
I recently set up a brand new backup plan. It's been only a couple of days. This is how it looks.
I have a daily full backup at 9 PM and Hourly TLog backup 5 AM - 11 PM. The full backup file size is very static and only 600 MB. However, here's my concern. I don't understand why TLog backup file is so big and keeps growing. I thought it will gets smaller every time TLog backup is done. FYI, I save each TLog backup in a different name with INIT and next day restart all over again.
Full Backup: 9 PM Nightly (627 MB)
TLog Backup: 5 AM (147 MB)
TLog Backup: 6 AM (166 MB)
TLog Backup: 7 AM (186 MB)
TLog Backup: 8 AM (207 MB)
TLog Backup: 9 AM (236 MB)
TLog Backup: 10 AM (272 MB)
TLog Backup: 11 AM (301 MB)
TLog Backup: 12 AM (332 MB)
December 4, 2014 at 1:55 pm
they look like differentials since theres not one instance of them decreasing in size.
December 4, 2014 at 1:58 pm
I know. Doesn't it look like a differential. Below is the script for that TLog backup.
BACKUP LOG DBName TO DISK = @Path WITH NO_TRUNCATE, INIT
December 4, 2014 at 2:05 pm
Take out the NO_TRUNCATE option, that's basically telling it not to mark the log entries it backs up as backed up and reusable which is why your log backups keep growing in size.
December 4, 2014 at 2:07 pm
I wonder if 'NO_TRUNCATE' part of the command keeps these tlog records active, and they just accumulate? Whats the reason for using 'NO_TRUNCATE' in your case, just curious!
December 4, 2014 at 2:33 pm
I tried it without the NO_TRUNCATE. It still keeps growing.
December 4, 2014 at 3:16 pm
Well were the file sizes you posted with or without the NO_TRUNCATE option set?
December 4, 2014 at 3:37 pm
chulheekim (12/4/2014)[/b
I don't understand why TLog backup file is so big and keeps growing. I thought it will gets smaller every time TLog backup is done.
Not necessarily. It depends on the amount of log activity since the last log backup. Say, for example, that large loads are done to the db at 3:30AM. Then you would expect the 4AM log backup to be much bigger than the other backups because of the extra logging activity during that time frame.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 4, 2014 at 3:41 pm
Never mind. For some reason, the second run after the change made it smaller.
TLog Backup: 5 AM (147 MB)
TLog Backup: 6 AM (166 MB)
TLog Backup: 7 AM (186 MB)
TLog Backup: 8 AM (207 MB)
TLog Backup: 9 AM (236 MB)
TLog Backup: 10 AM (272 MB)
TLog Backup: 11 AM (301 MB)
TLog Backup: 12 AM (332 MB)
TLog Backup: 1 PM (387 MB)
TLog backup: 2 PM (12 MB)
Now I'm happy with the TLog backup file sizes. However, any reason the TLog file itself still stays the same size 387 MB? The primary db file size is only 600 MB.
December 4, 2014 at 3:43 pm
The size of the log file won't change unless you manually shrink it, so since it grew at some point to 367MB it'll stay that size even if most of it is empty.
December 4, 2014 at 4:02 pm
Thank you, everyone.
December 5, 2014 at 8:33 am
Try to do this experiment:
Run DBCC loginfo immediately before and after your log backup. Load its results to some table or copy/paste to Excel.
Sum(FileSize) where Status = 2 , plus some header info - should be the size of your future log backup. Ideally, it should be 0 right after your backup. If it's not, check sys.databases to find what's holding your log from reusing.
Do you have replication? If yes - check its monitor for errors, they might be holding your log. If not, check for open transactions with DBCC opentran.
Every time you're running DBCC loginfo, check if island of "2"-s is moving ahead. Does it beginning stays at the same place? Does it consistently increase in size? Try to run diff backup after every 3 to 5 log backups.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply