Post reply

Transaction log grows because of maintenanc plans?

  • October 4, 2006 at 12:45 pm

    #170602

    HI. Can someone tell me what step in the maintenance plan causes the transaction log to grow? Is it the optimization or the integrity? From what I understand the backing up of the database does not write to the transaction log. Some of my transaction logs are large after the maintenance plan runs even thought the used space is low, the log itself is large.

    Thanks,

    Juanita

     

  • October 4, 2006 at 1:08 pm

    #664165

    Is your maintenance plan doing reindexing? If so, note that reindexing can cause the log file to grow to 2.5 times the size of the database.

    -SQLBill

  • October 4, 2006 at 1:13 pm

    #664167

    Would that be on the optimization tab where it says 'REORGANIZE DATA AND INDEX PAGES'? i have that checked off with the box underneath checked off also that reads, 'change free space per page percentage to'.

    Juanita

     

  • October 4, 2006 at 1:26 pm

    #664173

    I believe so. I don't use maintenace plans...I write my own jobs to do what needs done.

    -SQLBill

  • October 4, 2006 at 1:29 pm

    #664174

    okay, well that would make sense then. thanks so much !

    Juanita

     

  • October 5, 2006 at 12:47 pm

    #664426

    Yeah, I had this problem too (50Gb database, caused a few issues ); got around it by adding an extra couple of steps to the job around the optimisation step. First step switches database to 'simple', and then back to 'full' after the optimisation has completed.

    HTH

  • October 5, 2006 at 12:59 pm

    #664434

    good idea !!! thanks !

  • October 17, 2006 at 7:09 am

    #666192

    Here's something to think about if you are going to switch recovery to simple and then back to full.  I had the same idea to get around this problem.  It seemed like a good one too, until I discovered that I had ruined my ability to restore any additional log files.

  • October 17, 2006 at 7:14 am

    #666197

    Thanks !! Good Point!

    Juanita

     

  • December 11, 2006 at 9:03 am

    #677235

    Wow, glad I found this post, I was having the same problem with a ~6GB database here.  Trans logs were growing to 6GB unexplainably during the night, and we determined the maintenance plan was definitely it!

    I've been using the Maintenance Plan wizard to setup daily "Reorganize data & index pages" along with daily full backups for years, on databases ranging from a few MB to 400+GB.  I've never, ever seen the transaction log grow like that because of a maintenance plan before.  I've always used the Full recovery model on all my databases.

    Does anyone know what situation specifically causes the optimizations job to make the trans log grow so much?  Bad disk fragmentation?  Lots of transactions during the day?

     

  • December 11, 2006 at 9:07 am

    #677236

    Well you might want to think about scheduling a job to shrink transaction logs.

  • December 11, 2006 at 9:17 pm

    #677401

    You have the schedule option in

    Update DataOptimization Information Step

    which performs the ReIndexing at specified schedule time

    With This you can set the schedule when to ReIndexing which causes Transaction Log grow at that schedule only instead of every day

    Thank You

     

    Thanks ,

    Shekhar

Viewing 12 posts - 1 through 11 (of 11 total)

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