Active portion of Transaction Log

A transaction is considered active under various circumstances.

1. In any recovery model, while a transaction is non completed (neither committed nor rolled back) it is considered as active
2. In full or partial recovery model, until a log backup is taken even the committed transactions are considered active. In simple recovery model, it automatically becomes inactive when the transaction is committed.

Active transactions in the log keep the space. There is no way of removing the space without data loss. It is always the inactive part of the log which get cleared when BACKUP LOG with truncate only or SHRINK DATABASE command is executed. (By the way, from SQL Server 2008 onwards, BACKUP LOG WITH TRUNCATE_ONLY has no meaning. Microsoft recommends changing the recover model to simple.)

I have seen log files growing even in simple recovery model. There weren’t many large transactions happen either. I couldn’t figure the reason and I didn’t bother much too as I didn’t become that big issue to investigate.

Recently I read an article, which explained the reason: During backup process, whatever is written to the transaction log is kept active until the end of the backup process. So if the backup job takes more time, or if there were large volume of transactions (need not to be large transactions; small many transactions would do the job!) the entire volume of transaction log will be kept active until the backup job completes.


Wanna read more? Try this: Understanding SQL Backups
This article not only gives the reason for the growth of Transaction log, but also an in-depth understanding of how backup is taken in SQL Server. A good article to all those need in-depth knowledge of SQL backup!