Open Forum

Expand all | Collapse all

Log file increasing between 10 to 20% in a daily basis

  • 1.  Log file increasing between 10 to 20% in a daily basis

    SILVER CONTRIBUTOR
    Posted 10 days ago
    I currently have Business Central (On-Prem) Version 13.3
    Version US Dynamics NAV 13.3, LINKFRESH 365 Business Central (7.10)
    SQL 2015 Standard

    I am facing an issue with my log database. It started increasing on rates between 10 to 20% in a daily basis.

    It went from 16 GC to 248 in 2 weeks. I did not notice the issue first till I ran of space. Then I added more resources to the VM but after 2 times in a row I ficured out something is broken.

    I want to perform a Shrink on my data first. And then a secnod Shrink on the log file.

    Any commnts of ideas?




    ------------------------------
    Juan F Martinez
    Wholesum Family Farms Inc.
    Nogales AZ
    ------------------------------


  • 2.  RE: Log file increasing between 10 to 20% in a daily basis

    NAVUG ALL STAR
    Posted 8 days ago
    Juan,

    I am not a technical person but I once read an article about Log Files:

    https://thedynamicsexplorer.com/2019/12/11/why-did-my-microsoft-sql-transaction-log-grow-so-big-and-how-do-i-shrink-it-and-reclaim-the-space/

    Hope this helps.
    Thanks,
    Steve

    ------------------------------
    Steven Chinsky
    Manager
    Wipfli
    Mansfield MA
    NAVUG/BC Programming Committee Member
    NAVUG All-Star, Granite Award Recipient, MCP, DCMP
    ------------------------------



  • 3.  RE: Log file increasing between 10 to 20% in a daily basis

    TOP CONTRIBUTOR
    Posted 7 days ago
    You should be running nightly maintenance/backup on your SQL database. This won't change the additions to the log, but will empty it each night so that it won't grow in physical size on the disk so rapidly.



    Sent from my T-Mobile 4G LTE Device






  • 4.  RE: Log file increasing between 10 to 20% in a daily basis

    Posted 6 days ago
    If your log is increasing and never freeing up space, then that would indicate that the backup is not flushing the log properly after completion.  You can check the log file usage in SSMS by right-clicking the database and choosing to Shrink files, and look at the Log percentage free.  If you have a log that is pretty much full, you either have some jobs that are continually running and never committing, or it's not being flushed.

    While you can shrink the database and log files, you really have to do some root cause analysis on WHY it's doing this.

    ------------------------------
    Torolf Haug
    Mchutchison
    Wayne NJ
    ------------------------------



  • 5.  RE: Log file increasing between 10 to 20% in a daily basis

    Posted 6 days ago
    Run this against the database as well in SSMS.

    SELECT 
        [TYPE] = A.TYPE_DESC
        ,[FILE_Name] = A.name
        ,[FILEGROUP_NAME] = fg.name
        ,[File_Location] = A.PHYSICAL_NAME
        ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
        ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
        ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
        ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
        ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
            WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
            + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
                ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
            + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
    FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
    order by A.TYPE desc, A.NAME; ​


    ------------------------------
    Torolf Haug
    Mchutchison
    Wayne NJ
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role