Post reply

Transaction log grows continuosly, even with daily backups

  • 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

  • November 22, 2005 at 3:15 am

    #605169

    <We do daily backups of the databse to disk, but not sure if this affects the transaction log.>

    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.)

  • November 22, 2005 at 5:58 am

    #605200

    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?

  • November 22, 2005 at 6:47 am

    #605210

    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

  • November 22, 2005 at 8:52 am

    #605249

    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 ?

  • November 22, 2005 at 12:07 pm

    #605300

    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

  • December 1, 2005 at 4:46 am

    #606998

    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

  • December 1, 2005 at 9:29 am

    #607099

    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.

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

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