Log File Size and dbcc sqlperf (logspace)

Question

Consider the following log file setting for a database named XYZ:

Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB

I have created a SQL Job to monitor the size of XYZ database ldf using the following script and insert it into a table in a different database named ABC.

DECLARE @sql_command VARCHAR(MAX)
DECLARE @action VARCHAR(MAX)
DECLARE @size REAL
DECLARE @sizeUsed REAL

IF exists (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = OBJECT_ID(N'tempdb..#TempForLogSpace'))
    DROP TABLE #TempForLogSpace;   

CREATE TABLE #TempForLogSpace
(
    DBName varchar(MAX),
    LogSize real,
    LogSpaceUsed real,
    Status int
)

SELECT @sql_command = 'dbcc sqlperf (logspace)' 
INSERT INTO #TempForLogSpace EXEC (@sql_command)

SELECT @size = LogSize, @sizeUsed = LogSpaceUsed FROM #TempForLogSpace WHERE DBName = 'XYZ'

INSERT INTO dbo.ABC.tbl (DBName, FileSize, SpaceUsedPercentage, LoggedDate)
VALUES('XYZ', @size, @sizeUsed, GETDATE())

Initial result after running it for several hours is as follows

My expectations is that,

1. as transactions being made in database XYZ, the SpaceUsedPercentage = LogSpaceUsed (DBCC) should be increasing,
2. and if the ldf size reaching the initial size, SQL server would increase the ldf file size by another 500MB and so would the the value of FileSize = LogSize (DBCC).

But after seeing the result:

1. Seems like SpaceUsedPercentage is increasing/decreasing from time to time - what is actually happening?
2. If the ldf reaching initial size - will the FileSize captured is the initial size or the increased size?

Thank you for your kind attention.

Answer 1

The space used will decrease like that for different reasons based on the recovery model of the database named XYZ.

First of all, the transaction log is "circular," meaning that portions of it (virtual log files or VLFs for short) can be reused once they are no longer needed. Marking a VLF for reuse is called "clearing" or "truncating" the transaction log.

If XYZ is in the Simple recovery model, then VLFs will be marked as resuable whenever a checkpoint operation occurs in the database (be it manual, or one of the automated checkpoints that occurs periodically).

If it's in the Full recovery model, VLFs can be reused once the information they contains has been backed up in a log backup.

It's likely one of those two things happening periodically that's causing space used to go down.

If VLFs aren't cleared fast enough, the log file will grow per your configuration / expectations (and File Size will change to the increased size).

---

You should check out the MS Docs page on SQL Server Transaction Log Architecture and Management Guide for detailed information on how the transaction log works.