F&O Data Management: Reviewing Large Tables and Database Growth

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. **

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.