'Transaction logs file size increased but most of the size is not in used, why?

i have database called TestDataBase contains a table called TestTable storing 10k records. below is the size of data file, transaction logs file(TestDataBase_log) and logs pace used:

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

DBCC SQLPERF(logspace)

DbName          FileName           type_desc    CurrentSizeMB   FreeSpaceMB
TestDataBase    TestDataBase       ROWS         8.000000        3.750000
TestDataBase    TestDataBase_log   LOG          8.000000        2.179688

Database Name   Log Size (MB)   Log Space Used (%)  Status
TestDataBase    7.992188        72.82503            0

After deleting 4k records:

DELETE FROM TestTable WHERE ID BETWEEN 4000 and 7999

DbName          FileName           type_desc    CurrentSizeMB   FreeSpaceMB
TestDataBase    TestDataBase        ROWS        8.000000        3.750000
TestDataBase    TestDataBase_log    LOG         72.000000       71.023438

Database Name   Log Size (MB)   Log Space Used (%)  Status
TestDataBase    71.99219        1.359875            0

After shrinking the database

USE [TestDataBase]
GO
DBCC SHRINKDATABASE(N'TestDataBase', 25 )
GO

DbName          FileName           type_desc    CurrentSizeMB   FreeSpaceMB
TestDataBase    TestDataBase       ROWS         8.000000        4.125000
TestDataBase    TestDataBase_log    LOG         24.000000       23.078125

Database Name   Log Size (MB)   Log Space Used (%)  Status
TestDataBase    23.99219        3.872924            0

i have 2 questions here:

  1. After deleting 4k records the size of log file jumped to 72MB but most of the size(71.023438MB) is not in used so what is the purpose of increasing transaction logs if we are not using space from the added size(64MB auto growth)?

  2. After shrinking the database why transaction logs file reduced to 24MB and not to the initial size 8MB despite less than 1MB of its size is used? i tried to shrink the database again with SHRINKDATABASE(N'TestDataBase', 50 ) but there is no noticeable difference.

sql-server


Solution 1:[1]

This is normal RDBMS behavior for all relational databases. The transaction log file is used to preserve the elements before they are changed and until the transaction is terminated by a COMMIT or a ROLLBACK. This changes concerns DML (INSERT, UPDATE, DELETE, MERGE, TRUNCATE...) but also any changes of the database structure (CREATE, ALTER, DROP...) and DCL (GRANT, REVOQUE...).

The volume of recorded data can be substantial at a time t which means that this file can grow significantly. Once the transactions have been completed and the data modifications have been reported in the data files (in an operation called CHECKPOINT), SQL Server, in order to guarantee good performance, never automatically reduces this file, even if it does not contains more than unusable data.

Why ? Because all growth/shrink operations on a file is a heavy one and block (locks...) the access to the file that must be accessed as fast as possible, because writing into the transaction log file is a prior operation, before modifying the data, and during that time, tables are locked until the transaction ended...

Reducing the transaction log file with a SHRINK regularely is alos a bad practice, becaus sif the transaction log as grown, it will certainly grow again, and for the same reason, the file will locked and access forbiden the time to do so...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions