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:
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 |