Sql-server – Log Autogrowths bigger than log size, but no shrinks to explain how

auto-growthsql serversql-server-2017transaction-log

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)

enter image description here

There haven't been any autoshrinks, or manual shrinks of the log file.

enter image description here

Best Answer

The most likely explanations I can think of are:

  • the database has been restored from a backup or snapshot
  • 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.

Related Question