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 18, 2005 at 4:10 am

#92254

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 to disk of the database.
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?
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 4 posts - 1 through 3 (of 3 total)

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

ron k SSCertifiable Points: 6103 More actions · Answer 1

This is not one of my best areas but if you could tell use whether your recovery mode is set for Simple or Full backups I'm sure that will help someone else get you the correct answer.

You could try checking your database properties and selecting the Auto shrink option. Also you could try exeuting the Shrink Database option from the Properties pop up menu.

ron k SSCertifiable Points: 6103 More actions · Answer 2

You might want to try posting in the "Backups" forum. I'm sure you'll get some answers there.

ron k SSCertifiable Points: 6103 More actions · Answer 3

I found this note in Books online but I didn't see anything on how to resolve this situation.

Note If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.