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

step2: Now Run the below mention query:

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:

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:

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