how to set proper tranlog size 1

[icemel]

Hi,

Does anyone have a technique for correctly sizing transaction logs?? Apparently our translogs are auto-growing far too frequently... on the other hand, I don't want to size them too large due to limited disk space.

How to find the correct setting? I know this depends on the actual usage of the translog, but how do I figure this out?

Thanks

 

[SQLBill]

1. How often do you want them to grow? Once a day, once a week, once a month???

2. Within that time, how big does your database grow?

3. Adjust the log file according to those figures.

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

Hi, thanks for the reply. What I am looking for is a more detailed, step-by-step process for sizing a translog correctly.

I would say, I don't want the translogs to grow at all, or very seldom. I'd rather have them sized properly so that they aren't constantly going up and down in size. I read somewhere that a good rule of thumb is to size the translog to 20-25% of the db size. Or less, if the db is very large - maybe only 10% in that case.

However, I was hoping to have some kind of process that was a little more accurate... for example, to look at a history of the translog growth, and come up with some type of average size that would be optimal. This is what I don't know how to do, and would like clarification on.

Also, every night, when we do a full backup, we also truncate the related translogs... don't know if this is a good idea. Some of our databases require this occasionally, but probably not a good idea to do every night with every db, right? This could just cause them to grow a lot... specifially, we are doing:

BACKUP LOG myDB
WITH TRUNCATE_ONLY

DBCC SHRINKFILE (myDBFile, TRUNCATEONLY)
GO

[full backup]

I'm a little confused on this. However, I do know this - Quest software has determined that several of our translogs are autogrowing far too frequently.

So, I think the solution is 2-fold: 1.) size translog properly, 2.) not to shrink, as shown above

Any insight is appreciated.

Thanks

 

[SQLBill]

I think there's lots of rules and most depend on what you are doing with the database. One rule states that if you are putting a clustered index on a table, your tlog needs to be 1.2 times the size of the table. That goes for reindexing. Defragging the index also expands the tlog quite a bit. So if you are redoing indexes frequently, you should have a very large tlog size.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

One thing you'll want to remember is that your translog size also depends on your recovery mode. If you have it set to Simple (which really doesn't log anything), then you won't see a lot of movement. On the other hand, you'll lose the ability to restore to a point-in-time. Bulk-Logged doesn't log BCP or Bulk Copy type of operations, but it does log everything else and FULL logs everything.

What I would do is run a Profiler session during your most frequent activity phases (when you know the translog is bouncing up and down). Profiler can tell you what queries are running which are causing your translog to grow. Once you pinpoint those, you can see if maybe they are BCP or Bulk Copy related. If so, you can add a script in that changes your recovery mode temporarily while that operation is running and then resets your recover to FULL.

Additionally, check your translog periodically and record the sizes. After a week or two, add all the sizes together and divide them by the number of times you tracked the size. This will give you an average Log size you can shoot for. Make sure to add a little to the top of this just for wiggle room.

Lastly, you'll want to make sure you have regular transaction log backups in place (if you're not using Simple mode). This will help keep your log from over-expanding and generally keep it the same size as long as you're not doing something transaction intensive on the system.

Above all else, remember that someone else's Rule-Of-Thumb on transaction log sizes don't work for everyone. As SQLBill said, it's largely dependent on what your db is doing.

Hope this advice helps you out!

Catadmin

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???