Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.
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. **
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.
Clean up job:Cost Management > Manufacturing accounting > Clean up > Clean up the costing sheet cache
DMFStagingValidationLogDMFStagingExecutionErrorsDMFStagingLogDetailDMFStagingLogDMFDefinitionGroupExecutionHistoryDMFExecutionDMFDefinitionGroupExecution 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”.
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.
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’)
Clean up job: Procurement and sourcing > Periodic tasks > Clean up > Purchase update history clean up
Clean up job:Sales and marketing > Periodic tasks > Clean up > Sales update history cleanup
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
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
Clean up Job: Warehouse management > Periodic tasks > Clean up > Containerization history purge
Clean up job: Inventory management > Periodic tasks > Clean up > Calculation of location load
Clean up job: Warehouse management > Periodic tasks > Clean up > Mobile device activity log cleanup
Clean up job: Warehouse management > Periodic tasks > Clean up > Work creation history purge
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.
Business Applications communities