In this blog we'll look at keeping database growth in check. Over time, it is normal for the database to grow as transactions occur. However, it is still recommended practice to monitor the overall size of your database to understand which tables are growing. In some cases, there are also clean up jobs that come with the product that can help. This blog assumes you have access to a sandbox environment with a recent copy of your database from production.

Unfortunately, a report in LCS doesn’t exist. But you can easily view this in a sandbox environment from the reports available in SQL Server Management Studio (SSMS) using the steps below.

  1. Connect to your sandbox database using SQL Server Management Studio (SSMS). You can use my blog here on how to request just-in-time database access to the AXDB database and set up the connection in SSMS. The database you are requesting access to in this blog is the same database that we will be working with in the next steps.
  2. After connecting to the database from the first step, expand the Databases
  3. Right click the AXDB database you requested just-in-time (JIT) database credentials for and select Reports > Standard Reports > Disk Usage by Top Tables

 

This is an out-of-the-box  report for SQL Server that shows the largest tables in a database and is sorted by the ‘Reserved (KB)’ column. You can also accomplish this from direct SQL queries as well, but for the purposes of this blog we’ll use the report. You will see a similar report like below after it loads:



The columns on this report are:

Table Name: The name of the table in the database.
# Records:  The total current number of records in the table.
Reserved (KB): The total reserved disk space in kilobytes. This includes space used by data, indexes, and unused space. The report is sorted by this column by default.
Data (KB): Total data written in kilobytes.
Indexes (KB): Total space used in kilobytes by indexes on this table.
Unused (KB): Allocated space that has not been used.

I recommend focusing on the top 10 or 20 tables. These sizes will vary between customers for various reasons and it is normal to see large transactional tables in this list as it is the nature of the application. However, in some cases tables show up on this list that are growing indefinitely due to customizations, misconfiguration, or clean up jobs not being ran. If you are seeing custom or ISV tables please work with the developer or ISV to determine if it should or can be cleaned up. 

Below are some common tables I see during Performance Reviews. This list also includes tables from the cleanup job documentation where associated tables are specifically called out in the descriptions but it is not an exhaustive list and you should reference the linked documentation for a full listing of clean up jobs. If you are regularly seeing any of these in your largest table report, it’s recommended to look at why and if the table growth needs to be remediated. If index size is taking a lot of space on the table, those indexes should be reviewed to make sure they are being used and are optimal. Please see the notes for any remediation advice.

** Be sure to work with your business to establish cleanup\retention policies and ensure that any data removed is no longer needed. Always test everything before use in Production. **

Table Name

Notes

BatchConstraintsHistory
BatchHistory
BatchJobHistory

Clean up jobs:
System administration > Periodic > Batch job history clean-up

System administration > Periodic tasks > Batch job history clean-up (custom) 

The first one does not use filtering and offers the best performance. The second clean up job that says “custom” can be used with filtering when you want to delete specific history using criteria such as execution status, job description, company, etc.

 

CostSheetCache

Clean up job:
Cost Management > Manufacturing accounting > Clean up > Clean up the costing sheet cache

DMFStagingValidationLog
DMFStagingExecutionErrors
DMFStagingLogDetail
DMFStagingLog
DMFDefinitionGroupExecutionHistory
DMFExecution
DMFDefinitionGroupExecution
Other DMF Staging Tables

 

Clean up job:
Data management workspace > Job history clean up

Do not use the “staging clean up” option as it has been deprecated. Job history clean up has replaced this and as of PU29 can be enabled in Feature Management. The feature to enable is “Execution history cleanup”.

EventInbox
EventInboxData

Clean up job:
System administration > Periodic tasks > Clean up > Notification clean up


Alerts should be disabled if they are not used by the business as this will help with performance.

InventSumLogTTS

This table has log data added to it if master planning is configured and is eventually cleared by the master planning process. If the master planning process is not being executed, this table will grow indefinitely.

If you run master planning, monitor that this table size is being cleared out after it has ran. If you do not run master planning and this table is continuously growing, you will need to delete the item coverage groups to prevent this and disable master resource planning.

Setup for Coverage Groups:
Master planning > Setup > Coverage > Coverage groups

Setup for Master planning:
Master planning > Setup > Master planning parameters 

(You are looking for ‘Disable all planning processes’)

PurchParmTable

Clean up job:
Procurement and sourcing > Periodic tasks > Clean up > Purchase update history clean up

 

SalesParmTable

 

Clean up job:
Sales and marketing > Periodic tasks > Clean up > Sales update history cleanup

 

smmTransLog

Used by Sales and Marketing transaction logging but does not have a cleanup job. Each setup is company specific so you will need to configure each in your F&O instance. Only set up logging in companies it is needed for. If you need to clear a log, set the configuration for ‘Days to keep log’ for that company to 0.


Setup Location:
Sales and marketing > Setup > Sales and marketing parameters > Transaction log

You can view the log for a company at:
Sales and marketing > Inquiries > Transaction log

 

 

SysDatabaseLog

Clean up job:
System administration > Inquiries > Database > Database Log > Clean up log

Database logging setup can be found here:
System Administration > Setup > Database log setup

 

WHSContainerizationHistory

Clean up Job:
Warehouse management > Periodic tasks > Clean up > Containerization history purge

 

WMSLocationLoad

Clean up job:
Inventory management > Periodic tasks > Clean up > Calculation of location load

 

WHSMobileDeviceActivityLog

Clean up job:
Warehouse management > Periodic tasks > Clean up > Mobile device activity log cleanup

 

WHSWorkCreateHistorytable

Clean up job:
Warehouse management > Periodic tasks > Clean up > Work creation history purge

WHSWorkUserSessionLog

Clean up job:
Warehouse management > Periodic tasks > Clean up > Work user session log cleanup

 


To review the total size of your database, there is also a report for that by right clicking your database in SSMS and going to Reports > Standard Reports > Disk Usage. At the top of the report are Total Space Reserved, Data Files Space Reserved, and Transaction Log Space Reserved. If you want to see the actual used space in your database data file(s), click and expand Disk Space Used by Data Files. The Space Used column will show you the actual space used per data file in your database.


If you are experiencing performance issues today or want to take proactive steps to ensure your environment continues to run optimally, we offer a Performance Review service that you can learn more about here from our service offerings. You will work with an accredited performance engineer from our team to make sure you get the best performance from your F&O investment.

I’d love to hear if you think I’m doing a good job or if this has helped you in some way in the comments below. Thank you for taking the time to read my blog and I hope you reach out on social media to connect. My social media information can be found in my user profile.