Reasons for Excessive Log Growth

There are several reasons for excessive log growth. Please go through the below possible reasons.

• Operating a database in FULL recovery model, without taking log backups
• Performing index maintenance
  • Index maintenance operations are a very common cause of excessive transaction log usage and growth, especially in databases using the FULL recovery model. The amount of log space required to perform index maintenance depends on the following factors
    • Rebuild or reorganize: Index rebuilds generally use a lot more space in the log.
    • Recovery model: If the risks to point-in-time recovery are understood and acceptable, then index rebuilds can be minimally logged by temporarily switching the database to run in BULK LOGGED recovery model. Index reorganization, however, is always fully logged.
  • If the Recovery Model is switched to the SIMPLE model to perform an index rebuild, the LSN chain will be immediately broken and we can only be able to recover the database to a point in time contained in the previous transaction log backup. To restart the chain, we need to switch back to the FULL model and immediately take a full or differential database backup.
  • If the Recovery Model is switch to the BULK_LOGGED model, the LSN chain is always maintained but there are still implications for the ability to perform point-in-time restores, since a log backup that contains a minimally logged operation can’t be used to recover to a point in time. If the ability to perform a point-in-time recovery is paramount for a database, then don’t use the BULK_LOGGED recovery model for index rebuilds or any other minimally logged operation, unless we can do it at a time when there is no concurrent user activity in the database. Alternatively, consider performing index reorganizations, in FULL recovery model, where possible.
  • If the BULK_LOGGED model is used, take steps to minimize the time period where point-in- time restore is unavailable, and so minimize exposure to data loss. To do this, take a log backup in FULL model, switch to BULK_LOGGED, perform the index rebuild, then switch back to FULL and take another log backup.
  • A final important point to note is that an ALTER INDEX REBUILD operation occurs in a single transaction. If the index is large, this could represent a long-running transaction that will prevent space reuse in the log for its duration. This means that, even if we rebuild an index in SIMPLE model, where we might think that the log should remain small since it is auto-truncated after a CHECKPOINT operation, and the rebuild is minimally logged, the log file can still expand quite rapidly during an extensive rebuild operation.
  • According to Microsoft https://technet.microsoft.com/en-us/library/ms189858.aspx for fragmentation levels greater than 5 percent but less than or equal to 30 percent, we should reorganize the index, and for fragmentation levels greater than 30 percent, we should rebuild it.

• Long-running or uncommitted transactions that prevent space in the transaction log from being reused.
• Lack of log space reuse
  • databases DMV provides the reason why Log space is not re-used

SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases

Log_reuse_wait_desc provides the reason for why the Log space was not reused. For possible reasons please navigate to https://msdn.microsoft.com/en-us/library/ms178534.aspx.