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,
- as transactions being made in database XYZ, the SpaceUsedPercentage = LogSpaceUsed (DBCC) should be increasing,
- 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:
- Seems like SpaceUsedPercentage is increasing/decreasing from time to time - what is actually happening?
- If the ldf reaching initial size - will the FileSize captured is the initial size or the increased size?
Thank you for your kind attention.