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!

Full Recovery Model

If you haven't change the recovery model of "model" database, you the new databases you create will be in full recovery model by default. However, it does not mean, all the changes done to the database are logged and kept.
Until the first backup is taken, all databases behave like they are in simple recovery model. So to take the full benefit of the recovery model, take the backup of the database immediately after creation.
Want to check it out?
Here is the test:
In any recovery model, transactions are written to log and forced to write to disk when transaction is completed. In simple recovery model, transactions will be marked inactive when these conditions met:
1. Transaction is either committed or rolled back.
2. The data page is written to disk.
So, These are the steps:
1. Create a database and a table there.
2. Add data one by one and check the log file size.
3. Add the same data in one statement and check the size.
4. Drop the database
5. Repeat step 1
6. Take a backup
7. Repeat steps 2 & 3
Please note, to avoid log file growth due to data pages waiting in memory (to be written to disk) we need to flush them to disk before the next insert.
Now try the code:

-- ********************* FIRST TEST ************************
SET NOCOUNT ON
-- Create database
USE master;
CREATE DATABASE RecoveryModel
GO
--Make sure it is in full recovery model
SELECT 'Before Backup' = DATABASEPROPERTYEX('RecoveryModel', 'Recovery')
GO
-- Create table
USE RecoveryModel;
GO
CREATE TABLE Test
(
TestID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
SampleData nvarchar(max) DEFAULT(REPLICATE(N'a', 8000))
)
;
-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'
--Insert 1000 rows one by one
DECLARE @i int
SET @i=1
WHILE @i <>
BEGIN
INSERT INTO Test DEFAULT VALUES;
SET @i =@i+1
END

-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'


--Insert 1000 rows at once
INSERT INTO Test SELECT SampleData FROM Test WHERE TestID<=1000;
--Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
GO
USE master;
DROP DATABASE RecoveryModel;
GO

--******************** SECOND TEST *****************************
SET NOCOUNT ON
-- Create database
USE master;
CREATE DATABASE RecoveryModel
GO
--Make sure it is in full recovery model
SELECT 'Before Backup' = DATABASEPROPERTYEX('RecoveryModel', 'Recovery')
GO
-- Create table
USE RecoveryModel;
GO
CREATE TABLE Test
(
TestID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
SampleData nvarchar(max) DEFAULT(REPLICATE(N'a', 8000))
)
;
BACKUP DATABASE RecoveryModel TO DISK = 'C:\RecoveryModel.bak';
GO
-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'
--Insert 1000 rows one by one
DECLARE @i int
SET @i=1
WHILE @i<1000
BEGIN
INSERT INTO Test DEFAULT VALUES;
SET @i =@i+1
END

-- Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
CHECKPOINT; WAITFOR DELAY '00:00:05'

--Insert 1000 rows at once
INSERT INTO Test SELECT SampleData FROM Test WHERE TestID<=1000;
--Check for size used; Fileid =2 refers to log file
SELECT fileid, [name], [size] FROM sys.sysfiles
GO
USE master;
GO
DROP DATABASE RecoveryModel;