Transaction log grows continuosly, even with daily backups

Question

Transaction log grows continuosly, even with daily backups

EDUSQL

SSC Enthusiast

Points: 161
More actions
November 21, 2005 at 2:29 am

#169787

Dear All
We have hired a company to build a system based on a SQL database, in a nutshell a process inputs data from remote sensors continuosly to the database and then users retrieve the data a web page.
We encountered some serious problems with the database a month ago, when it appeared as if the database had blocked. I spotted a abnormaly huge file on our system (98GB) which turned out to be the transaction log and the cause of the problem.
The company somehow reduced it, I think by creating a new database and copying it to it, or something along those lines, and the system seems to work fine now.
We do daily backups of the databse to disk, but not sure if this affects the transaction log.
The .MDF file is 213MB, but after 3weeks the .LDF is already 3GB !
The reason i'm contacting for help is that i need to establish if:
1.- Is it normal that transaction logs grow continuosly? I thought they stabilized after a while?
2.- If it is normal, how can it be prevented? can it not be configured to be truncated automatically?
3.- Shouldn't the daily backups limit the size log? if they should why is it not happening? is it not properly configured?
I unfortunately now very little of databases (that's why we hired a company to do the work for us) so if at all possible bare this in mind in your responses 😉
Thanks very much for your advice,
Eduardo

Viewing 8 posts - 1 through 7 (of 7 total)

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

leifah Right there with Babe Points: 724 More actions · Answer 1

But does your maintenance routine include a transaction log backup? Backing up the database won't do much for your transaction log.

<1.- Is it normal that transaction logs grow continuosly? I thought they stabilized after a while?>

Yes, they'll grow if you never back them up or shrink them otherwise. What I prefer to do is to allocate a LOT of space for my database and log files upon creation. This prevents poor performance when the files grow, and I believe it reduces file fragmentation. Then I create backup-jobs for BOTH database and log files. A couple of times a week, I run optimization jobs (reorganize indexes, update stats.)

EDUSQL SSC Enthusiast Points: 161 More actions · Answer 2

Thanks for your reply,

I'm assured by our IT staff that transaction log should be happening, so it must be enabled.

Is it possible that something could be preventing the backup of the transaction log?

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

Hi,

your IT staff should be able to tell you if and when transaction log backups occur. they also should be able to tell you if the transaction log backups are cutting of the unused log parts or not (is 'with truncate' or 'with NO_TRUNCATE' used)

Do you need the ability to do a point-in-time-recovery? If not, you might set the database recovery model to "simple", shrink the transaction log and forget about it...

regards

karl

Best regards
karl

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

What if their backups are done with a Maint Plan through Ent Mgr ?? I don't see where you get the truncate\no_truncate option there ? Perhaps they'd need to do a separate shrink of the trans log after the backup ?

Vik Nat Hall of Fame Points: 3930 More actions · Answer 5

Looks like your DB is small enough. I would run your DB in Simple mode, which would truncate your log on checkpoint and you would not worry about your trans gowing. I would then set up something like a full DB backup a few times a day and do differential backups more often.

Also check to see if you have some DB optimizations like indexdefrag or dbreindex nightly - those can cause the trans log to grow

EDUSQL SSC Enthusiast Points: 161 More actions · Answer 6

I think the simplest solution is to use the simple Mode as has been suggested by a couple of you.

I Thank you all very much for your help!

Eduardo

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Just be sure that everyone understands that with SIMPLE recovery, you can ONLY restore a previous full backup. You will be unable to restore to a specific point in time between backups. Simple is just fine in some applications, and completely unacceptable in others.