SQL Server - Log file not getting smaller on Log backup

  • 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.

  • Just gonna leave this here:

    https://www.mssqltips.com/tutorial/how-to-shrink-the-transaction-log/

  • pietlinden wrote:

    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

  • 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')

     

    Attachments:
    You must be logged in to view attached files.
  • pietlinden wrote:

    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.

  • This was removed by the editor as SPAM

  • run Chekpoint on database and try take a log backup if log smaller or take full backup then shrink the log file.

  • 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