September 15, 2009 at 9:49 am
If we increase the number of times we backup the transaction log a day then overtime the transaction log will get a little smaller correct?
And if I understand this correctly, WITH_TRUNCATE does not shrink the physical size, it just frees up the space inside the log files?
Is it a good or bad practice to run DBCC SHRINKFILE after truncating the log? Like having a maintenance plan that backs up the log (truncating it) and then with the next task to shrink the log file.
Thanks guys!
September 15, 2009 at 9:53 am
1. yes, the log size would decrease with frequency of backups
2. truncate only clears the space and does not unallocate the space
3. if you want to free up disk space, then run DBCC Shrink after you truncate to reclaim that space in the filesystem.
USE CAUTION HERE!!!
truncating the log will cause you to loose point in time recovery.
September 15, 2009 at 9:59 am
Adam Angelini (9/15/2009)
1. yes, the log size would decrease with frequency of backups2. truncate only clears the space and does not unallocate the space
3. if you want to free up disk space, then run DBCC Shrink after you truncate to reclaim that space in the filesystem.
USE CAUTION HERE!!!
truncating the log will cause you to loose point in time recovery.
Thanks Adam. I always get confused by transaction log backups. I keep thinking backing up the transaction logs will truncate the log. Is that true? If it's false then does backing up the log with the WITH_TRUNCATE option truncate the log? And if you use that option then you will lose point in time recovery?
Thanks Again
September 15, 2009 at 10:04 am
I'm retarded. with_truncate doesn't exist. i think i was mixed up with truncate_only
September 15, 2009 at 10:09 am
A log backup does a truncate in that all records that were backed up are marked as free, and that space can be re-used. Your log is a bucket that needs to hold all the log records between backups. As an example.
If I record 1 log record a minute, and back up the log every hour, I need to hold 60 (or 61) records in my log file, depending on how long the backup takes. Say that's 60 kb.
Now if I back up every 30 minutes, my log only needs to be half that size, 30kb. If I don't shrink the log file, then it will still be 60kb, and that's fine, but it's not needed. If I back up every 5 minutes, I can further reduce the size of the log.
However, you need to keep some bad in the log. There will be events at times that change the rate at which you log things, and you might need some extra space. So if my log backups are every 15 minutes and typically 10MB, I wouldn't necessarily set my log size to 10MB. I'd probably have it at 50 or 100MB because space is relatively cheap and I wouldn't want to manage it that closely. Data sizes also grow, and that is often because of increased load, which can be more log space needed. note that if I had a 100GB log file I wouldn't keep 1TB of space, but I might make it 120GB as a pad.
You need to manage log space just like database space. Periodically check (every month or so) and see if you have enough space.
September 15, 2009 at 12:06 pm
Thanks Steve.
I remember reading somewhere that if you rebuild indexes it can make the transaction log full or grow bigger. If thats the case should you run a transaction log back up right after an index rebuild?
September 15, 2009 at 12:29 pm
nice explanation steve ....
Yes, you can back up transaction log after your reindex operation. To keep log file size in check during the reindex process, i generally increase the frequency of the log backups at that time.
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply