Agresso help and temp tables
Job details
| Name: | Agresso help and temp tables | |
| Platform: | Sqlserver | |
| Category: | Agresso | |
| Description: | Collects information about objects (temporary tables and help tables) created in the agresso application schema. | |
| Long description: | Collects information about objects (temporary tables and help tables) created in the agresso application schema. | |
| Version: | 1.31 | |
| Default schedule: | 30 6 2,6 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| database name | agr% | The name of the agresso database. You can specify a list of databases (separated by comma). You can use % (percent sign) to represent wild card characters. |
| Help tables prefix | agr% | Prefix used by the Agresso help tables created when running Agresso reports. |
| Temporary tables prefix | H% | Prefix used by the temporary tables in the Agresso schema. |
| warning threshold — total size | 1000 | Warning threshold, in MB, for aggregated size value for all help and tmp tables. |
| warning threshold — total count | 1000 | Warning threshold for total count value of all help and tmp tables. |
| history threshold | 366 | The number of days the statistics will be kept in the history table. |
| max databases per graph | 5 | The maximum number of databases visualized in the report graphs. |
Job Summary
- Purpose: The monitoring job is designed to track and manage the creation and usage of temporary and help tables within Agresso application schema databases.
- Why: This monitoring is critical for maintaining database efficiency and handling. It ensures that too many or oversized temporary/help tables do not clog the system, which could otherwise lead to degraded performance or increased storage costs.
- Manual Checking: You can manually check the status of these tables in the database using the SQL query provided in the implementation section of the job’s XML configuration.
Job Description
- This dbWatch job collects and reports on the size and count of temporary and help tables in Agresso application schemas.
- The job checks for databases that exceed set thresholds for table count and size, which triggers a warning or alert.
- A history of table counts is maintained to monitor and analyze trends over time, which is crucial for historical data analysis and capacity planning.
Key Features
- The job periodically collects data on help and temporary table metrics such as their count and size.
- Provides a report on current statistics and history trends of table usage.
- Automatically handles data clean-up in case of job failures to maintain data integrity.
Technical Implementation
- The implementation scripts build and utilize two main tables: ‘agr_help_tabs_schemas_curr’ for current database state and ‘agr_help_tabs_schemas_histr’ for historical data tracking.
- SQL CRUD operations are performed as part of the job to update records based on the current state of the databases, considering Agresso specific prefixes and set thresholds.
- Error handling is effectively managed using TRY-CATCH blocks in SQL to ensure that any execution errors are caught and logged appropriately.
Outputs and Reports
- “Agresso Help and Temporary tables space usage” report is generated showing detailed information about the database tables including size and count.
- The report includes visual graphs for quick analysis of the data across different time frames, focusing only on the databases with the highest table counts.
Dependencies and Parameters
Specified dependencies for this job include:
| Description | Object | Cleanup Required |
|---|---|---|
| Self Dependency | agr_help_tabs_check | Yes |
| Current statistics | agr_help_tabs_schemas_curr | Yes |
| Historical data table | agr_help_tabs_schemas_histr | Yes |
Parameters configured for threshold management include:
- Total size warning threshold
- Total count warning threshold
- History retention period
- Max databases per graph for reporting
Version and Compatibility
- The current version of this job is 1.31.
- It is designed to be compatible with Microsoft SQL Server and Azure SQL Managed Instance, particularly version newer than 2012.
Implementing and monitoring such jobs is crucial for database administrators to ensure optimal performance and to avoid potential issues related to database size and schema complexity. This job provides a useful blend of automation and manual intervention points for effective database management.