Full Database Cleanup Routine, Part 2

Submitted by Nathan Clouse on 07/19/20.

Last modified:

05/16/21

In part 1, we talk about the process of cleaning. Now we're going to do it.

In part 1 of this series, we talk about how to clean up your database. Now, we're going to run that process and record the changes as they occur and evaluate what those changes mean. We'll still be sticking with weight lifting puns here and there.

What We'll Be Measuring (The Saga of Swoliness)

We'll be measuring the company size, as reported by AX 2012 using the System Administration > Reports > Database > Size of company accounts report. This may not be accurate but it will at least be consistent so while it may not be right, it will be consistently not right. Next, we'll be measuring the database size, overall, as well as the SysDatabaseLog table. This article serves as a good warning that planning is as important as execution. You'll see why near the end. Also, if y

From The Top (Gainesis)

We will use this query to measure database storage used.

SELECT d.NAME
,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
and d.name = 'AX2012DEV'
GROUP BY d.NAME
ORDER BY d.NAME

We are starting at 1,166 GB

We will be focusing on Company AAA, BBB, CCC, and DDD. The process that is outlined below will be reported based on a complete run of the process for that company. Companies AAA, BBB, CCC and DDD are different so their profiles are listed below:

AAA - All buy, make, sell, and retail sale operations in one legal entity with WMS processes. 12261 Purchase Order Lines per year, 497910 Sales Order Lines per year, 16797 PO Invoice lines per year, 491411 Sales Order invoice lines per year, and 569034 Ledger Journal Lines per year.

BBB - Purchases and manufacturing. sold via intercompany to CCC using WMS processes. 2965 Purchase Order Lines per year, 3641 Sales Order Lines per year, 3223 PO Invoice lines per year, 3561 Sales Order invoice lines per year, and 20255 Ledger Journal Lines per year.

CCC - General Ledger consolidation for BBB and DDD. 0 Purchase Order Lines per year, 0 Sales Order Lines per year, 0 PO Invoice lines per year, 0 Sales Order invoice lines per year, and 33455 Ledger Journal Lines per year.

DDD - Sales and Retail sales using WMS processes. 10170 Purchase Order Lines per year, 782041 Sales Order Lines per year, 10500 PO Invoice lines per year, 765551 Sales Order invoice lines per year, and 459014 Ledger Journal Lines per year.

The reported size for each company from the "Size of company accounts" report in System Administration > Reports > Database > Size of company accounts are as follows in bytes converted to GB in brackets:

AAA - 180,570,962,188 [180.57 GB]

BBB - 20,446,510,214 [20.45 GB]

CCC - 801,608,356 [0.80 GB]

DDD - 216,878,323,824 [216.88 GB]

So, just using math, we can see that all other legal entities plus data not specific to a company take up approximately 775.96 GB in misc storage. There are 13 other legal entities but all were less than .5 GB so they aren't being included in this exercise or for analysis. I don't think the report takes into account index space usage so the above metrics may be data only. However, we'll be using the same method from the start to end so we can still collect some valuable data even if only using data usage and not data + index usage.

General Ledger

First, we're going to clean up our ledger journals. For this exercise, we will be selecting all options for our 4 legal entities.

After running this for all companies, we had the following gains

AAA - 168,915,781,435

BBB - 19,817,655,620

CCC - 149,085,672

DDD - 208,504,771,356

And our database is reporting this:

If you'll notice, we actually increase storage space usage, rather than reduce it. Once thing we didn't take a look at yet is our database logs ( System Administration > Setup > Database > Database log setup ). You'll want to review this periodically during normal operation and remove items that are no longer needed or no longer provide value. In this instance, all journals are logged for delete so all items we deleted, we created a log entry recording before and after state, nearly doubling storage used because we took each journal line, deleted it, then created a log with the before state (what it was before delete) and an empty record to record the after state that shows it was deleted. We'll address this later. We'll leave the database logs in this instance in place and report on the changes. I'll now be recording the SysDatabase Log table size also from now on. It is now 9,038.453 MB.

Procurement and sourcing

Next, we're going to move onto Procurement and Sourcing. There are 2 cleanup routines available for both AX 2012 and Dynamics 365 for Finance and Supply Chain. Depending on your legal entities and what functionality they use, the request for quotations functionality may not offer much value in terms of storage usage savings. We'll start with the purchase order history cleanup. After we run this, the companies are reporting these values.

AAA - 167,754,666,141

BBB - 19,469,570,140

CCC - 148,537,154

DDD - 207,416,529,761

Next, we run the Delete Requests for Quotations routine and our companies are reporting these values.

AAA - 167,754,665,244

BBB - 19,469,570,140

CCC - 148,537,154

DDD - 207,416,529,761

After those 2 routines, the database is reporting:

the Database Log table is reporting:

Production control

Next, we'll clean up Production Control. In this instance, no real MES / JMG transactions take place so we won't see a lot of movement there but we selected the "Delete" option whenever presented. First, we'll start with cleaning up time registrations. Once we ran those, these are the reported values for the company accounts sizes.

AAA - 167,754,665,244

BBB - 19,469,570,140

CCC - 148,537,154

DDD - 207,416,529,761

Next, we'll clean up production order journals. This will cleaned up all production order journals based on the production order status. Once we ran those, these are the reported values for the company accounts sizes.

AAA - 168,258,209,855

BBB - 20,902,978,842

CCC - 148,537,154

DDD - 207,416,529,761

Now that we've cleaned up the journals related to production orders, we're going to clean up the production order. This will remove production orders based on some filters. Once we ran those, these are the reported values for the company accounts sizes.

AAA - 168,020,460,167

BBB - 20,044,964,353

