April 18, 2025 at 12:42 pm
I have a database that is in full backup recovery model. When the log file is backed up the usage size is not getting smaller [ dbcc sqlperf(logspace) ] . For example, if the log file is 1000 MB, and 800MB is used, the expectation is when a log backup occurs, the usage will then be a small amount <5% used. What I have found out is if I run a checkpoint manually on the database and then do a log backup, the usage will get smaller as expected. These log files will continue to grow indefinitely until manual action is taken freeing up space. I have also narrowed the issue down to having a memory optimized file group on the database. I wanted to know if anyone else has run into a similar issue and/or if there are any known settings to configure to ensure that the automatic checkpoints are occurring while having a memory optimized file group.
April 19, 2025 at 1:08 am
Just gonna leave this here:
https://www.mssqltips.com/tutorial/how-to-shrink-the-transaction-log/
April 19, 2025 at 2:37 pm
Just gonna leave this here:
https://www.mssqltips.com/tutorial/how-to-shrink-the-transaction-log/%5B/quote%5D
I don't think the OP is asking about how to shrink the file - rather, the VLF's are not getting cleared and marked as reusable.
First thing I would check is 'lock pages in memory' - there is a known bug that affects the transaction log with memory-optimized tables if that setting is enabled.
I would also check target recovery time for the database - the new default is 60 (Indirect) where the previous default was 0 (Automatic). I would recommend setting it to indirect checkpoints if it isn't already set.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2025 at 7:44 pm
Thanks Jeffery for the response, you are correct this issue relates to the VLF's not getting cleared/reused.
The lock pages in memory is not enabled.
The target recovery time is set to 60 seconds, I have tested this with both 0 and 60 values and the issue is still occurring.
Just to give more background, I have created a brand new database and created two tables on it, one being memory optimized. The DB is set to full recovery mode and I have done a full backup against it. I've set the Log file max size to 100MB and then I just insert some data into the non-memory optimized table increasing the log size. I then do Log file backups when the log size reaches about 75% space used and the percentage of the log file size used remains the same. However when I do a manual checkpoint and then do a log backup, the space used does shrink as expected to <15%.
I am not sure if this is related but I do get the attached result when running the below query and both my new DB and all of the DB's having this issue. This transaction runs indefinitely (total_elapsed_time continues to increase) and I am not sure if that it what is causing the automatic checkpoints to not run properly.
SELECT *
FROM sys.dm_exec_requests r
WHERE database_id = DB_ID('Matt')
May 14, 2025 at 1:25 pm
Just gonna leave this here:
https://www.mssqltips.com/tutorial/how-to-shrink-the-transaction-log/%5B/quote%5D
Thanks for the link, you saved my day.
May 14, 2025 at 5:17 pm
This was removed by the editor as SPAM
May 14, 2025 at 8:46 pm
run Chekpoint on database and try take a log backup if log smaller or take full backup then shrink the log file.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply