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...
PostgreSQL Deadlocks
When deadlocks occur in a PostgreSQL instance, they are written into a log file specified by log_directory and log_filename parameters in posgresql,conf file. The error looks similar to the following:
2023-06-02 09:15:03.717 CDT [10384] ERROR: deadlock detected 2023-06-02 09:15:03.717 CDT [10384] DETAIL: Process 10384 waits for ShareLock on transaction 3087012926; blocked by process 11352. Process 11352 waits for ShareLock on transaction 3087012925; blocked by process 10384...
Within SQL Server, it is nice to know when objects are getting created, modified and or dropped. To configure DPA to alert you about these events, here are the steps:
Step 1 - Either edit an existing extended event session like system_heath or create a new session and have it watch for events named object_created, object_altered, object_deleted. On some versions, these are already part of the system_health extended event session, so check there first.
Step 2 - Configure a DPA alert to read...
To monitor SQL Server replication, you should first add in the custom metrics from this article: https://thwack.solarwinds.com/content-exchange/database-performance-analyzer/m/custom-metrics/3748 . The custom metric queries are also attached to this article as well.
When adding the metrics, set thresholds on the ones you want to alert on, and then create a Resource Alert similar to below. To use a single alert for all replication metrics, choose All Metrics in a Category as the alert type:
...
Description
Database corruption occurs when data is not stored correctly on disk or I/O subsystem. The cause of the corruption is often a hardware issue on the I/O systems or a bug in the operating system, but corruption can be caused by a wide array of issues. The problem you will notice are errors when SQL Server tries to read the corrupted information. Within SQL Server the suspect_pages table in the msdb database records the corrupted pages whenever the page is read and an error occurs, CHECKDB...
DESCRIPTION
This alert works in conjunction with the SQL Server Table Size Collector alert in this same section. Please install that alert as a requirement for this alert. This alert will review the last 2 days of data from the collector alert (runs once a day) and calculate the growth of each table based on number of rows. You can use the thresholds in the alert to get warned when the number of rows in a table grows by more than 10% is a given day as an example.
ALERT DEFINITION
To create...
DESCRIPTION
This alert will collect the number of rows of the top 500 tables (based on number of rows) in all databases in a SQL instance. This example alert is used only for collecting data and would never alert anyone. This data is provided as input for another custom alert named "SQL Server Table Percent Growth". Also shown below is a query that will allow you to report on this data as collected in the DPA alert tables.
ALERT DEFINITION
To create this alert, click on Alerts > Manage Alerts...
Monitoring the db2diag.log file is key to understanding when problems are occuring. Both warning and error message are written to this file, and a DPA custom alert can watch this file by using the SYSPROC.PD_GET_LOG_MSGS system function. The 2 main columns used in this alert are the MSG (the error message) and MSGSEVERITY (W for Warning or E for Error) columns.
This custom alert turns any Warning messages into an alert value of 1 and Error message use a value of 2. This allows us to setup alert...
This alert is based off data collected as part of the Database Size custom metric: https://thwack.solarwinds.com/content-exchange/database-performance-analyzer/m/custom-metrics/3452
It checks the max collected value from today, compared to yesterday and returns a percent of growth. The threshold are based on the percent growth, so it you want to be notified when a database grows more than 10 or 20%, use these thresholds:
Note: after pasting in this SQL script, modify the value for the variable...
Later versions of Sybase include a monitoring view named monDeviceSpaceUsage that can help us monitor disk space within DPA. This monitoring view shows data for each file for all databases, but we can use some MIN and MAX logic to break that down to the drive level. The query will return multiple rows of free space percentages at the drive level.
To create the alert within DPA, create an alert of type Custom - Custom SQL Alert - Multiple Numeric Alert and configure thresholds accordingly. Note...
Many customers have asked if DPA can alert them when a SQL statement starts executing longer than it has historically. This custom alert will help do that and as written will calculate the following:
Historical Average - how long has the query historically averaged - uses CON_STATS_SUM_XX DPA table
Recent Average - how long as the query executed on average recently - uses a combination of CONSW_XX and CONSS tables
Percent Higher - how much higher is the current average compare to history...
DPA does not include disk space monitoring out of the box, but there are DMVs that can help us create a custom alert. The DMV is named dm_os_volume_stats and when it's joined to the sys.master_files table, we can see available bytes and total bytes for each database file. Aggregating that at the drive/mount point will allow a free space percent to be calculated for each drive.
To add this alert into DPA, create a custom alert of type Custom SQL Alert - Multiple Numeric Return and paste in the attached...
