Transaction log grows because of maintenanc plans?

  • 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

     

  • 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

  • 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

     

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

    -SQLBill

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

    Juanita

     

  • 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

  • good idea !!! thanks !

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

  • Thanks !! Good Point!

    Juanita

     

  • 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?

     

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

  • 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 12 (of 12 total)

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