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)
1 Comment. Leave new
Hi,
I’ve tested it. And I’m confused.
How is it possible that before the commit operation log file grew?
As Microsoft says: “Log records are written to disk when the transactions are committed.” So it couldn’t be possible to write records to disk when the transaction is not commited.
Please, clarify it.
Regards