Skip to main content
edited body
Source Link
James Jenkins
  • 6.3k
  • 6
  • 45
  • 87

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?

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', 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 8MG)
  • 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?

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, 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 8MB)
  • 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?

Source Link
James Jenkins
  • 6.3k
  • 6
  • 45
  • 87

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', 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 8MG)
  • 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?