Views:

Applies to Product - Dynamics 365 Commerce

What’s happening?
Need help to delete the scheme from the offline database to reduce the size of the database, specifically regarding expired discounts that are enabled in the system.
 

Reason:
The customer wants to delete expired discounts which are enabled in the system, as they are consuming a significant amount of data.
 

Resolution:
To remove expired discounts, follow these steps:
1. Enable the feature that excludes expired records in the CDX package:
       - Go to shared parameters in the Configuration parameters.
        - Add the following:
        - Name: CDX_ENABLE_DATEFILTER
        - Value: 1
       - Note: This will only filter new records from the headquarters (HQ) and will not delete expired records in the offline database.
2. Cleanup irrelevant data. This functionality is enabled through Commerce scheduler parameters form.

  1. This functionality will run the stored procedure crt.StripMasterData in the offline database after data sync.
  2. This feature will apply to both channel databases and offline databases, so be sure to test it in a sandbox environment before enabling it in production.
3. Manual Investigation:
  1. To get more information about which tables are consuming the most space, use SQL Server Management Studio (SSMS) to generate a report on Disk Usage By Top Tables.
  2. Alternatively, you can run the following SQL script: sql SELECT s.[Name] AS SchemaName, t.[Name] AS TableName, p.[rows] AS [RowCount], SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id 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 WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND s.[Name] IN ('ax', 'crt') AND i.index_id > 0 GROUP BY s.[Name], t.[Name], p.[Rows] ORDER BY 5 DESC
  3. Manually enabling index compression (if necessary):
  4. If needed, enable index compression for specific tables to optimize space usage.
  5. Reduce FTS catalog size.  The FTS catalog can grow significantly in size. To reduce it, run: sql ALTER FULLTEXT CATALOG COMMERCEFULLTEXTCATALOG REORGANIZE
  6. This should usually be followed by: sql DBCC SHRINKDATABASE (RetailOfflineDatabase)