dbWatch engine alert
Job details
| Name: | dbWatch engine alert | |
| Platform: | Sqlserver | |
| Category: | Internal Internal |
|
| Description: | Creates tables that can be used to simulate blocked sessions in the database. | |
| Long description: | This alert returns warning/alarm if there are several dbWatch engines installed on the same instance. | |
| Version: | 1 | |
| Default schedule: | 90m | |
| Requires engine install: | No | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’) ] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore_databases | NO | List of databases (separated by comma) which will be ignored. |
| multi_engine_status | NO | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when there are more than one dbWatch engine installed. |
| active_engine_status | NO | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when there are more than one active dbWatch engines (tasks executed within last 30 minutes), or if the dbWatch engine is not active (tasks not executed within last 30 minutes). |
Job Summary
- Purpose: The purpose of this job is to monitor and alert if there are multiple dbWatch engines installed on the same SQL Server or Azure SQL Managed Instance. It ensures only the necessary engines are active, which is crucial for maintaining optimal monitoring and management performance.
- Why: Monitoring the number of dbWatch engines is essential:
- To prevent potential conflicts or resource contention caused by multiple engines.
- To ensure clear and unambiguous monitoring data by maintaining only active necessary engines.
- Manual checking: You can manually check the status and details of dbWatch engines in the database by running specific SQL commands as shown below:
SELECT name FROM master..sysdatabases
WHERE CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'Updateability')) = 'READ_WRITE'
AND CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'UserAccess')) = 'MULTI_USER'
AND name NOT IN ('tempdb', 'msdb', 'master', 'model')
AND CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'Status')) = 'ONLINE'You would then need to further explore the ‘DBW_TASKS‘ table within each relevant database to check for engine creation dates and last run details.
Job Implementation Details
The dbWatch engine alert monitors instances based on several criteria:
- SQL Server version greater than 2000
- Instance has engine privileges
- Database types are either Microsoft SQL Server or Azure SQL Managed Instance
Key Metrics Monitored
- “dbw_engine_count”: Count of dbWatch engines installed.
- “dbw_active_engines”: Count of actively running dbWatch engines.
- “dbw_engines_info”: Information about each dbWatch engine, including creation date and last activity timestamp.
Output Details
The alert can output in several states:
- ALARM if more than one dbWatch engine is installed or active
- WARNING if there are multiple active dbWatch engines or no active dbWatch engines
- OK if there is exactly one active dbWatch engine
Scheduled Execution
This job runs periodically every 90 minutes with a timeout setting of 120 seconds, giving sufficient intervals to check and react to changes without causing excessive load.
Reporting
Reports Generated
Table reports provide the following insights:
- “Instance” details which instance the dbWatch engines belong to.
- “dbWatch engines count” shows total number of dbWatch engines.
- “Active dbWatch engines” indicates how many of these engines are currently active.
- “Details” provides creation and last active times of dbWatch schemas within each database.
Reports include visual representations such as tables to make the data clear and actionable. Updates are triggered by the default schedule ensuring data is up-to-date for operational needs.