Explaining log growth for Simple recovery database

• Maddave

  SSCertifiable

  Points: 6828

  More actions

  June 10, 2013 at 3:08 am

  #278689

  I thought I understood log growth, but a bit confused why the following is happening.

  We use SQL Monitor and Ola's maintenance scripts on our 2012 instance. These are set to rebuild indexes when 30% fragmentation occurs.

  Over the weekend the SQL Monitor database, set to Simple recovery, experienced the transaction log grow from 3 GB to 35 GB and it filled up the disk. This was due to the rebuild index operation from the maintenance scripts which rebuilt just 13 indexes before the disk became full.

  Looking at these indexes, their total storage size is just over 1GB. I thought when a rebuild happens then the log would only grow to the size of the index as that is all that would need to be rolled back, so why did the log grow to 35 GB? Any why, on a simple recovery databases did the log not get reused, after the first few rebuild commands completed?

  Any pointers would be good to understand this a bit better and to narrow down the reason..

  Thanks.