October 4, 2006 at 8:53 am
Hi
Im doing some testing on mirroring at the moment, and have found out about the inherent problems with transaction log size that thie replication scenario entails. However I have found that frequent trans log backups are a way around the problem - This sets the vast majority of VLFs in the trans log to state 0 allowing them to be overwritten by new transactions VLF's. Accordingly I dont need to autogrow half as often.
The question is which is the most harmful of system performance? frequent trans log backups or autogrows? if i DO backup frequently i DONT need to autogrow, and if i DONT back up frequently i DO need to autogrow...
which which people choose?
Cheers
Alastair Jones
Methodology Group
October 5, 2006 at 12:53 am
I prefer frequent backups than autogrows.
If your system is so busy that your transaction log grows quickly, you should also consider differential backups if you don't have yet. Of course, you will have to balance between differential backups and transaction log backups.
October 5, 2006 at 6:49 am
You can also reserve a large amount of disk space for the T-Log. Therefore it won't need to autogrow as often.
Maybe an alert when it gets close to growing would help as well. This way you can determine when the grow occurs.
October 5, 2006 at 7:50 am
The correct way is to size your transaction log so that you should never need autogrow.
I know you are going to say... it is not possible. Well it is ... to some extent. autogrow should be used just as a fail safe. You can create an alert that automatically backups the tlog when it reaches lets say 70% full. Besides the Tlog backup schedule should be also adjusted according to your needs I have seen places in which they are as frequent as 15 min appart and others that do it once a day. Feel free to adjust yours.
Autogrow will halt all activity in the database. Backups are supposed to be online operations.
Cheers,
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply