2

We have a couple of databases which are in simple recovery model. When the weekly rebuild job runs, the tlog drive is becoming full every time. How do I control the size tlog from every growing?Because these are in simple recover - should I be resizing the tlog autogrowth in chunks of 512mb instead of 10%. Would that help?

  • 1
    Index rebuilds are a size of data operation. That means your logs need to be at least as large as your largest index. Plus whatever else is going on at the time. – Erik Darling Apr 12 '17 at 4:36
6

Just because database is in simple recovery does means transaction log will not grow. Actually there is not much difference in terms of logging in full and simple recovery.

In simple recovery model transaction log is truncated when checkpoint is fired and log file grows 70% of its size subject to condition no long running transaction is holding the logs in your case it is the index rebuild which is holding things from being truncated leading to log file increase.

What you can do is use smart index rebuild technique dont rely on SSMS. There are quite few available Ola Hallengren Index rebuild, Minion Index rebuild and My Custom Script.

It may also be possible that the drive on which log file resides needs more space because your indexes are big and needs space and you have to add more space.

Please also read Why Does the Transaction Log Keep Growing or Run Out of Space?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.