Database Centric Alerts for SQLServer

Instance Level Alert

Except for Oracle platform all the dbWatch Alerts are returning status on the instance level and not on the database level. Although some of the dbWatch Alert return status about individual databases (like database backup or status) only one warning or alarm is raised even there are several databases causing the problem. In other words, you do not get a warning/alarm for each database causing a problem, but only one alert regardless of how may databases are involved. The warning or alarm message can in some cases list all names of the databases causing the alert, or just give you a number of databases if many has reached a warning or an alarm threshold. In that case you can always run a dbWatch Alert Report to get detailed information about each database.

Database Level Alert

Starting version 12.7.5 (For now it only supports SQL Server platform) all dbWatch Alert which are database centric (which check something in each database) produce “db_centric_alert_id” property (with the following sub-properties) for each database which raises a warning or an alarm.

Property:

db_centric_alert_id	Subproperty	Definition
	alert_name	name of the dbWatch Alert
	last_run	timestamp when the dbWatch Alert was last run
	status	the dbWatch Alert status – the highest alert value return by all databases
	db_name	the name of the database causing a warning or an alarm
	db_status	status return by the database – warning or alarm
	details	details related to the current database status

List of dbWatch database centric alerts

Here is a list of all (15) dbWatch database centric alerts for SQL Server platform:

Database status
Database backup
Database Log back
Check database recovery mode
Database backup (system databases)
Collation check
Data file size check
Transaction log size check
Transaction log space usage
Autogrow settings
Autoshrink settings
DBCC CHECKDB
Database mirroring
Log shipping monitor (primary)
Log shipping monitor (secondary)

How it works

The dbWatch Alert “Database backup” is a database centric alert and can give you a warning or an alarm if a database have old backup or the backup is missing.
Here you can see the “Database backup” Alert in alarm status “complaining” about 11 databases which haven’t been backed up.

You can then right click on the alert Database backup and choose Details.

A new window will appear showing you the detailed report of the alert, displays databases which has old and missing backups.

Retrieving data through DBWQL

To get an overview over all database causing problems you can check the sub-properties values for the “db_centric_alert_id” property for that alert
In DBWQL console – filtered for instance “SQLT01” and for dbWatch Alert “Database backup”.

In the example below shows all databases which hasn’t been backed up for the past few days.

Query:

instance[name=‘SQLT01’]{}/db_centric_alert_id[alert_name=‘Database backup’]->c

/$c/alert_name{}

/$c/last_run{}

/$c/status{}

/$c/db_name{}

/$c/db_status{#sort(desc)}

/$c/details{}?

In the example below shows all databases and their data/log files which has been changed due to autogrowth settings