Log Autogrowths bigger than log size, but no shrinks to explain how I have a database with a logfile size of 1GB.  I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows.

What I'm experiencing though, is that SQL Server is saying there were autogrowths of the log file, but the filesize of the log file is not actually changing.  There are enough autogrowths to have grown my file to 12GB in size since the beginning of the year, but my file is still only 1GB.

Does anyone know how/why this would happen?

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)   Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor) 



There haven't been any autoshrinks, or manual shrinks of the log file.
The most likely explanations I can think of are:


the database has been restored from a backup or snapshot


in the case of a restore from backup, the log file would be reverted to whatever size it was when the backup was taken
in the case of a restore from a snapshot, the log file is discarded and replaced with a tiny, 2-vlf log file

the monitoring is missing shrink events, or duplicating growth events due to a bug


the default trace event "Log File Auto Shrink" will only capture automatic shrinks.  If someone uses DBCC SHRINKFILE, it will show up as an Audit DBCC Event event.  So make sure you're capturing both



Check to see if restores are regularly occurring (for instance, if this is a test or reporting database).  Otherwise, you might need to review the code for the monitoring queries, and make sure there aren't missing or duplicated events.

One way to try and gather more information would be to use DBCC SQLPERF('LOGSPACE') to determine if the size of the log is ever changing (thanks to Paul Randal for suggesting this).  You could add this to your existing every-15-minutes check, by dumping the output into a temp table (and from there you could insert it into your logging tables):

CREATE TABLE #log_space
( 
    [DatabaseName] sysname NOT NULL,
    LogSizeMb float NOT NULL,
    LogSpaceUsedPct float NOT NULL,
    [Status] int NOT NULL
);

INSERT INTO #log_space
EXEC ('DBCC SQLPERF(LOGSPACE);');


This will help you track down whether the file ever actually grows.  If it does, now you'll know at what point in the day it's shrinking again.