SQL Server Database Growth Under Business Central 14 / NAV 2018

Comments

• Options

  skiddoo Member Posts: 19

  2021-06-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

  0
• Options

  Developer101 Member Posts: 528

  2021-06-16

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

  United Kingdom

  0