Questions about the Database growth in Sterling B2B Integrator are very common.
Possible growth causes include: housekeeping jobs problems, mis-configuration, Database performance problems or caused by the way the product is used like processing so many messages or keeping the data for a very long in the Database.
The first place I would look at, is the DBStats report. This report can be generated from: Operations-> Reports in the Sterling B2B Integrator UI. Choose the PDF format for ease of readability.
Note: If The DBStats report takes to long to generate, you can schedule the report to run in a BP and then send it or drop it off somewhere. Go to Operations>Reports then choose Schedule report and select DBStats. You can specify a folder on filesystem or an Email address to sent to (If the application is configured to send emails). Select Do Not Run on a Schedule but rather run manually on demand.
This report contains a lot of useful information about what is stored in the database, when the tables will be purged and whether there are tables growing unexpectedly.
The DBStats report contains only 4 sections. I will explain what to look for under each of them with examples.
DBStats report- Section 1: Top Executed Business Processes
This first section will show all of the executed BPs in the Database ordered by the number of execution. So this section will give information about which BPs are writing (persisting) a lot of data and holding space.
What to look for?
- Look at the minimum and maximum dates. If the minimum date is very old, this means that either there is a problem with housekeeping jobs or that the BP is configured with a long lifespan or high persistence. See the difference of 9 months between the min and max dates in the Example above.
- Look for BPs with large Total persisted records. In the example below the highlighted BP persist 50k + step per execution. BPs with large number of steps can cause performance problems especially with MS SQL Server and will hold a lot of space. Check this post for best practices if you are using MS SQL Server.
This first section will point us to potential issue such as: very old BPs in the systems, BPs with large number of steps. Usually we would dig deeper with SQL queries to understand further.
DBStats report -Section 2: Archive Status report
The second section called Archive Status Report: will give you the counts, Max and min dates for different records statuses : BPs , Documents, visibility and DMI tracking. If the Eligible counts are too high this means that one of the housekeeping processes is not running correctly or not efficient enough to remove or flag the eligible records.
What to look for?
- High Index, Archive, Purge Eligible counts.
- Minimum date very old.
DBStats report – Section 3: Table Statistics
The Table statistics section will give you the top tables in terms of numbers of records. It will also show the data size, index size and when was the table last analysed during the Database statistics gathering Job.
What to look for?
- Last analysed very old for important tables. Optimizer Statistics are crucial and should be recent.
- Tables with large number of rows.
- Tables with big data size.
DBStats report – Section 4: Unassociated Document Report:
The last section will give you the Unassociated document report, look if the unassociated document count is high. This usually indicates a problem with associateBPsToDocs cleanup process.
What to look for?
Look for high counts. In the example above the count for unassociated Documents, which are mailbox messages, is high. Possibly mailbox deletes jobs are not running or the AssociateBPsToDocs has some backlog.
Read more:
Housekeeping jobs in Sterling B2B Integrator explained.
3 Pingbacks