SQL SERVER – Impact of Transaction on the Free Log Space

Earlier I wrote a blog post about SQL SERVER – How to Find Free Log Space in SQL Server? After reading this blog post, I received the following question from one of the users about the impact of the transaction on the free log space.

Let us create a new database and we create a transaction inside that database and along with that, we will monitor the free log space.

First Create a database with simple recovery model.

-- Create a database
CREATE DATABASE TransactionTest
GO
-- Change recovery model to simple recovery model
ALTER DATABASE [TransactionTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Next, create a sample table in the database.

USE TransactionTest
GO
CREATE TABLE TestTable (Col1 CHAR(4000),Col2 CHAR(4000) );
GO

Now begin the transaction.

-- Create a transaction
BEGIN TRANSACTION

Next check the free log space in the database. I have executed CHECKPOINT command before I measure the log space so I can flush all the pending changes from memory to a hard drive.

CHECKPOINT
-- Check the free log space
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
 used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
 (total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 
 AS free_log_space_in_MB 
FROM sys.dm_db_log_space_usage
GO

Now run the following command multiple times which will create some data and log file size will increase.

INSERT INTO TestTable (Col1, Col2)
SELECT 'a', 'b'
GO 1000

Now once again run the command to check the log size. You will notice that log size has grown a bit and there is very little free space there.

CHECKPOINT
-- Check the free log space
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
 used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
 (total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 
 AS free_log_space_in_MB 
FROM sys.dm_db_log_space_usage
GO

Next, run the command COMMIT.

COMMIT
GO

Right after that run once again the script to check free log spaces.

CHECKPOINT
-- Check the free log space
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
 used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
 (total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 
 AS free_log_space_in_MB 
FROM sys.dm_db_log_space_usage
GO

You will notice that after COMMIT is executed once again Log is emptied and it has free spaces in it.

You can clean up your newly created database by running following code.

-- Clean up
USE master
GO
DROP DATABASE TransactionTest
GO

Remember if you ever see your log file growing big in the Simple Recovery model, I suggest to check for open transactions in your business. Additionally, one more note, if you have just created a database and have not taken a full backup, the database is automatically in the simple recovery model, irrespective of the recovery model setup.

Reference: Pinal Dave (https://blog.sqlauthority.com)