Custom Alerts

This custom alert will tell you when a database job is stopped. The query checks the job's enabled flag and if it's 0, an alert will be triggered based on the thresholds. Note: To filter the results, i.e. ignore a specific job, add a "WHERE name NOT IN ('<list of jobs to exclude>')" clause to the query. To create this alert, click into Alert and create a Custom Multiple Numeric SQL Alert and configure it similarly to this:

This custom alert executes the df -k command on a Linux/Unix system to retrieve disk space usage information. It parses the output to extract relevant data for each drive, such as the total and available space. The alert then calculates the percentage of free disk space for each drive and sends the results to a DPA for comparisons to the defined thresholds. To set this up, create the function on each instance and an alert of the type Custom SQL Multiple Numeric . Paste the provided SQL script ...

This alert monitors each active archive log destination and will tell you when any of the destinations are having problems, i.e. the status is anything other than 'VALID'. To create this alert, create a Custom SQL Multiple Numeric Alert and use the attached query for the SQL Statement. Here is an example of an alert with thresholds:

This alert will return a list of objects that have recently been modified. It uses the sys.objects table and the modify_date column for detection. This table is specific to each database, so it also loops through all non-system databases to get an instance-wide perspective. To create the alert, create a Custom SQL - Multiple Numeric Return and make is look similar to this example:

Introduction DPA currently offers an alert called Database Instance Wait Time Anomaly , which notifies you when the overall instance wait times exceed normal levels. Some customers have requested a more granular approach, allowing alerts to focus on individual databases within an instance. This new alert is designed to address that need. Note: This alert is compatible with all database types except Oracle, as Oracle does not utilize a database concept similar to SQL Server and others. It leverages...

This alert will take the table size data collected by the MySQL Table Size Collector alert and report on growth. Create a Custom SQL Multiple Numeric Growth alert and make it look similar to this example:

Introduction This alert is designed to gather the sizes of all tables within a MySQL instance. It does not trigger any notifications and serves solely as a mechanism for collecting data, which is then stored in the DPA repository. After the data is collected, you can use a query like the one below to retrieve historical data. Additionally, consider using the custom alert MySQL Table Size Growth , which notifies you when a table's size increases by more than a specified percentage. Query to...

This alert uses the results from MySQL Database Size Collector to calculate the amount of growth for each database. If those value are above the thresholds, you will get an email with the database name and percent growth.

DPA does not collect database sizes out of the box, but we can use a custom alert (that never triggers) as a method for collecting this data. Querying Alert Results to get Database Sizes The data is stored in the DPA alert tables and this information can be retrieved with a query similar to this: DECLARE @CollectorAlertID int, @InstanceID int; -- Change this query to match the name you used for the collector alert name, this is the default SELECT @CollectorAlertID = id FROM ignite.con_alert...

Introduction Table bloat in PostgreSQL refers to the excessive use of disk space within database tables and indexes, typically caused by inefficient storage due to deleted or updated rows. This happens when rows are updated or deleted but the space previously occupied by these rows isn't immediately reclaimed. As a result, the table grows in size, even though the number of rows may not have increased significantly, leading to wasted storage and potential performance degradation. Causes of Table...

This alert will query the pg_database table and use the datallowconn value. If the value is 1, the database is not currently accepting connections and the alert query will return 1, otherwise the query returns 0. To create the alert, create a Custom SQL Multiple Value alert and make it look similar to this example:

This alert will tell you if a Postgres instance is changed to be in read-only mode. To determine the state, this alert queries the pg_settings table for the value of default_transaction_read_only. When this is ON, all sessions will be created in read only mode. To configure this alert, create a Custom SQL - Single Value Alert and make it look similar to this example:

This alert will tell you when a MySQL instance is configured for read only activity based on the read_only global variable.

Introduction Sleeping sessions with open transactions can lead to blocking and deadlocking issues. This is often due to coding problems, such as a stored procedure that fails to handle errors properly and ends without committing or rolling back the transaction. However, there can be other contributing factors as well. Detection The attached script utilizes four DMVs to gather information about sleeping sessions. The query returns a message that includes the SQL Handle of the most recently executed...

Introduction To detect block corruption within Oracle, RMAN is the tool I have typically used to do this. While performing backups or as part of a VALIDATE command, it can check for both physical and logical corruption. In both cases, it stores the corrupted blocks information in the V$DATABASE_BLOCK_CORRUPTION object. This view can be utilized from a DPA custom alert to check for corruption. Custom Alert Within DPA, create a Custom SQL Multiple Numeric alert, paste in the attached SQL statement...

Introduction This alert will check the status of RMAN backups by looking at the V$RMAN_BACKUP_JOB_DETAILS tables and return any row from the last time the alert ran (based on frequency of the alert) where the status is 'COMPLETED WITH WARNINGS', 'COMPLETED WITH ERRORS', 'FAILED'. Alert Definition To create this alert, click on Alerts > Manage Alerts tab and create a Custom SQL Alert with Multiple Numeric Return type. Configure the alert similar to below:

Introduction There is a custom query located here that provides a list of all missing indexes in your environment ranked by the time savings: (2) Global Index Advisor with Time Savings - Custom Queries - Database Performance Analyzer - THWACK . Some people have been asking about automating this, and the best way to achieve this in DPA is by using a custom alert that runs once a day or week. Custom Alert Query Use the main query from the Global Index Advisor with Time Savings link above to get...

This custom alert utilizes the DM_DB_INDEX_PHYSICAL_STATS DMV to retrieve fragmentation information for all indexes within a database. Since this DMV only provides data specific to the current database, the script uses the sp_MSforeachdb procedure to collect information from all databases. Note : the first script does not work for Azure SQL Databases because the sp_MSforeachdb procedure does not exist. However, the script contains another query at the bottom that can be used for Azure SQL Databases...

If you want to alert on a specific query executing N% more than the avg, here is a start to get you going. Go to Options -> DB Query Tool -> run SELECT ID, NAME from COND -- to get the id of the instance you want to create an alert for In DPA, go to Alerts -> Manage Alerts -> Custom of Multiple Numeric Return (Create Alert) Name the alert something appropriate, choose 1 hour as the interval (since we are going after summarized data to be efficient), then select the instance to watch for a specific...

Introduction Oracle Automatic Storage Management (ASM) is a storage management solution integrated with Oracle databases, designed to simplify and optimize the management of disk storage. ASM eliminates the need for traditional file systems by directly managing disk groups and distributing data across all available storage for balanced performance and redundancy. It provides features like data striping, mirroring, and dynamic storage resizing, ensuring high availability and optimized I/O performance...