Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Explaining log growth for Simple recovery database Expand / Collapse
Posted Monday, June 10, 2013 3:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:26 AM
Points: 361, Visits: 1,451
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..
Post #1461438
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse