Fast growing Transaction log

Question

Fast growing Transaction log

petter.gronskog

Newbie

Points: 2
More actions
November 15, 2007 at 7:10 am

#180564

We are having problem with fast growing transactions log files.
The transaction log is growing from 100-200 MB to 1-2 GB in a half our depending on our systems we are hosting.
How can we deal with this?
Can you have a second transaction log that it will fall back on or is the key to expand the transaction disk to 8 GB.
Thanks Petter

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

What you can do is to take more frequent transaction log backups, or (this is not a good approach, but depends on the requirements) switch to simple or bulk (if you do reindex for example) temporarily, then take a full backup.

It is probably worth finding out the reason for why the transaction log is growing so fast. Is it a dbreindex? (in this case switching to bulk helps), ...

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

petter.gronskog Newbie Points: 2 More actions · Answer 2

Thanks for the fast reply.

It´s BizTalk that is generating the transaction, we know what generate the transaction and I don´t think we can do anything about it. We need to backup everything and I men everything ;)…

We are doing the transaction backup every our, is there any down side if you perform it every 15 minutes?

Petter

Clive Strong SSChampion Points: 11222 More actions · Answer 3

You can take transaction log backups as frequently as you want. Every minute if you really want to.

But, not knowing your system, you have to strike the balance between frequency and performance. Also, one thing I would suggest is that you mention it blows up from say 200MB to 2GB. Personally, I'd not shrink the log down to 200MB. The process needs the logspace, so your only wasting IOs having to constantly grow the log file.

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 4

I agree with Clive. Figure out what your transaction logs require every hour. You don't save space by running more frequent log backups, the same amount of log backup space is needed, but you can keep the log backup file at a lower level and perhaps offload the log backups to another server after they are made.