SQL Server Performance Forum – Threads Archive
T-Log size after Full backupDuring the day, from 6am to 8pm I have t-logs every hour.
After business hours, I get a Full backup
Then run the Optimization and Integrity maintenance Jobs
And at last, I get another full backup
But the first T-Log at 6am next day is too big. Why? I thought a full backup truncates the T-Log.
quote:I thought a full backup truncates the T-Log.No it doesn’t.
You need to use shrinkdb or shrinkfile in combination with the backup to shrink the log.
Thanks for the replies. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />CanadaDBA
No, he needs just additional transaction log backup.
I overlooked the main question:
quote:But the first T-Log at 6am next day is too big. Why?Maintenance tasks, especially index rebuilding/defragmentation produce big log and full backup doesn’t truncate it.
If the log size is fairly constant, I wouldn’t bother shrinking it at any cost just to have it grow again during the day. —
Microsoft SQL Server MVP
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)
Again I refer to this old post.
The situation is that after business hour I have index rebuild and maintenance jobs. And in the morning I have lots of DTS imports just before the business starts. Question is that how about I set the database to SIMPLE immediately after business hours and turn it back to FULL before business starts? Is it recommended?
You could do that, but you have to run full database backup immediatelly after switching back to full. If you do so, and you don’t have to backup database before maintenance tasks. You can just execute transaction log backup instead.
I would echo Frank’s suggestion at not shrinking the log. I used to figure that was just a matter of preference. When I actually read (first for me) reference material on this, I found that there are "logical" logs within the logfile. So when it grows in chunks it isn’t all a single contiquous space anymore even after a backup. So the recommendation is to create the logfile the size you think it will grow to at the worst part of the day and then just leave it alone. LogBackups only backup the transactions so the size of the logfile isn’t relevant. Of course after having made the change in my system to no longer truncate the log file, and bumping the initial size, I got bitten on my report server because it was restoring my backup and creating the log file to the originally set size and all worked fine. But once I bumped the size in the properties (Did a shrink back to as tiny as possible then reset the properties to a very large number) then it tried to restore to that size and my report server didn’t have that much space. I was able to correct that by restoring to a different location on the report server, but thought I’d throw it out there as a warning. Good luck,