| Tasks and Alerts | Description |
|---|---|
| SQLServer | |
| Database alwayson backup | Analyzes the backup statistics (type D and I) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb). |
| Database alwayson log backup | Checks the database transaction log backups from the msdb.dbo.backupset table (excluding system databases: master, model and msdb). |
| Health state | Checks groups health state. |
| Member state | Checks the status of each member node of the current WSFC cluster based on Dynamic Management Views sys.dm_hadr_cluster_members. |
| Replica states | Checks role state for all alwayson groups. |
| Agent Jobs Check | Checks whether there exists jobs on the SQL server which have not been executed, or have failed during execution. |
| Check database recovery mode | Checks if all databases run in SIMPLE (or FULL) recovery mode. |
| Collation check | Checks if there is a collation conflict with temp tables and table variables. |
| Database Log backup | Checks the database transaction log backups from the msdb.dbo.backupset table excluding system databases |
| Database Server uptime | Collects database server uptime statistics. |
| Database backup | Analyzes the backup statistics (type D and I) from the msdb.dbo.backupset table excluding the system databases |
| Database backup (SIMPLE) | Analyzes the backup statistics (type D) from the msdb.dbo.backupset table for databases in SIMPLE recovery model excluding the system databases |
| Database backup (System databases) | Analyzes the backup statistics for the system databases (master, model and msdb) using data from the msdb.dbo.backupset table. |
| Database status | Checks if all databases have status ONLINE |
| Deadlocks event collector | Collects deadlocks event statistics from Microsoft SQL Server Extended Event Log files. |
| Instance error log | Reads and checks the Instance error log file by using the sp_readerrorlog stored procedure. |
| Instance status | This alert checks if the instance has been restarted since the last check |
| Restricted Enterprise edition features | Checks if there are any restricted features in use (supported only by Enterprise or Developer SQL Server edition). |
| SQL Server Agent status | Checks if the SQL Server Agent is running. |
| Auto growth event collector | Collects statistics on how often an auto-growth event has occurred. |
| Data file size check | Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
| Database disk capacity | Checks free space on drives where data and transaction log files are defined. |
| Database disk space usage | Checks free space on drives where all data and transaction log files are defined. |
| Database growth rate (aggregated) | Collects size of all database files (including transaction log files) to visualize the growth rate for all databases. |
| Database growth rate (detailed) | Collects size of all database files (including transaction log files) to visualize the growth rate for all databases. |
| Databases NOT IN USE collector | Collects information about most inactive databases. |
| Filegroups growth rate | collects space usage in all filegroups defined in each database |
| Instance error log file size check | Checks the size of the Instance error log file by using the extended stored procedure xp_cmdshell. |
| Objects size collector (all databases) | Collects table and index size information for the largest objects in the system. The information is extracted from the dynamic management view sys.dm_db_index_physical_stats. |
| Temporary database space usage | Collects space usage and size of tempdb database (including transaction log files) to visualize the temporary space usage. |
| Transaction log size check | Checks transaction log size |
| Transaction log space usage | Checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log. |
| Autogrow settings | Checks database files auto-growth settings. |
| Autoshrink settings | Checks if all databases have auto-shrink turned off. |
| Backup All databases | Takes backup of all application and system databases. Only ONLINE databases with the following properties are included: UserAccess = MULTI_USER, READ_ONLY, IsInStandBy = 0, IsMergePublished = 0, IsPublished = 0 and IsSubscribed = 0. |
| Backup All transaction logs | Takes backup of all transaction logs for databases running in FULL recovery mode. |
| Check database and server principal mapping | Checks if the database owner (dbo) is maped into any Server Login (server principal). |
| Cleanup MSDB history tables | This task deletes entries in the MSDB database history tables which holds statistics of backup/restore, jobs and maintenance plan executions. |
| Cycle error log | This task cycle MS SQL Server error log and Agent error log files. |
| DBCC CHECKDB | Checks the logical and physical integrity of all the objects in all databases by performing the DBCC CHECKDB operation. |
| DBCC UPDATEUSAGE | This task is performing the DBCC UPDATEUSAGE operation to corrects pages and row count inaccuracies in the catalog views. |
| Internal fragmentation check | Checks the internal fragmentation for tables and indexes in all databases. The information is extracted from the dynamic management function (view) sys.dm_db_index_physical_stats. |
| Rebuild indexes | Rebuilds fragmented indexes in all databases. |
| Reorganize indexes | Reorganizes fragmented indexes in all databases. |
| SQL Server performance counters | Checks if SQL Server performance counters are missing. |
| Update index statistics | Update index statistics in all (non-system) databases. |
| Update statistics | Update statistics in all (non system) databases. |
| Blocking statistics | Checks whether there exists any blocked sessions. |
| Data cache memory usage | Collects data cache memory usage per database (for top 10 databases). |
| Data hit ratio | Monitors the buffer cache hit ratio by extracting counter values from the master.dbo.sysperfinfo table for the counters “Buffer cache hit ratio” and “Buffer cache hit ratio base”. |
| External fragmentation (all databases) | It occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server to perform extra work to return ordered results. |
| File IO statistics | Collects I/O statistics for data and log files. |
| High activity monitor | Collects SQL instance CPU usage, CPU usage of other processes, logical and physical reads, and active processes count on the SQL Server instance. |
| Index usage statistics (all databases) | This procedure collects statistics from sys.dm_db_index_usage_stats performance view which gives information on how an index (or a table – heap) has been used to resolve queries. |
| Instance memory check | The alert checks the target memory value of the SQL Server instance, and gives a warning/alarm if the instance is not able to allocate a certain percentage of the total server/machine memory. |
| Instance memory usage | Collects total memory usage and data cache memory usage for SQL Server instance. This dbWatch task can be configured to automatically reduce the amount of memory used by the SQL Server instance. |
| Internal fragmentation (all databases) | Checks Internal fragmentation – occurs when there is too much free space in the index pages. |
| Lazy writer and Checkpoint statistics | Monitors “Checkpoint pages/sec”, “Lazy writes/sec” and “Page life expectancy” by extracting counter values from the sys.dm_os_performance_counters performace table. |
| SQL statements (Logical reads) | Collects statistics for the SQL statements corresponding to the query plans that have resulted in the most logical reads. |
| Session load | Shows the number of active sessions over time. |
| Sessions per database | This task returns aggregate performance statistics based on sessions connected to the SQL Server instance. |
| Test DML-DDL performance | Runs performance test on the database. The procedure executes SELECT, INSERT, UPDATE, DELETE (and TRUNCATE) statements on the test table. |
| Transactions load | Shows the transactions load over time, total and top 5 databases. |
| Transactions log flushed bytes load | Shows bytes flushed to transaction logs over time, total and top 5 databases. |
| Wait statistics | Collects statistics about all waits encountered by threads that executed. This task is based on the sys.dm_os_wait_stats dynamic performance view. |
| Database mirroring | Checks state information of all mirrored databases. The Catalog View master.sys.database_mirroring is used to alert if any Principals or Mirrors are in an abnormal state (normal states: SYNCHRONIZED,SYNCHRONIZING). |
| Log shipping monitor (primary) | Monitor the primary database in each log shipping configuration, including information about the last backup file and last restored file. |
| Log shipping monitor (secondary) | Monitor the secondary database in each log shipping configuration. |
| Replication status | The following check provides general info in regards to any replication going on in a server. |
Post your comment on this topic.