August 7, 2006 at 10:36 am
Hello,
I'm struggling a bit with sizing a transaction log. The database in
question uses the full recovery model, and transaction log backups
are happening every hour.
As a starting point, I sized the transaction log at 25% of the MDF
file. The MDF file is almost 1 GB, so the log was set to 250MB. This
is overkill most of the time, except during our weekly batch insert
of a huge amount of data using a DTS package. When the weekly insert
happens, the log file balloons to almost 1 GB.
I have read all of the caveats about not continually shrinking
transaction logs, but I am not sure what else I can do in this
situation. We can't change to the simple recovery model, as I can't
accept data loss if something goes wrong with the database.
Would it be best for me to size the log at around 1 GB, and have it
grossly underutilized 99.9% of the time? I know that autogrowth is a
bad thing, but I also wonder about the performance hit from having a
huge log file that is hardly used. We have the disk space, so having
the large log file hanging around is no problem.
Or am I thinking about this problem in the wrong way altogether?
Thanks!
Chris
August 7, 2006 at 1:04 pm
Does the extra space bother you?
You better have one huge log file that is not used than one that autogrows (or shrinks) everytime. VLogs are created everytime a log grows and this is very bad for performance.
HTH
August 9, 2006 at 3:45 am
Chris
Just to add to what Wesley said:
If you can afford the disk space, a 10GB log is not going to impair performance. If you can't, then change to simple or bulk-logged mode before your weekly batch starts, then change back after it has finished and take a full backup immediately. That way, if you have any problem you can restore up to any point in time before the batch started, then run the batch again if necessary.
Another alternative, if you really want to leave your database in full recovery mode, is to change your DTS job so that it inserts data in small batches, and to create a job that automatically backs up your log when it gets to 70 or 80% full.
John
August 9, 2006 at 7:55 am
Thanks John and Wesley for your replies. Disk space is not a concern, so I'll just leave the log large and not worry about it.
Chris
August 9, 2006 at 9:18 am
Chris,
There is another way to minimize your transaction log when you run from dts, if you open your dts package and right click anywhere, you can see the package properties and go to the advance tab and uncheck the 'use transactions' checkbox. I have found that this sometimes will reduce your log usage depending on what you're doing in your dts package.
Jules Bui
IT Operations DBA
Backup and Restore Administrator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply