SQL Server Database Growth Under Business Central 14 / NAV 2018

MJV

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

skiddoo

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

Developer101

Interesting...didn't know this. Thanks for sharing