sql server 2008 – Log File Size and dbcc sqlperf (logspace)

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.


IF exists (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = OBJECT_ID(N'tempdb..#TempForLogSpace'))
    DROP 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

enter image description here

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.