This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration in which you can get to know that committed inner transactions are not releasing any log disk space in SQL Server.
One of our developers was telling me like when we commit the transaction it will release the occupied log space, but it is not entirely correct.
If you are dealing with nested transactions, it is true only for outer committed transactions.
Please check the below demonstration of this.
Create a sample database:
|
1 2 3 4 |
CREATE DATABASE Test_Tran; GO USE Test_Tran GO |
Check the log size:
|
1 2 3 4 5 6 |
CHECKPOINT GO DBCC SQLPERF ('LOGSPACE') GO -- the result -- 4.557673 |
Create a sample table:
|
1 2 |
CREATE TABLE tbl_TestTrans(ID INT) GO |
Open outer and inner transaction and insert few sample records:
|
1 2 3 4 5 6 7 8 |
BEGIN TRANSACTION OuterTrans GO BEGIN TRANSACTION InnerTrans GO INSERT INTO tbl_TestTrans VALUES(88) GO 1000 COMMIT TRANSACTION InnerTrans GO |
Check the log size:
The inner transaction is committed, but log size is increased, and it didn’t release the logs.
|
1 2 3 4 5 6 7 |
CHECKPOINT; GO DBCC SQLPERF ('LOGSPACE'); GO -- the result: -- 8.767107 |
Now, commit outer transaction:
|
1 2 |
COMMIT TRANSACTION OuterTrans GO |
Check the log size:
Once you commit your outer transaction, it releases the log size.
|
1 2 3 4 5 6 7 |
CHECKPOINT; GO DBCC SQLPERF ('LOGSPACE'); GO -- the result: -- 5.08 |
Leave a Reply