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:
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:
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?
Trace shows a 90 minute shrink and grow cycle
|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|