Custom Alerts

Note: the query used in this alert, could also be used to create a custom metric as well. This would allow you to chart the metric, see trends and alert based on thresholds. Introduction PostgreSQL Transaction Wraparound is a concept related to how PostgreSQL manages transaction identifiers (XIDs) to ensure data consistency and prevent data corruption. Understanding transaction wraparound involves diving a bit into PostgreSQL's Multi-Version Concurrency Control (MVCC) mechanism, the finite nature...

Fragmentation can be defined many different ways, but for the sake of this alert, it is based on the total size allocated to a table vs the space the actual data consumes. A highly fragmented table can take up extra space that it doesn't need, and when table scans are required to access the data, performance can suffer. This query accesses all tables but excludes SYSTEM and SYS tables. It only returns tables that are larger than 100 MB, so adjust the threshold as needed. It returns a data set that...

This custom alert will query the DBA_OBJECTS system table and return all objects that are currently invalid. The query will return the object information in the first column and a the number of minutes the object has been invalid in the second column. For the thresholds, use 1 as the min value, meaning anything that has been invalid more than 1 minute will cause the alert to be triggered. If you have have objects that become invalid but later will get revalidated, consider setting the threshold to...

This custom alert will query the DBA_OBJECTS system table and return all materialized views that are currently invalid. This query could be modified to return any object or specific objects as needed. The query will return the object information in the first column and a the number of minutes the object has been invalid in the second column. For the thresholds, use 1 as the min value, meaning anything that has been invalid more than 1 minute will cause the alert o be triggered. If you have have...

Introduction DPA includes several metrics around TempDB, but when it has grown to a large size, the question that often comes up is "What's consuming my TempDB database?". DPA does not inherently track that information, but we can use a custom alert (that never alerts) to collect this information. When the alert executes, the results are stored in the DPA alert history tables. The attached query will collect the database name, SQL statement, User MB, Internal MB as the message (first column) and...

When Oracle is configured in ARCHIVELOG mode, once redo logs are filled, they are archived to another location, possibly for a standby database to apply or just for safekeeping. To monitor the archiver and ensure it's working properly, this custom alert will make use of the V$ARCHIVE_DEST view to check for errors. Note that this query only watches for archive destinations with a STATUS='ERROR', but there may be additional status you want to check for as well. Here is a link to Oracle documentation...

Description The current alert for Oracle Tablespace Freespace has limitations and I find this simple custom alert to work a bit better. It uses the system view named DBA_TABLESPACE_USAGE_METRICS (Oracle 11.2+) to calculate the free space remaining in all tablespaces. It includes both data and temp tablespaces as well and takes autoextend data files into account. Alert Definition To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple...

To watch for blocking that comes from a specific machine in your environment, this alert can help do that. To create it, create a Custom SQL - Single Numeric alert and make it look similar to this example. Paste in the contents of the attached script into the SQL Statement text box. The following placeholders are automatically passed into the SQL statement when the alert executes: #FREQUENCY# - the number of minutes chosen for the execution interval. This query looks back in the repository...

Many people like to customize the database (and transaction log) free space alerts and this is a script that can be used to help do that. Feel free to edit the script as needed for your environment. This script loops through all databases in an instance to collect free space data and then returns it back to the alert in DPA. The query returns 2 columns: 1) the message about the database/tlog and 2) the free space percent. Note : this script only checks free space for database files that have a...

If you want to alert on table sizes, the attached script will retrieve a list of tables with its associated size in GB for all non-system database in the instance. This script can be plugged into a Custom - Multiple Numeric alert in DPA. For example, if you want to get a Warning alert when a table grows to above 850GB and a Critical alert, setup an alert with thresholds similar to this:

Introduction DPA can currently alert on resource metrics that exceed specific threshold values. However, some customers have wanted DPA to alert based on baselines. For example, my CPU typically averages about 40% and never deviates that much, but I want to know if that jumps to 60%. The default threshold for CPU % is 90% and an alert would not get triggered. However, if you calculated baselines for CPU, it might have a 90th percentile value of 47, so if you use that within the alert, 60% would...

Description DPA includes an out of the box alert to alert you on database job failures. However, there is no way to adjust it to exclude jobs by name, owner, etc. Wi this custom alert, simply modify the query that executes to exclude any necessary jobs and an example is provided to exclude by job name. Note : the SQL statement returns the job information and error in the first column, and a value of 1 (think of as true, i.e. it failed) in the second column. The alert levels near the bottom include...

Description If you have Oracle Scheduler jobs running and want to be notified when one of those jobs runs long, this alert will help. A long running job in this case is defined as one that is currently running and has exceeded 2 standard deviations of its average execution time. This alert uses 14 days of history to calculate the average. Alert Definition Create a Custom Alert of type " Custom SQL Alert - Multiple Numeric Return", copy/paste in the attached SQL statement and make it look similar...

If you want to get alerted by DPA when a data or log file grows/shrinks, this alert will help you do that. As a pre-requisite, an extended event session needs to be configured to track the changes that occur. Configure Extended Event Session Extended Event Sessions (EES) can be used to track many events that occur within your SQL instance, and one of those events is named "database_file_size_changed". It fires when a database data or log file grows or shrinks and to configure the EES from Management...

Introduction DPA's base feature set includes alerts for SQL Server, but none are enabled by default. Almost all SQL Server environments that are using DPA could benefit from a base set of alerts this article includes. The attached zip file contains 25 alerts that can be imported into your environment. Some of them are standard DPA alerts and some are custom alerts that may be important to you (and will get added to DPA eventually). Importing the Alerts To import these alerts, download the attachment...

DPA can be configured to monitor your VMware environment for the databases you are monitoring. The easiest approach is to add a vCenter server into DPA and here is more information on how to configure this: https://documentation.solarwinds.com/en/success_center/dpa/content/dpa-register-virtual-machine.htm Once this is configured, DPA will collect performance data as well as events that happen within VMware. Some of these events may be more important to you, especially events for vMotion (the VM...

This alert executes against the DPA repository and finds queries that have had plan changes recently, and the wait times have increased by more than 100% (doubled). It will return the query text along with details about recent execution times as well as historical averages. To create this, add an alert of type Custom SQL - Multiple Numeric Return and make it look similar to this (paste in the attached SQL statement into the alert):

This custom alert can be used to catch sessions that have been running for more than X seconds. The first columns provides a description of the session and what the session is doing, and the 2nd column is the time it has been running. Configure a Custom SQL Alert - Multiple Numeric Return alert in DPA, and plug in the attached query for the SQL Statement field. Setup thresholds based on the number of seconds you consider a long running query. Here is an example I am using to catch session running...

When it comes to blocking, DPA can currently alert when the cumulative blocking wait time goes beyond a threshold. The alert can be found under the category of Wait Time and is named Total Blocking Wait Time. However, one drawback to alerting on cumulative blocking time, is that it can potentially be noisy in a busy server. For example, lets take 2 blocking scenarios: 10 root blocker sessions each blocking 20 other sessions for 15 seconds 1 root blocker session blocking 2 other sessions for...

An Oracle database can be started in several different states, but READ WRITE is the most common. To ensure the database remains in a READ WRITE state, you can create a custom alert within DPA to detect any other status using the attached query. This query will return 0 (false) if the status is READ WRITE and 1 for any other status and alert you. To create the alert, create a Custom SQL Alert - Single Numeric Return and paste in this query. You can use 1 as your Min threshold for the High status...