SQL Server Database Transaction Log Files – Virtual Log Files: Space Required to Rollback: Part 3

Hi Friends,

Today I am going to explain a very interesting thing about SQL server database transaction log files. Which is every database transaction log file maintain some space to rollback the transaction as per the requirement of the transaction. We can understand this thing very clearly by using an example. So let’s start with the below example:

step1: Create Databases

USE [master]
go
create database TEST1
go
create database TEST2
go

step2: Now Run the below mention query:

Use [test1]
go
create table xttest
(
	id int,
	name char(8000)
)
begin tran
dbcc loginfo
declare @a int
set @a=1
while @a<60
begin
insert into xttest values(@a,'SQLGEEKS')
set @a=@a+1
end
dbcc loginfo
commit tran

The output of above query is:

Here we create a database which contains 3 VLF. When we run the data insert transaction with begin and commit then only those three VLF Files are used here. But it also creates one more VLF. Why? This is just because of that Reason “SQL Server always maintain some free space in the transaction log file to rollback the transaction.” how much free space will it contain? It will totally depend on how much space is required by the transaction to rollback.

step3: Now run the below mention code:

USE [master]
GO
ALTER DATABASE [TEST2] MODIFY FILE ( NAME = N'TEST2_log', MAXSIZE = UNLIMITED, FILEGROWTH = 0)
GO

This will disable the auto growth for the Test2 database log file. Now run the same query on database test2 which we ran on database test1.

step4: Run the below mention code:

use [test2]
go
create table xttest
(
	id int,
	name char(8000)
)
begin tran
dbcc loginfo
declare @a int
set @a=1
while @a<60
begin
insert into xttest values(@a,'SQLGEEKS')
set @a=@a+1
end
dbcc loginfo
commit tran

The output of above query is:

Here database test2 also contain 3 VLF same as database test1. We run the same query on database test2 which we ran on database test1. We also know that this query will only use existing three VLFs but along with some free space required to rollback the transaction if required. No free space is allowed here because we disable the auto growth for log file. So when we run the above query it gives the error “The transaction log for database ‘TEST2’ is full due to ‘ACTIVE_TRANSACTION’.”

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook