SQL Server Database Growth Under Business Central 14 / NAV 2018

MJVMJV Member Posts: 35
Hi, We converted our sql server database to 14.0.3391.2, then we did a NAV upgrade from NAV 2016 to BC14/NAV 2018.

Now, our database size is increasing much faster than it did before.

In addition, our differential backup is increasing at 5 or 10 times the rate it did before.

Has anyone noted this condition?

Does BC14 tend to generate more transactions than previous versions of NAV?

Our business volume has not increased, so I am at a loss to explain this.

Thanks,

Michael

Comments

  • skiddooskiddoo Member Posts: 16
    Hey Michael,

    It might be the Change Log. Previous versions of NAV logged only changes made by users on Pages. But then Microsoft changed it so that even modifications made by code are logged now.
    If you have the change log enabled on tables that are quite often used in your processes, the change log will grow rapidly.

    You can use the following query to check the biggest tables in your database. I suggest running it periodically (once per week) and save the output in a separate table to see which tables are growing.
    SELECT top 30
        t.NAME AS TableName,
        s.Name AS SchemaName,
        p.rows,
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceGB,
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB, 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255 
    GROUP BY 
        t.Name, s.Name, p.Rows
    ORDER BY 
        TotalSpaceGB DESC, t.Name
    

    Regards
    Ben
  • Developer101Developer101 Member Posts: 453
    Interesting...didn't know this. Thanks for sharing
    United Kingdom
Sign In or Register to comment.