2

I have a small (~3GB) database in Full recovery mode, with full backups at midnight and transaction log backups at noon.

Despite being a testing database with virtually no activity, the size of the log files (and transaction log backups) are growing exponentially:

Date Size
March 2: 200MB
March 3: 560MB
March 4: 963MB
March 5: 1.5GB
March 6: 2.5GB
March 7: 5.9GB

For comparison, the nigh-identical production version of the same database has transaction log backups (same schedule) under 100MB.

The autogrow is set to 10%, so each new VLF winds up 10% larger than the last, but it's a mystery to me why new VLFs are created several times a day at all! There are no open transactions, and when I checked DBCC LOGINFO last night, there were numerous large VLFs with status 0, so log backup is truncating fine. They were all status 2 this morning.

Doing periodic LOGINFO & LOGSPACE checks this morning, I saw the log expand to add a VLF, despite having virtually nothing in the last one:

loginfo increase

The previous size would have been ~6369MB, so that VLF had about 25/700MB in use.

Can something cause SQL Server to move onto a new VLF before the active one is remotely full?

EDIT:

Trace shows a 90 minute shrink and grow cycle

Action duration start end
Log File Auto Grow 1473000 2021-03-08 10:50:14.970 2021-03-08 10:50:16.443
Log File Auto Shrink 1000 2021-03-08 10:50:13.763 2021-03-08 10:50:13.763
Log File Auto Grow 1116000 2021-03-08 09:19:36.813 2021-03-08 09:19:37.930
Log File Auto Shrink 1000 2021-03-08 09:19:35.827 2021-03-08 09:19:35.827
Log File Auto Grow 1090000 2021-03-08 06:48:33.760 2021-03-08 06:48:34.850
Log File Auto Shrink 1000 2021-03-08 06:48:32.207 2021-03-08 06:48:32.207
Log File Auto Shrink 1000 2021-03-08 05:17:54.147 2021-03-08 05:17:54.147
Improve this question

1 Answer 1

Reset to default
0

Have you checked the error log ? Run select * from sys.databases & look at the log_reuse_wait_desc column. Run DBCC OPENTRAN and see if you have any open active transactions ?

Query the default trace and get the timings of when the file growths are happening https://www.mssqltips.com/sqlservertip/3445/using-the-sql-server-default-trace-to-audit-events/

Improve this answer
7
  • log_reuse_wait_desc is LOG_BACKUP, which makes sense since all VLFs are full, and it's on full recovery mode. DBCC OPENTRAN shows nothing
    – Tim Nyborg
    Mar 8, 2021 at 9:57
  • Are your transaction log backups succeeding ? Maybe time to run them more than once a day ?
    – Stephen Morris - Mo64
    Mar 8, 2021 at 9:59
  • They all succeed. Theres no reason to run them more frequently, because there's no activity, and they don't stop the exponential growth. The log hit 25GB last week before I shrank it.
    – Tim Nyborg
    Mar 8, 2021 at 10:00
  • & what is in the SQL Server error log & the windows event logs ? Most common cause of log growth is an index rebuild job so worth double checking for that but right now that seems like a long shot. Try querying the default trace to see what time the file growths are happening & see if you can get a correlation mssqltips.com/sqlservertip/3445/…
    – Stephen Morris - Mo64
    Mar 8, 2021 at 10:06
  • Nothing in the SQL error log and the windows event log only has an occasional DNS blip. The maintenance routine only has a reorganize, not a rebuild. I'll have to look into the trace.
    – Tim Nyborg
    Mar 8, 2021 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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