Agresso blob size collector
Job details
| Name: | Agresso blob size collector | |
| Platform: | Sqlserver | |
| Category: | Agresso | |
| Description: | Collects information about the size of blobs in agresso databases. | |
| Long description: | Collects information about the size of blobs in agresso databases. | |
| Version: | 2.1 | |
| Default schedule: | 15 6 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 |
|---|---|---|
| min blob size | 0 | The threshold size for blob data that must be exceeded before statistics are gathered in the history table. |
| max blobs | 5 | The highest count of blobs allowed per database to gather statistics in the history table. |
| database name | agr% | Name of the database where the Agresso application is installed. You can specify a list of databases (separated by comma). You can use % (percent sign) to represent wild card characters. |
| max objects per graph | 5 | The maximum number of objects visualized in the report graphs. |
Job Summary
The dbWatch job “Agresso blob size collector” is designed to monitor and manage large binary objects (BLOBs) within Agresso databases.
- Purpose: The purpose of this job is to collect, track, and analyze the size of BLOB data in Agresso databases to help in efficient database management and ensure optimal performance.
- Why: Monitoring the size of BLOBs is crucial for:
- Preventing database overgrowth
- Ensuring that resources are utilized efficiently
- Maintaining system performance and responsiveness
- Manual Checking: To manually check the BLOB sizes in the database, administrators can execute the following SQL command:
select db_name "Database", tab_name "Table name", data_type "Data type", size_mb "Space usage (MB)", histr_date "Date collected" from agresso_blob_size order by 1,2,4;Implementation Details
This job works by implementing several SQL tables and procedures to manage the data collection from the Agresso databases. Here are the primary components involved:
- “agresso_blob_size” – This table is created to store current BLOB sizes.
- “agresso_blob_size_histr” – This table holds historical data about BLOB sizes.
- “agresso_blob_db_info” – Stores information about databases that have been checked.
SQL codes are used to create these structures and gather data periodically according to a defined schedule.
Dependencies and Parameters
The job operates with multiple dependencies that ensure data integrity and error handling:
- Historical tables such as “agresso_blob_size” and “agresso_blob_size_histr” are crucial for tracking data over time.
- A cleanup process is incorporated to manage failures effectively, ensuring stability and reliability of the monitoring process.
Parameters specified in the job setup include constraints like the minimum BLOB size to track and the number of top BLOBs to monitor, which allows customization according to specific database needs.
Presentation and Reporting
The dbWatch job provides a detailed reporting interface that assists in visualizing:
- Current space usage by BLOBs
- Historical data on BLOB size variations
- Comprehensive list of all databases checked with their respective BLOB sizes
Presentations are designed to be clear and informative, making it easier for database administrators to make data-driven decisions.
| Database | Table Name | Data Type | Space Usage (MB) | Date Collected |
|---|---|---|---|---|
| db_example1 | table_blob_1 | image | 150 | July 10, 2023 |
| db_example2 | table_blob_2 | varbinary(max) | 220 | July 10, 2023 |
This tabulated format in reports allows for straightforward assessments of data patterns and critical insights.
Advanced Features
- Automated data handling procedures are set to operate seamlessly with the SQL Server environment, particularly tailored for versions above 2012.
- Jobs are configured to ensure robust data collection even in complex setups such as databases involved in AlwaysOn groups.
In summary, the “Agresso blob size collector” job is a comprehensive tool for managing large binary data in Agresso databases, crucial for maintaining optimal database health and performance.