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:

1_Database_Transaction_Log_Virtual_Log_Files_Space_Required_to_Rollback_Part3

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:

2_Database_Transaction_Log_Virtual_Log_Files_Space_Required_to_Rollback_Part3

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *