A little while ago one of my work colleges asked me why a transaction log could suddenly grow in size on SQL Server 2005 . I went with the stock standard “long running transaction” answer; then a few days latter read about another reason. And have just read about another one.
So here is my in progress list of reasons the transaction log for a SQL Server 2005 database may suddenly grow in size:
- Long running transaction resulting the in inactive log records been stuck between two active log records.
- The Log Reader Agent failing when transactional replication is configured. The log records will not be truncated until they have been replicated to the distribution database.
- Transaction Log backups failing. When using either Full or Bulk Logged recovery modes log records will not be automatically truncated until they have been backed or explicitly truncated.
- Recovery model changed from Simple. In Simple mode the transaction log will be truncated when a checkpoint operations occurs. Under Full or Bulk Logged models the log will not be truncated until either the DB is backed up, the log is backed up or the log is explicitly truncated (without backup).
- The mirror DB in an asynchronous Mirror session is running slow, or possibly off line somehow.
I don’t have a complete understanding of this scenario so will try to get some more detail later.
Post a Comment