I (OP on the question) was at a loss on why, "Pseudo-Simple SQL Server Recover" was working as defined in multiple posts, but I was not seeing it on my system with these tests.
In the research stage, I put the database in SIMPLE
and recreated the test, the log file used size grew the same as it did in `FULL'FULL
, this suggested something with Control Transaction Durability but further testing ruled this out.
After more research and testing I figured it out! (Thank you to a coworker who helped me problem solve.)
- We are using 50MB as the start size for 'model' which means RecoveryModel starts the same. (Default SQL 2017 is 8MG8MB)
- This was creating 4 VLFs at about 12.5MB each
- The workload I am using in the test above, creates about 2MB of data in the logs with each run.
- I was running the test work load 3 to 5 times (6 to 10MB of logs), before doing the
COPY ONLY
backup. - The VLF used space was growing, but as it never filled up the first VLF, it stayed at status 2, and nothing was released.
- When the test seemed to give unexpected results I dropped the database and started a new test.
- In a follow up test I ran the workload to create ~15MB of logs, then ran the
COPY ONLY
and used space fell to 2MB, with the first VLF returning to Status 0 and the second VLF holding the last 2MB - This can be validated by using the code below in addition to above to modify and test for yourself.
.
USE [RecoveryModel]
GO
DBCC LOGINFO
Related info talking about VLF's: Too many VLFs - How do I truncate them?