2

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

1
  • What happens if you actually run a command to increase the log file size? Does it increase the file or just reports the success of the operation and log it without actually changing the file size?
    – Ronaldo
    Jan 13, 2020 at 16:21

1 Answer 1

3

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.

2
  • Database is static. No restores. The top screenshot is from the Microsoft built in reports. I'm monitoring the default trace on the server to collect the events, running every 15 minutes, then merging the results to my logtable. Jan 13, 2020 at 16:06
  • @ToddHisted Interesting! I've updated my answer with some additional information you might want to capture along with the growth events, to try and track down what's going on. Jan 14, 2020 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.