Why is my database so big? Understanding the DBStats report and what it can do for you. (Part 3 Minimum and Maximum Start Dates)

Body

In this post I want to look at the Minimum and Maximum Start Dates in the Top Executed Processes section of the DBStats report.

The column names are descriptive. For each process we list the timestamp for the earliest start time of a run and the most recent start time of a run.

So, for example:

In this report, we can see the earliest run of Schedule_JPMTestFSA was on 1/5/15 at 3:55PM and the most recent run was on 1/20/15 at 3:53PM.

What can the Minimum Start Date tell us?

In the above example it tells us when the earliest run of Schedule_JPMTestFSA stated. It doesn't really tell us much more. It could be Schedule_JPMTestFSA is running on a schedule every 3 minutes and expires after 15 days. It could also mean that there is a run of Schedule_JPMTestFSA that ran on 1/5/15 which isn't leaving the system for some reason.

One way to get more data is to run the DBStats report again. Say we run it a 24 hours later and get the result:

Now what we see is the Minimum State Date for Schedule_JPM_TEST_FSA has moved forward about 24 hours. In fact, all of the dates have moved forward about 24 hours. This tells us is that (probably) the first two processes, Schedule_JPM_TestFSA and Recover.bpml, have a 15 day lifespan and the other three processes have a 1 day lifespan. Also, purge is working as expected and data is deleting when it expires.

Another example.

We see here that most processes look like they have a 5 day lifespan. The process JPM_ROUTE_B looks different however. We see that there are a much larger number of executions and the Minimum State Date is more than a year in the past. Next we should search for JPM_ROUTE_B in the B2B Sterling B2B Integrator Dashboard on 12/18/13 and look to see if we can figure out why the run is still. This might be because the lifespan of the process is set to 400 days. It also might mean these processes are tied to mailbox messages that have not been deleted, preventing the processes from being deleted.

Here is one more example:

This shows us that there is a single run of the process JPM_ROUTE_F with over 10,000 steps. It ran on 12/18/13 and for some reason has not purged. In this case we should go and look at the runs of the process on 12/18/13 and see if we can figure out what is going on. Like the earlier example, this might be a mailbox message blocking the process from deleting.

The Minimum and Maximum Start Dates can point us to potential issues. We cannot see why the data is still there. We can make some guesses. Mainly what we can get is a place to look for an issue.