CCC - 148,537,154

DDD - 207,416,529,761

After these routines, the database is reporting:

the Database Log table is reporting:

Sales and Marketing

Next, we'll clean up the sales update history.
http://www.atomicax.com/article/clean-sales-update-history

AAA - 161,255,497,104

BBB - 19,930,648,749

CCC - 148,537,154

DDD - 198,288,727,759

Then Sales Orders.http://www.atomicax.com/article/clean-your-sales-orders

AAA - 161,269,469,569

BBB - 19,940,871,426

CCC - 148,537,154

DDD - 198,277,438,339

Then return orders. http://www.atomicax.com/article/clean-return-orders

AAA - 161,665,590,590

BBB - 19,940,871,426

CCC - 148,537,154

DDD - 199,514,940,537

Then Sales Quotations. http://www.atomicax.com/article/clean-sales-quotations

AAA - 161,665,590,590

BBB - 19,940,871,426

CCC - 148,537,154

DDD - 199,514,940,537

Warehouse Management

http://www.atomicax.com/article/work-creation-history-clean

AAA - 161,322,229,706

BBB - 19,796.513,694

CCC - 148,537,154

DDD - 196,031,883,877

http://www.atomicax.com/article/cycle-count-plan-clean

AAA - 161,322,009,371

BBB - 19,796,513,694

CCC - 148,537,154

DDD - 196,031,883,877

http://www.atomicax.com/article/wave-batch-cleanup

AAA - 161,322,229,706

BBB - 19,796,513,694

CCC - 148,537,154

DDD - 196,031,870,742

http://www.atomicax.com/article/mobile-device-activity-log-cleanup

AAA - 161,322,009,371

BBB - 19,796,512,214

CCC - 148,537,154

DDD - 196,031,870,742

http://www.atomicax.com/article/warehouse-containerization-history-purge

AAA - 161,322,009,371

BBB - 19,796,512,214

CCC - 148,537,154

DDD - 196,031,870,742

http://www.atomicax.com/article/work-user-session-log-cleanup

AAA - 161,322,009,371

BBB - 19,796,512,214

CCC - 148,537,154

DDD - 196.031,870,742

After these routines, the Database Log table (SysDatabaseLog) is now

Inventory Management

First, Load adjustments.

AAA - 161,322,123,700

BBB - 19,796,512,214

CCC - 148,537,154

DDD - 196,031,870,742

Next, Inventory Journals.

AAA - 160,605,144,787

BBB - 19,609,112,222

CCC - 148,537,154

DDD - 195,570,204,443

Next, cost calculations details.

AAA - 160,145,470,539

BBB - 18,739,068,233

CCC - 148,537,154

DDD - 195,570,119,006

Next, Inventory Settlements. We Took the default date generated for this. it is today less 1 year moved to the first day of the next month ( ie, 5/1/2019 when today is 5/18/2020 )

AAA - 160,145,470,539

BBB - 18,739,068,233

CCC - 148,537,154

DDD - 195,570,119,006

Next, aggregate on hand by financial dimensions.

AAA - 160,145,609,917

BBB - 18,740,567,341

CCC - 148,537,154

DDD - 195,572,250,082

Next, on hand entry cleanup.

AAA - 160,145,609,917

BBB - 18,740,567,341

CCC - 148,537,154

DDD - 193,563,292,108

Next, WMS on hand entries cleanup.

AAA - 160,145,609,917

BBB - 18,424,737,657

CCC - 148,537,154

DDD - 193,036,095,767

Next, cleanup any dimension inconsistencies. This is more than likely optional. It only applies if you are using the inventory site link.

AAA - 160,145,609,917

BBB - 18,424,737,657

CCC - 148,537,154

DDD - 193,036,095,767

Finally, Inventory Dimension Cleanup.

AAA - 158,923,312,213

BBB - 15,478,878,507

CCC - 148,537,154

DDD - 185,498,079,822

After those routines, the database is reporting:

the Database Log table is reporting:

System Administration

Two areas in AX 2012 that are not easy to keep track of are management of AIF and Batch logging systems as well as the database log. I wrote 2 items to help automate some of this. The following processes are custom and require custom code but they can be helpful in removing lots of old logs.

AIF and Batch Cleanup

This will help cleanup various messages that have accumulated over the years in the AIF and Batch history tables.

http://www.atomicax.com/content/ax-2012-aif-and-batch-cleanup-automation

I will be running this tool using the defaults but since this data is shared, so I'll be reporting the before and after sizes for the tables involved. The database I am using has this cleanup running routinely so you can more than likely expect much larger in your database.

Before:

I took the defaults for this, as shown.

After:

Database Log Cleanup

I'll be using the tool I wrote to clean up this table using the defaults highlighted in this article: http://www.atomicax.com/content/ax-2012-database-log-cleanup-automation. Again, the database I am using has this routine run at least annually so your values, if left unattended for years, yours may be much larger.

We started at (12,683.9 MB):

And ended at (10,621.8 MB):

Our database size is now

Retail

I also wrote a custom cleanup routine for the chatter type messages for retail that can accumulate over time. You can find it here: http://www.atomicax.com/blog-entry/ax-2012-retail-download-and-upload-session-cleaner.

Before we started:

After it finished for all companies:

Lessons Learn, So Far

What I learned from this process is just how important it is to schedule this stuff as part of your normal maintenance, whether it's yearly or just whenever you find time. If you are looking to upgrade, keeping your data clean and as small as you can will be critical. Additionally, keep an eye on your database logs. Go to System Administration > Setup > Database > Database Log Setup to view the logs your system currently has. This list should be kept to essential logs only and not be a catch all for anything / everything, like so: