SQL Server instance alarms

Alarms raised under a Spotlight Cloud Basic subscription

• User - Response Time alarm
• Memory - Page Life Expectancy alarm
• Services - DTC Service Status alarm
• Services - Full Text Search alarm
• Services - Integration Service Status alarm
• Services - Reporting Service Status alarm
• Services - SQL Agent Mail Status alarm
• SQL Agent - Alerts alarm
• TempDB Contention alarm
• Databases - Data File Group Space Used alarm
• Databases - Dynamic Data File Group Growths Remaining alarm
• Databases - Unavailable alarm

Alarms raised under a Spotlight Cloud Professional subscription

Requires Spotlight Cloud Professional

Availability Group - Failover occurred

Raised when the availability group role on the monitored instance is changed as a result of automatic or manual failover.

Availability Group - Listener status is not ONLINE

Raised when the state of an availability group listener is “not online”. Connections using the listener’s name will fail to connect to availability group databases.

Availability Group - Not Ready for Failover

Raised when the failover mode of the primary replica of the availability group is automatic, however none of the secondary replicas in the availability group are failover ready or at least one availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state.

Backup - A Full Backup Is Not Defined alarm

Raised when a full backup on the SQL Server database has never taken place.

Backup - Days Since Last Full Backup alarm

Raised when the number of days since the last valid full database backup exceeds a predefined threshold.

Backup - Hours Since Last Differential Backup alarm

Raised when the number of hours since the last valid differential database backup exceeds a predefined threshold.

Backup - Minutes Since Last Log Backup alarm

Raised when the number of minutes since the last log backup of the SQL Server database exceeds a predefined threshold. Log backups are useful in keeping transaction logs in reasonable size.

Clusters - Failed Over alarm

Raised when the SQL Server cluster group’s active node has moved to another node. If the SQL Server host is less powerful or has to share resources with competing consumers this may result in slower performance of SQL Server.

Clusters - File Share Witness Inaccessible alarm

Raised when the cluster resource File Share Witness in a clustered service or Cluster Group application failed.

Clusters - Node Unavailable alarm

Raised when at least one cluster node (computer system) is not currently running as part of the cluster. When a cluster node is unavailable the workload that it normally processes will fail over to other servers in the cluster, resulting in lower performance.

Clusters - Non-Preferred Node alarm

Raised when SQL Server is not running on its preferred cluster node.

Compiles - Percentage Recompilations alarm

Raised when the average ratio between the number of recompiles and the total number of compiles exceeds a threshold. This value is taken over a specific number of background collections.

Databases - Data File Group about to grow

Raised when a file in the disk group is about to grow.

Databases - Data File Group Space Used alarm

Raised when a database data file group is nearing its full capacity.

Databases - Dynamic Data File Group Growths Remaining alarm

Raised when the disk space is insufficient for files to keep growing into or the maximum file size (if it is set) is being approached.

Databases - Dynamic Log File Growths Remaining alarm

Raised when a non fixed size log file in any database is in danger of running out of space to grow.

Databases - Log File Space Used alarm

Raised when a database log file is reaching its full capacity.

Databases - Log Flush Wait Time alarm

Raised when the duration of the last log flush for a database exceeds a threshold.

Databases - SQL I/O Errors alarm

Raised when I/O errors are encountered by SQL Server.

Databases - Unavailable alarm

Raised when a SQL Server database is not available for reading. Users attempting to access an unavailable database will receive an error message.

Error Log - Error Count alarm

Raised when Spotlight Cloud detects messages that could become potential problems in the SQL Server error log, SQL Server Agent error log or Windows event logs. To configure Spotlight Cloud to detect these messages, see configure error log.

Error Log - Critical Error (severity level lower than 20)

Raised when SQL Server experiences a new informational error.

Error Log - Fatal Current Process Error (severity level 20)

Raised when SQL Server experiences a new fatal error of severity level 20. It indicates that a statement has encountered a problem.

Error Log - Fatal Database (dbid) Process Error (severity level 21)

Raised when SQL Server experiences a new fatal error of severity level 21. It indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.

Error Log - Fatal Table Integrity Suspect Error (severity level 22)

Raised when SQL Server experiences a new fatal error message of severity level 22. It might indicate that the table or index specified in the message has been damaged by a software or hardware problem.

Error Log - Fatal Database Integrity Suspect Error (security level 23)

Raised when SQL Server experiences a new fatal error message of severity level 23. It indicates that the integrity of the entire database is in question because of a hardware or software problem.

Error Log - Fatal Hardware Error (severity level 24)

Raised when SQL Server experiences a new fatal error message of severity level 24. It indicates a media failure.

Error Log - Fatal Hardware Error (severity level 25)

Raised when SQL Server experiences a new fatal error message of severity level 25. It indicates a system error.

Error Log - High Severity Error Log Message

Raised when the SQL Server Agent experiences a new critical error.

Error Log - Medium Severity Error Log Message

Raised when the SQL Server Agent experiences a new warning error.

Error Log - Low Severity Error Log Message

Raised when the SQL Server Agent experiences a notice error.

Error Log - Information Severity Error Log Message

Raised when the SQL Server Agent experiences a new informational error.

I/O Stall Time alarm

Raised when the average I/O wait time for any database file exceeds a threshold. This value is taken over a specific number of background collections.

Index Fragmentation alarm

Raised when the largest index partition on the SQL Server is more than 5% fragmented. It is a good idea to defragment large index partitions that are more than 5% fragmented. By default, only partitions that have more than 1000 pages, and 5 or more scan or update operations, are taken into consideration. To configure the defaults, see configure fragmentation checks.

Locks - Blocked Processes alarm

Raised when a process is blocked for longer than a predefined threshold. The default is 120 seconds. Excessive blocking can be a major cause of poor application performance.

Locks - Deadlocks alarm

Raised when the number of deadlocks detected since the previous data collection exceeds a threshold. A Deadlock occurs when multiple SQL Server sessions request conflicting locks in such a way that two locks are blocked by each other.

Locks - Long Duration Block alarm

Raised when the amount of time for which a specific process in a SQL Server instance has been blocked exceeds a predefined threshold. Excessive blocking can be a major cause of poor application performance.

Log Shipping - Primary Backup Job alarm

Raised when the backup job on the primary database is disabled.

Log Shipping - Secondary Job alarm

Raised when a secondary copy or restore job is disabled.

Log Shipping - Secondary Latency alarm

Raised when the amount of time elapsed between the log backup being created on the primary database and restored on the secondary database exceeds a threshold.

Memory - Page Life Expectancy alarm

Raised when the page life expectancy falls below a threshold.

Mirroring - Failedover alarm

Raised when the principal database becomes unavailable. The mirror server takes over the role of principal server and brings its copy of the database online as the principal database.

Mirroring - State alarm

Raised when the mirror database is not synchronized or is synchronizing.

Mirroring - Unrestored Log alarm

Raised when the size of the mirroring log on the mirroring database exceeds a threshold.

Mirroring - Unsent Log alarm

Raised when the size of an unsent mirroring log exceeds a threshold.

Missing SQL Performance Counters alarm

Raised when information for SQL Server performance counters is missing. Spotlight Cloud will not be able to collect the required data and will display “0” for some of its metrics.

Monitored Server - Missing Windows Host alarm

Raised when the SQL Server connection is not linked to an operating system connection.

Monitored Server - SQL Server Alarm Action Failure alarm

Raised when a configured Alarm Action failed to evaluate.

Monitored Server - SQL Server Alarm Evaluation Failure alarm

Raised when Spotlight Cloud failed to evaluate a given alarm from a given collection.

Monitored Server - SQL Server Collection Execution Failure alarm

Raised when a given collection failed to execute against the monitored server.

Monitored Server - SQL Server Connection Failure alarm

Raised when Spotlight Cloud fails to connect to the SQL Server. Check the SQL Server address, authentication and availability.

Monitored Server - SQL Server Host Not Defined alarm

Raised when the SQL Server host is not defined for a Microsoft Cluster Server (MSCS).

Monitored Server - SQL Server Planned Outage alarm

Raised during a planned outage of the connection, and cleared upon its completion.

Monitored Server - SQL Server Secondary Connection Failure alarm

Raised when the SQL Server is not responding.

Network - SQL Server Packets Error Rate alarm

Raised when the rate at which SQL Server is encountering network packet errors exceeds a threshold.

Services - DTC Service Status alarm

Raised when The Distributed Transaction Coordinator (DTC) service is installed but not running.

Services - Full Text Search alarm

Raised when The Full Text Search service (MSSearch) is installed but not running.

Services - Integration Service Status alarm

Raised when the SQL Server Integration service is installed but not running.

Services - Reporting Service Status alarm

Raised when the SQL Server Report service is installed but not running.

Services - SQL Agent Mail Status alarm

Raised when the SQL Agent Mail service is installed but not running.

SQL Agent - Alerts alarm

Raised when at least one SQL Agent alert has occurred in the last few minutes.

SQL Agent - Jobs Failed alarm

Raised when a SQL Agent job fails.

SQL Agent - Long Running Job (Time) alarm

Raised when a SQL Agent job has been running longer than its average running time.

SQL Agent - Long Running Job (Percent) alarm

Raised when a SQL Agent job has been running for considerably longer than expected for SQL Agent jobs of its type.

SQL Agent - Status alarm

Raised when the SQL Server Agent service is installed but not running.

SQL - Long Running SQL alarm

Raised when SQL has been running longer than a configured time. Because the (configured time) is unique to each environment, the SQL - Long Running SQL alarm is not configured by default.

SQL Virtual Log Files Count alarm

Raised when the count of Virtual Log Files (VLFs) is excessive. A high number can have a major impact on SQL start up times and database restores.

Tempdb Auto Growth alarm

Raised when Tempdb is configured to auto grow and a significant number of growth events are detected.

Tempdb Contention alarm

Raised when Tempdb is experiencing contention.

Tempdb File Location alarm

Raised when Tempdb files are in the same location as other database files and I/O activity is significant and more than one drive is available.

Tempdb File Size alarm

Raised when files in Tempdb have different physical sizes.

Tempdb Growth Settings alarm

Raised when Tempdb files have nonidentical growth settings.

Tempdb Number of Files alarm

Raised when the number of files in the Tempdb database is less than the number of processors and the SQL Server is experiencing contention on Tempdb.

Tempdb Version Store Generation Rate alarm

Raised when the Tempdb Version Store growth rate is high compared to the cleanup rate.

Tempdb Version Store Size alarm

Raised when the Tempdb version store is too large for the monitored environment.

User - Connection Count alarm

Raised when the number of users connected exceeds a threshold.

User - Logins Per Second alarm

Raised when the average number of logins per second exceeds a threshold. This value is taken over a specific number of background collections.

User - Response Time alarm

Raised when the time taken for Spotlight Cloud to send a simple query to the monitored SQL Server instance, have it processed and get the answer back is more than the configured threshold. The response time alarm does not tell you where the time is spent; it could be the network or it could be the SQL Server instance is unresponsive.