| Control Center Jobs | Description |
|---|---|
| Availability | |
| Agent Jobs Check | Checks whether there exists jobs on the SQL server which have not been executed, or have failed during execution. |
| Agent Jobs Check for MS2000 | Checks whether there exists jobs on the SQL server which have not been executed or have failed during execution. |
| SQL Server Agent status | Checks if the SQL Server Agent is running. |
| Database backup for MS2000 | This procedure analyzed the backup statistics from msdb.dbo.backupset table (excluding system databases: master, model and msdb). |
| Database backup for system dbs MS2000 | This procedure analyzed the system database backups statistics from msdb.dbo.backupset table. |
| Database backup | This procedure analyzes the backup statistics (type D and I) from the msdb.dbo.backupset table (excluding the system databases: master, model and msdb). |
| Database backup for SIMPLE recovery model | This procedure analyzes the backup statistics (type D) from the msdb.dbo.backupset table for databases in SIMPLE recovery model (excluding the system databases: master, model and msdb). |
| Database backup for system databases | This procedure analyzes the FULL backup statistics for the system databases (master, model and msdb) using data from the msdb.dbo.backupset table. |
| Database Log backup | This procedure checks the database transaction log backups from the msdb.dbo.backupset table (excluding system databases: master, model and msdb). |
| Collation check | Checks if there is a collation conflict with temp tables and table variables. |
| Check database recovery mode | Checks if all databases are running in FULL or SIMPLE recovery mode. |
| Database status | Checks if all databases have status ONLINE |
| Database status for MS2000 | Checks if all databases have status ONLINE |
| Database Server uptime | Collects database server uptime statistics. |
| Database Server uptime for MS2000 | Collects database server uptime statistics. |
| Database Server uptime for MS2005 | Collects database server uptime statistics. |
| Instance error log | Reads and checks the Instance error log file by using the sp_readerrorlog stored procedure. |
| Instance error log for AWS | Reads and checks the Instance error log file by using the AWS rdsadmin.dbo.rds_read_error_log stored procedure. |
| Instance status | This alert checks if the instance has been restarted since the last check |
| Database backup noschema | This alert checks if the instance has been restarted since the last check |
| Database log backup noschema | This alert checks if the instance has been restarted since the last check |
| Database status noschema | This alert checks if the instance has been restarted since the last check |
| Missing database backup noschema | This alert checks if the instance has been restarted since the last check |
| Missing database log backup noschema | This alert checks if the instance has been restarted since the last check |
| Program status | Checks for any program connected to the SQL Server by checking the program_name column in the master.dbo.sysprocesses table. |
| Report Server status | Checks if the program ‘Report Server’ is connected to the SQL Server by checking the program_name column in the master.dbo.sysprocesses table. |
| Restricted Enterprise edition features | Checks if there are any restricted features in use (supported only by Enterprise or Developer SQL Server edition). |
| Restricted Enterprise edition features for MS2000 | Checks if there are any restricted features in use (supported only by Enterprise or Developer SQL Server edition). |
| Restricted Enterprise edition features for MS2005 | Checks if there are any restricted features in use (supported only by Enterprise or Developer SQL Server edition). |
| Capacity | |
| 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). |
| Data file size check for MS2000 | Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
| 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 aggregated for MS2000 | 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 database files (including transaction log files) to visualize the growth rate for the largest databases. |
| Database growth rate detailed for MS2000 | Collects size of database files (including transaction log files) to visualize the growth rate for the largest databases. |
| Database disk capacity | Checks free space on drives where data and transaction log files are defined. An alarm (or warning) is raised if the percentage limit is reached OR if the abosulte limit is reached. IF the xp_cmdshell Instance configuration option is enabled the alert can check disk and mounted volumes where data-files are not present. |
| Database disk space usage | Checks free space on drives where all data and transaction log files are defined. Drives where no data files exist will be ignored. |
| Databases NOT IN USE collector | Collects information about most inactive databases. |
| Disk space check | Checks the amount of free space on the available disk drives. |
| Instance error log file size check | Checks the size og the Instance error log file by using the extended stored procedure xp_cmdshell. |
| Filegroups growth rate | This procedure collects space usage for the largest filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined on a SQL Server instance. |
| Filegroups growth rate for MS2000 | This procedure collects space usage for the largest filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined on a SQL Server instance. |
| Data file size check noschema | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
| Database disk capacity noschema | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
| Disk space check noschema | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
| Transaction log space usage noschema | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
| Objects size collector all databases | Collects table and index size information for the largest objects for all databases. |
| Temporary database space usage | Checks space usage in tempdb database, and collects statistics including size of data and transaction log files. |
| Transaction log size check | This procedure checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). If the size of the transaction log file(s) in percentage is greater than the warning/alarm parameter value, the check returns a warning/alarm. |
| Transaction log size check | This procedure checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). If the size of the transaction log file(s) in percentage is greater than the warning/alarm parameter value, the check returns a warning/alarm. |
| Transaction log space usage | This procedure checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log. |
| Transaction log space usage for MS2000, MS2005 and MS2008 | This procedure checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log. |
| Truncate transaction log | This procedure truncates the transaction log for the dbWatch database. |
| Version store space usage | Checks total space in tempdb used by version store records for each database. |
| Cluster | |
| Database mirroring | Checks state information of all mirrored databases. |
| Failover cluster host switch | Checks if an instance switched to a differen host i a Windows Server Failover Cluster (WSFC). |
| Health state | Checks groups health state. |
| Log shipping monitor | Monitor the primary database in each log shipping configuration, including information about the last backup file and last restored file. |
| Log shipping monitor | Monitor the secondary database in each log shipping configuration. |
| Member state | Checks the status of each member node of the current WSFC cluster based on Dynamic Management Views sys.dm_hadr_cluster_members. |
| AlwaysOn database backup alert | Checks the status of each member node of the current WSFC cluster based on Dynamic Management Views sys.dm_hadr_cluster_members. |
| AlwaysOn transaction log backup alert | 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. |
| Replication status | The following check provides general info in regards to any replication going on in a server. |
| Compliance optional | |
| MS SQL Server patch status | Checks latest updates for Microsoft SQL Server. |
| Consolidation optional | |
| Database statistics | This procedure gathers statistics per database. |
| Internal optional | |
| This alert returns warning/alarm if there are several dbWatch engines installed on the same instance. | |
| dbWatch Server activity alert | This alert returns warning/alarm if the dbWatch Server is using consumes a lot of cpu over a period of time. |
| Maintenance | |
| Autogrow settings | Checks database files auto-growth settings. |
| framework | dbWatch engine framework job (for internal use only). Used for patching or upgrading of dbWatch engine framework. |
| framework for MS2000 | dbWatch engine framework job (for internal use only). Used for patching or upgrading of dbWatch engine framework. |
| Maintenance optional | |
| Backup All databases | Takes backup of all application and system databases. |
| 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. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. |
| External fragmentation all databases | External (logical) fragmentation occurs when an index leaf page is not in logical order. 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 |
| 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. |
| SQL Server performance counters | Checks if SQL Server performance counters are missing. |
| Rebuild indexes | Rebuilds fragmented indexes in all databases. |
| Rebuld indexes in table | Rebuilds fragmented indexes in all tables listed in the ‘table list’ parameter. |
| Reorganize indexes | Reorganizes fragmented indexes in all databases. |
| Reorganize indexes in table | Reorganizing fragmented indexes in all tables listed in the ‘table list’ parameter. |
| Shrink transaction logs | This procedure shrinks transaction log files which are detected by ‘Transaction log size check’ alert which cheks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). |
| Suspect pages | Monitors suspect pages statistics in suspect_pages table. |
| Update index statistics | Update statistics in all (non-system) databases. |
| Update statistics | Update statistics in all (non system) databases. |
| Migration optional | |
| Statistics migration | Migrates statistics from a number of tasks from dbWatch version 12 to dbWatch CC. |
| Performance | |
| Blocking detector | Checks whether there exists blocked session. |
| Blocking statistics | Checks whether there exists any blocked sessions. |
| Cached query statistics | This task returns aggregate performance statistics based on cached query plans in SQL Server. |
| Data cache memory usage | Collects data cache memory usage per database (for top 10 databases). |
| Data cache memory usage for MS20005 and MS2008 | 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’. |
| Database session load | Shows the number of connections over time per database, host and application. |
| Test DML-DDL performance | Runs performance test on the database. The procedure executes SELECT, INSERT, UPDATE, DELETE (and TRUNCATE) statements on the test table. |
| File IO statistics | Shows the number of connections over time per database, host and application. |
| 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. |
| High activity monitor for MS2005 | 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 | This job checks the target memory value of the SQL Server instance, and gives a warning/alarm if the instance is not able to allocate a |
| 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. |
| Instance memory usage for MS2005 and MS2008 | 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. |
| 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. |
| Memory objects statistics | This task collects the size of memory objects that are currently allocated by the SQL Server |
| Blocking detector noschema | Checks whether there exists any blocked sessions. |
| Instance memory check noschema | 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. |
| Server memory statistics | This tasks collects statistics from the sys.dm_os_sys_info performance view about the memory resources available to and consumed by the SQL Server. |
| Sessions per database | This task returns aggregate performance statistics based on sessions connected to the SQL Server instance. |
| Session load | Shows the number of active sessions over time. |
| Transactions log flushed bytes load | Shows bytes flushed to transaction logs over time, total and top 5 databases. |
| Transactions log flushed bytes load for MS2000 | Shows bytes flushed to transaction logs over time, total and top 5 databases. |
| Transactions load | Shows the transactions load 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. |
| Performance optional | |
| SQL statistics | Collects performance statistics for the SQL statements. |
| Query Store status | Checks Query Store space usage in every database where it is enabled. |
Post your comment on this topic.