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.
May 15, 2025 at 4:58 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy