MSSQL by ODBC
Macros used
| Name | Value |
|---|---|
| {$MSSQL.AVERAGE_WAIT_TIME.MAX} | 500 |
| {$MSSQL.BACKUP_DIFF.CRIT} | 6d |
| {$MSSQL.BACKUP_DIFF.WARN} | 3d |
| {$MSSQL.BACKUP_DURATION.WARN} | 1h |
| {$MSSQL.BACKUP_FULL.CRIT} | 10d |
| {$MSSQL.BACKUP_FULL.WARN} | 9d |
| {$MSSQL.BACKUP_LOG.CRIT} | 8h |
| {$MSSQL.BACKUP_LOG.WARN} | 4h |
| {$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT} | 30 |
| {$MSSQL.BUFFER_CACHE_RATIO.MIN.WARN} | 50 |
| {$MSSQL.DBNAME.MATCHES} | .* |
| {$MSSQL.DBNAME.NOT_MATCHES} | master|tempdb|model|msdb |
| {$MSSQL.DEADLOCKS.MAX} | 1 |
| {$MSSQL.DSN} | <Put your DSN here> |
| {$MSSQL.FREE_LIST_STALLS.MAX} | 2 |
| {$MSSQL.INSTANCE} | SQLServer |
| {$MSSQL.JOB.MATCHES} | .* |
| {$MSSQL.JOB.NOT_MATCHES} | CHANGE_IF_NEEDED |
| {$MSSQL.LAZY_WRITES.MAX} | 20 |
| {$MSSQL.LOCK_REQUESTS.MAX} | 1000 |
| {$MSSQL.LOCK_TIMEOUTS.MAX} | 1 |
| {$MSSQL.LOG_FLUSH_WAITS.MAX} | 1 |
| {$MSSQL.LOG_FLUSH_WAIT_TIME.MAX} | 1 |
| {$MSSQL.PAGE_LIFE_EXPECTANCY.MIN} | 300 |
| {$MSSQL.PAGE_READS.MAX} | 90 |
| {$MSSQL.PAGE_WRITES.MAX} | 90 |
| {$MSSQL.PASSWORD} | <Put your password here> |
| {$MSSQL.PERCENT_COMPILATIONS.MAX} | 10 |
| {$MSSQL.PERCENT_LOG_USED.MAX} | 80 |
| {$MSSQL.PERCENT_READAHEAD.MAX} | 20 |
| {$MSSQL.PERCENT_RECOMPILATIONS.MAX} | 10 |
| {$MSSQL.PORT} | 1433 |
| {$MSSQL.USER} | <Put your username here> |
| {$MSSQL.WORKTABLES_FROM_CACHE_RATIO.MIN.CRIT} | 90 |
| {$MSSQL.WORK_FILES.MAX} | 20 |
| {$MSSQL.WORK_TABLES.MAX} | 20 |
Items collected
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL: Get job status | The item gets sql agent job status. | ODBC | 10m | db.odbc.get[get_job_status,"{$MSSQL.DSN}"] |
| MSSQL: Get last backup | The item gets information about backup processes. | ODBC | 10m | db.odbc.get[get_last_backup,"{$MSSQL.DSN}"] |
| MSSQL: Get performance counters | The item gets server global status information. | ODBC | 0;m0-59 | db.odbc.get[get_status_variables,"{$MSSQL.DSN}"] |
| MSSQL: Auto-param attempts per second | Number of auto-parameterization attempts per second. The total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when an instance of SQL Server tries to parameterize a Transact-SQL request by replacing some literals with parameters to me reuse of the resulting cached execution plan across multiple similar-looking requests possible. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server. This counter does not include forced parameterizations. | DEPENDENT | - | mssql.autoparam_attempts_sec.rate |
| MSSQL: Average latch wait time | Average latch wait time (in milliseconds) for latch requests that had to wait. | CALCULATED | 0;m0-59s3 | mssql.average_latch_wait_time |
| MSSQL: Average latch wait time base | For internal use only. | DEPENDENT | - | mssql.average_latch_wait_time_base |
| MSSQL: Average latch wait time raw | Average latch wait time (in milliseconds) for latch requests that had to wait. | DEPENDENT | - | mssql.average_latch_wait_time_raw |
| MSSQL: Total average wait time | The average wait time, in milliseconds, for each lock request that had to wait. | CALCULATED | 0;m0-59s3 | mssql.average_wait_time |
| MSSQL: Total average wait time base | For internal use only. | DEPENDENT | - | mssql.average_wait_time_base |
| MSSQL: Total average wait time raw | Average amount of wait time (in milliseconds) for each lock request that resulted in a wait. Information for all locks. | DEPENDENT | - | mssql.average_wait_time_raw |
| MSSQL: Batch requests per second | Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput. | DEPENDENT | - | mssql.batch_requests_sec.rate |
| MSSQL: Buffer cache hit ratio | Indicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio changes very little. Since reading from the cache is much less expensive than reading from the disk, a higher value is preferred for this item. To increase the buffer cache hit ratio, consider increasing the amount of memory available to SQL Server or using the buffer pool extension feature. | DEPENDENT | - | mssql.buffer_cache_hit_ratio |
| MSSQL: Cache hit ratio | Ratio between cache hits and lookups. | DEPENDENT | - | mssql.cache_hit_ratio |
| MSSQL: Cache objects in use | Number of cache objects in use. | DEPENDENT | - | mssql.cache_objects_in_use |
| MSSQL: Cache object counts | Number of cache objects in the cache. | DEPENDENT | - | mssql.cache_object_counts |
| MSSQL: Cache pages | Number of 8-kilobyte (KB) pages used by cache objects. | DEPENDENT | - | mssql.cache_pages |
| MSSQL: Checkpoint pages per second | Indicates the number of pages flushed to disk per second by a checkpoint or other operation which required all dirty pages to be flushed. | DEPENDENT | - | mssql.checkpoint_pages_sec.rate |
| MSSQL: Database pages | Indicates the number of pages in the buffer pool with database content. | DEPENDENT | - | mssql.database_pages |
| MSSQL: Total data file size | Total size of all data files. | DEPENDENT | - | mssql.data_files_size |
| MSSQL: Total errors per second | Number of errors per second. | DEPENDENT | - | mssql.errors_sec.rate |
| MSSQL: Failed auto-params per second | Number of failed auto-parameterization attempts per second. This number should be small. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server. | DEPENDENT | - | mssql.failed_autoparams_sec.rate |
| MSSQL: Forwarded records per second | Number of records per second fetched through forwarded record pointers. | DEPENDENT | - | mssql.forwarded_records_sec.rate |
| MSSQL: Free list stalls per second | Indicates the number of requests per second that had to wait for a free page. | DEPENDENT | - | mssql.free_list_stalls_sec.rate |
| MSSQL: Full scans per second | Number of unrestricted full scans per second. These can be either base-table or full-index scans. Values greater than 1 or 2 indicate that there are table / Index page scans. If that is combined with high CPU, this counter requires further investigation, otherwise, if the full scans are on small tables, it can be ignored. | DEPENDENT | - | mssql.full_scans_sec.rate |
| MSSQL: Granted Workspace Memory | Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations. | DEPENDENT | - | mssql.granted_workspace_memory |
| MSSQL: Index searches per second | Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row. | DEPENDENT | - | mssql.index_searches_sec.rate |
| MSSQL: Errors per second (Info errors) | Number of errors per second. | DEPENDENT | - | mssql.info_errors_sec.rate |
| MSSQL: Errors per second (Kill connection errors) | Number of errors per second. | DEPENDENT | - | mssql.kill_connection_errors_sec.rate |
| MSSQL: Latch waits per second | The number of latch requests that could not be granted immediately. Latches are lightweight means of holding a very transient server resource, such as an address in memory. | DEPENDENT | - | mssql.latch_waits_sec.rate |
| MSSQL: Lazy writes per second | Indicates the number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers. | DEPENDENT | - | mssql.lazy_writes_sec.rate |
| MSSQL: Total lock requests per second | Number of new locks and lock conversions per second requested from the lock manager. | DEPENDENT | - | mssql.lock_requests_sec.rate |
| MSSQL: Total lock requests per second that timed out | Number of timed out lock requests per second, including requests for NOWAIT locks. | DEPENDENT | - | mssql.lock_timeouts_sec.rate |
| MSSQL: Total lock requests per second that required waiting | Number of lock requests per second that required the caller to wait. | DEPENDENT | - | mssql.lock_waits_sec.rate |
| MSSQL: Lock wait time | Average of total wait time (in milliseconds) for locks in the last second. | DEPENDENT | - | mssql.lock_wait_time |
| MSSQL: Logins per second | Total number of logins started per second. This does not include pooled connections. Any value over 2 may indicate insufficient connection pooling. | DEPENDENT | - | mssql.logins_sec.rate |
| MSSQL: Logouts per second | Total number of logout operations started per second. Any value over 2 may indicate insufficient connection pooling. | DEPENDENT | - | mssql.logouts_sec.rate |
| MSSQL: Total log file size | Total size of all the transaction log files. | DEPENDENT | - | mssql.log_files_size |
| MSSQL: Total log file used size | The cumulative used size of all the log files in the database. | DEPENDENT | - | mssql.log_files_used_size |
| MSSQL: Maximum workspace memory | Indicates the maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations. | DEPENDENT | - | mssql.maximum_workspace_memory |
| MSSQL: Memory grants outstanding | Specifies the total number of processes that have successfully acquired a workspace memory grant. | DEPENDENT | - | mssql.memory_grants_outstanding |
| MSSQL: Memory grants pending | Specifies the total number of processes waiting for a workspace memory grant. | DEPENDENT | - | mssql.memory_grants_pending |
| MSSQL: Total lock requests per second that have deadlocks | Number of lock requests per second that resulted in a deadlock. | DEPENDENT | - | mssql.number_deadlocks_sec.rate |
| MSSQL: Errors per second (DB offline errors) | Number of errors per second. | DEPENDENT | - | mssql.offline_errors_sec.rate |
| MSSQL: Page life expectancy | Indicates the number of seconds a page will stay in the buffer pool without references. | DEPENDENT | - | mssql.page_life_expectancy |
| MSSQL: Page lookups per second | Indicates the number of requests per second to find a page in the buffer pool. | DEPENDENT | - | mssql.page_lookups_sec.rate |
| MSSQL: Page reads per second | Indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design. | DEPENDENT | - | mssql.page_reads_sec.rate |
| MSSQL: Page splits per second | Number of page splits per second that occur as the result of overflowing index pages. | DEPENDENT | - | mssql.page_splits_sec.rate |
| MSSQL: Page writes per second | Indicates the number of physical database page writes that are issued per second. | DEPENDENT | - | mssql.page_writes_sec.rate |
| MSSQL: Percent of Adhoc queries running | The ratio of SQL compilations per second to Batch requests per second in percentage. | CALCULATED | 0;m0-59s3 | mssql.percent_of_adhoc_queries |
| MSSQL: Percent of Recompiled Transact-SQL Objects | The ratio of SQL re-compilations per second to SQL compilations per second in percentage. | CALCULATED | 0;m0-59s3 | mssql.percent_recompilations_to_compilations |
| MSSQL: Number of blocked processes | Number of currently blocked processes. | DEPENDENT | - | mssql.processes_blocked |
| MSSQL: Read-ahead pages per second | Indicates the number of pages read per second in anticipation of use. | DEPENDENT | - | mssql.readahead_pages_sec.rate |
| MSSQL: Safe auto-params per second | Number of safe auto-parameterization attempts per second. Safe refers to a determination that a cached execution plan can be shared between different similar-looking Transact-SQL statements. SQL Server makes many auto-parameterization attempts some of which turn out to be safe and others fail. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server. This does not include forced parameterizations. | DEPENDENT | - | mssql.safe_autoparams_sec.rate |
| MSSQL: Full scans to Index searches ratio | The ratio of Full scans per second to Index searches per second. The threshold recommendation is strictly for OLTP workloads. | CALCULATED | 0;m0-59s3 | mssql.scan_to_search |
| MSSQL: SQL compilations per second | Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes runs caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state. | DEPENDENT | - | mssql.sql_compilations_sec.rate |
| MSSQL: SQL re-compilations per second | Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low. | DEPENDENT | - | mssql.sql_recompilations_sec.rate |
| MSSQL: Table lock escalations per second | Number of times locks on a table were escalated to the TABLE or HoBT granularity. | DEPENDENT | - | mssql.table_lock_escalations.rate |
| MSSQL: Target pages | The optimal number of pages in the buffer pool. | DEPENDENT | - | mssql.target_pages |
| MSSQL: Target server memory | Indicates the ideal amount of memory the server can consume. | DEPENDENT | - | mssql.target_server_memory |
| MSSQL: Total latch wait Time | Total latch wait time (in milliseconds) for latch requests in the last second. This value should stay stable compared to the number of latch waits per second. | DEPENDENT | - | mssql.total_latch_wait_time |
| MSSQL: Total server memory | Specifies the amount of memory the server has committed using the memory manager. | DEPENDENT | - | mssql.total_server_memory |
| MSSQL: Total transactions number | The number of currently active transactions of all types. | DEPENDENT | - | mssql.transactions |
| MSSQL: Total transactions per second | Total number of transactions started for all databases per second. | DEPENDENT | - | mssql.transactions_sec.rate |
| MSSQL: Unsafe auto-params per second | Number of unsafe auto-parameterization attempts per second. For example, the query has some characteristics that prevent the cached plan from being shared. These are designated as unsafe. This does not count the number of forced parameterizations. | DEPENDENT | - | mssql.unsafe_autoparams_sec.rate |
| MSSQL: Uptime | MS SQL Server uptime in 'N days, hh:mm:ss' format. | DEPENDENT | - | mssql.uptime |
| MSSQL: Number users connected | Number of users connected to MS SQL Server. | DEPENDENT | - | mssql.user_connections |
| MSSQL: Errors per second (User errors) | Number of errors per second. | DEPENDENT | - | mssql.user_errors_sec.rate |
| MSSQL: Version | MS SQL Server version. | DEPENDENT | - | mssql.version |
| MSSQL: Work files created per second | Number of work files created per second. For example, work files can be used to store temporary results for hash joins and hash aggregates. | DEPENDENT | - | mssql.workfiles_created_sec.rate |
| MSSQL: Work tables created per second | Number of work tables created per second. For example, work tables can be used to store temporary results for query spool, lob variables, XML variables, and cursors. | DEPENDENT | - | mssql.worktables_created_sec.rate |
| MSSQL: Worktables from cache ratio | Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache. | DEPENDENT | - | mssql.worktables_from_cache_ratio |
| MSSQL: Service's TCP port state | Test the availability of MS SQL Server on a TCP port. | SIMPLE | 30s | net.tcp.service[tcp,{HOST.CONN},{$MSSQL.PORT}] |
Triggers
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL: Total average wait time for locks is high | An average wait time longer than 500ms may indicate excessive blocking. This value should generally correlate to 'Lock Waits/sec' and move up or down with it accordingly. | min(/MSSQL by ODBC/mssql.average_wait_time,5m)>{$MSSQL.AVERAGE_WAIT_TIME.MAX} | WARNING 📢 | MSSQL: Total average wait time |
| MSSQL: Percentage of the buffer cache efficiency is low | Too low buffer cache hit ratio. | max(/MSSQL by ODBC/mssql.buffer_cache_hit_ratio,5m)<{$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT} | HIGH ⛔ | MSSQL: Buffer cache hit ratio |
| MSSQL: Percentage of the buffer cache efficiency is low | Low buffer cache hit ratio. | max(/MSSQL by ODBC/mssql.buffer_cache_hit_ratio,5m)<{$MSSQL.BUFFER_CACHE_RATIO.MIN.WARN} | WARNING 📢 | MSSQL: Buffer cache hit ratio |
| MSSQL: Number of rps waiting for a free page is high | Some requests have to wait for a free page. | min(/MSSQL by ODBC/mssql.free_list_stalls_sec.rate,5m)>{$MSSQL.FREE_LIST_STALLS.MAX} | WARNING 📢 | MSSQL: Free list stalls per second |
| MSSQL: Number of buffers written per second by the lazy writer is high | The number of buffers written per second by the buffer manager's lazy writer exceeds the threshold. | min(/MSSQL by ODBC/mssql.lazy_writes_sec.rate,5m)>{$MSSQL.LAZY_WRITES.MAX} | WARNING 📢 | MSSQL: Lazy writes per second |
| MSSQL: Total number of locks per second is high | Number of new locks and lock conversions per second requested from the lock manager is high. | min(/MSSQL by ODBC/mssql.lock_requests_sec.rate,5m)>{$MSSQL.LOCK_REQUESTS.MAX} | WARNING 📢 | MSSQL: Total lock requests per second |
| MSSQL: Total lock requests per second that timed out is high | The total number of timed out lock requests per second, including requests for NOWAIT locks, is high. | min(/MSSQL by ODBC/mssql.lock_timeouts_sec.rate,5m)>{$MSSQL.LOCK_TIMEOUTS.MAX} | WARNING 📢 | MSSQL: Total lock requests per second that timed out |
| MSSQL: Some blocking is occurring for 5m | Values greater than zero indicate at least some blocking is occurring, while a value of zero can quickly eliminate blocking as a potential root-cause problem. | min(/MSSQL by ODBC/mssql.lock_waits_sec.rate,5m)>0 | AVERAGE ⚠ | MSSQL: Total lock requests per second that required waiting |
| MSSQL: Number of deadlock is high | Too many deadlocks are occurring currently. | min(/MSSQL by ODBC/mssql.number_deadlocks_sec.rate,5m)>{$MSSQL.DEADLOCKS.MAX} | AVERAGE ⚠ | MSSQL: Total lock requests per second that have deadlocks |
| MSSQL: Page life expectancy is low | The page stays in the buffer pool without references of less time than the threshold value. | max(/MSSQL by ODBC/mssql.page_life_expectancy,15m)<{$MSSQL.PAGE_LIFE_EXPECTANCY.MIN} | HIGH ⛔ | MSSQL: Page life expectancy |
| MSSQL: Number of physical database page reads per second is high | The physical database page reads are issued too frequently. | min(/MSSQL by ODBC/mssql.page_reads_sec.rate,5m)>{$MSSQL.PAGE_READS.MAX} | WARNING 📢 | MSSQL: Page reads per second |
| MSSQL: Number of physical database page writes per second is high | The physical database page writes are issued too frequently. | min(/MSSQL by ODBC/mssql.page_writes_sec.rate,5m)>{$MSSQL.PAGE_WRITES.MAX} | WARNING 📢 | MSSQL: Page writes per second |
| MSSQL: Percent of adhoc queries running is high | The lower this value is the better. High values often indicate excessive adhoc querying and should be as low as possible. If excessive adhoc querying is happening, try rewriting the queries as procedures or invoke the queries using sp_executeSQL. When rewriting isn't possible, consider using a plan guide or setting the database to parameterization forced mode. | min(/MSSQL by ODBC/mssql.percent_of_adhoc_queries,15m) > {$MSSQL.PERCENT_COMPILATIONS.MAX} | WARNING 📢 | MSSQL: Percent of Adhoc queries running |
| MSSQL: Percent of times statement recompiles is high | This number should be at or near zero, since recompiles can cause deadlocks and exclusive compile locks. This counter's value should follow in proportion to “Batch Requests/sec” and “SQL Compilations/sec”. | min(/MSSQL by ODBC/mssql.percent_recompilations_to_compilations,15m) > {$MSSQL.PERCENT_RECOMPILATIONS.MAX} | WARNING 📢 | MSSQL: Percent of Recompiled Transact-SQL Objects |
| MSSQL: Number of index and table scans exceeds index searches in the last 15m | Index searches are preferable to index and table scans. For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations. | min(/MSSQL by ODBC/mssql.scan_to_search,15m) > 0.001 | WARNING 📢 | MSSQL: Full scans to Index searches ratio |
| MSSQL: Failed to fetch info data | Zabbix has not received data for items for the last 30 minutes. | nodata(/MSSQL by ODBC/mssql.uptime,30m)=1 | INFO 🔔 | MSSQL: Uptime |
| MSSQL: Service has been restarted | Uptime is less than 10 minutes. | last(/MSSQL by ODBC/mssql.uptime)<10m | INFO 🔔 | MSSQL: Uptime |
| MSSQL: Version has changed | MSSQL version has changed. Ack to close. | last(/MSSQL by ODBC/mssql.version,#1)<>last(/MSSQL by ODBC/mssql.version,#2) and length(last(/MSSQL by ODBC/mssql.version))>0 | INFO 🔔 | MSSQL: Version |
| MSSQL: Number of work files created per second is high | Too many work files created per second to store temporary results for hash joins and hash aggregates. | min(/MSSQL by ODBC/mssql.workfiles_created_sec.rate,5m)>{$MSSQL.WORK_FILES.MAX} | AVERAGE ⚠ | MSSQL: Work files created per second |
| MSSQL: Number of work tables created per second is high | Too many work tables created per second to store temporary results for query spool, lob variables, XML variables, and cursors. | min(/MSSQL by ODBC/mssql.worktables_created_sec.rate,5m)>{$MSSQL.WORK_TABLES.MAX} | AVERAGE ⚠ | MSSQL: Work tables created per second |
| MSSQL: Percentage of work tables available from the work table cache is low | A value less than 90% may indicate insufficient memory, since execution plans are being dropped, or on 32-bit systems, may indicate the need for an upgrade to a 64-bit system | max(/MSSQL by ODBC/mssql.worktables_from_cache_ratio,5m)<{$MSSQL.WORKTABLES_FROM_CACHE_RATIO.MIN.CRIT} | HIGH ⛔ | MSSQL: Worktables from cache ratio |
| MSSQL: Service is unavailable | The TCP port of the MS SQL Server service is currently unavailable. | last(/MSSQL by ODBC/net.tcp.service[tcp,{HOST.CONN},{$MSSQL.PORT}])=0 | DISASTER 🔥 | MSSQL: Service's TCP port state |
Discovery rule №1
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Availability groups discovery | Discovery of the existing availability groups. | ODBC | 1h | db.odbc.discovery[availability_groups,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}': Get replica states | Getting replica states - name, primary and secondary health, synchronization health. | ODBC | - | db.odbc.get[{#GROUP_NAME}_replica_states,"{$MSSQL.DSN}"] |
| MSSQL AG '{#GROUP_NAME}': Primary replica recovery health | Indicates the recovery health of the primary replica: 0 = In progress 1 = Online 2 = Unavailable | DEPENDENT | - | mssql.primary_recovery_health["{#GROUP_NAME}"] |
| MSSQL AG '{#GROUP_NAME}': Primary replica name | Name of the server instance that is hosting the current primary replica. | DEPENDENT | - | mssql.primary_replica["{#GROUP_NAME}"] |
| MSSQL AG '{#GROUP_NAME}': Secondary replica recovery health | Indicates the recovery health of a secondary replica replica: 0 = In progress 1 = Online 2 = Unavailable | DEPENDENT | - | mssql.secondary_recovery_health["{#GROUP_NAME}"] |
| MSSQL AG '{#GROUP_NAME}': Synchronization health | Reflects a rollup of the synchronization_health of all availability replicas in the availability group: 0: Not healthy. None of the availability replicas have a healthy synchronization. 1: Partially healthy. The synchronization of some, but not all, availability replicas is healthy. 2: Healthy. The synchronization of every availability replica is healthy. | DEPENDENT | - | mssql.synchronization_health["{#GROUP_NAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}': Primary replica recovery health in progress | The primary replica is in the synchronization process. | last(/MSSQL by ODBC/mssql.primary_recovery_health["{#GROUP_NAME}"])=0 | WARNING 📢 | MSSQL AG '{#GROUP_NAME}': Primary replica recovery health |
| MSSQL AG '{#GROUP_NAME}': Secondary replica recovery health in progress | The secondary replica is in the synchronization process. | last(/MSSQL by ODBC/mssql.secondary_recovery_health["{#GROUP_NAME}"])=0 | WARNING 📢 | MSSQL AG '{#GROUP_NAME}': Secondary replica recovery health |
| MSSQL AG '{#GROUP_NAME}': All replicas unhealthy | None of the availability replicas have a healthy synchronization. | last(/MSSQL by ODBC/mssql.synchronization_health["{#GROUP_NAME}"])=0 | DISASTER 🔥 | MSSQL AG '{#GROUP_NAME}': Synchronization health |
| MSSQL AG '{#GROUP_NAME}': Some replicas unhealthy | The synchronization health of some, but not all, availability replicas is healthy. | last(/MSSQL by ODBC/mssql.synchronization_health["{#GROUP_NAME}"])=1 | HIGH ⛔ | MSSQL AG '{#GROUP_NAME}': Synchronization health |
Discovery rule №2
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Database discovery | Scanning databases in DBMS. | ODBC | 1h | db.odbc.discovery[dbname,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL DB '{#DBNAME}': Last diff backup duration | Duration of the last differential backup. | DEPENDENT | - | mssql.backup.diff.duration["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Last diff backup (time ago) | The amount of time since the last differential backup. | DEPENDENT | - | mssql.backup.diff["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Last full backup duration | Duration of the last full backup. | DEPENDENT | - | mssql.backup.full.duration["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Last full backup (time ago) | The amount of time since the last full backup. | DEPENDENT | - | mssql.backup.full["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Last log backup duration | Duration of the last log backup. | DEPENDENT | - | mssql.backup.log.duration["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Last log backup | The amount of time since the last log backup. | DEPENDENT | - | mssql.backup.log["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Active transactions | Number of active transactions for the database. | DEPENDENT | - | mssql.db.active_transactions["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Data file size | Cumulative size of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb. | DEPENDENT | - | mssql.db.data_files_size["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log bytes flushed per second | Total number of log bytes flushed per second. Useful for determining trends and utilization of the transaction log. | DEPENDENT | - | mssql.db.log_bytes_flushed_sec.rate["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log file size | Cumulative size of all the transaction log files in the database. | DEPENDENT | - | mssql.db.log_files_size["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log file used size | Cumulative used size of all the log files in the database. | DEPENDENT | - | mssql.db.log_files_used_size["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log flushes per second | Number of log flushes per second. | DEPENDENT | - | mssql.db.log_flushes_sec.rate["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log flush waits per second | Number of commits per second waiting for the log flush. | DEPENDENT | - | mssql.db.log_flush_waits_sec.rate["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log flush wait time | Total wait time (in milliseconds) to flush the log. On an AlwaysOn secondary database, this value indicates the wait time for log records to be hardened to disk. | DEPENDENT | - | mssql.db.log_flush_wait_time["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log growths | Total number of times the transaction log for the database has been expanded. | DEPENDENT | - | mssql.db.log_growths["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log shrinks | Total number of times the transaction log for the database has been shrunk. | DEPENDENT | - | mssql.db.log_shrinks["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Log truncations | Number of times the transaction log has been shrunk. | DEPENDENT | - | mssql.db.log_truncations["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Percent log used | Percentage of space in the log that is in use. | DEPENDENT | - | mssql.db.percent_log_used["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': State | 0 = ONLINE 1 = RESTORING 2 = RECOVERING | SQL Server 2008 and later 3 = RECOVERY_PENDING | SQL Server 2008 and later 4 = SUSPECT 5 = EMERGENCY | SQL Server 2008 and later 6 = OFFLINE | SQL Server 2008 and later 7 = COPYING | Azure SQL Database Active Geo-Replication 10 = OFFLINE_SECONDARY | Azure SQL Database Active Geo-Replication | DEPENDENT | - | mssql.db.state["{#DBNAME}"] |
| MSSQL DB '{#DBNAME}': Transactions per second | Number of transactions started for the database per second. | DEPENDENT | - | mssql.db.transactions_sec.rate["{#DBNAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL DB '{#DBNAME}': Diff backup is old | The differential backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.diff["{#DBNAME}"])>{$MSSQL.BACKUP_DIFF.CRIT:"{#DBNAME}"} | HIGH ⛔ | MSSQL DB '{#DBNAME}': Last diff backup (time ago) |
| MSSQL DB '{#DBNAME}': Diff backup is old | The differential backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.diff["{#DBNAME}"])>{$MSSQL.BACKUP_DIFF.WARN:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Last diff backup (time ago) |
| MSSQL DB '{#DBNAME}': Full backup is old | The full backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.full["{#DBNAME}"])>{$MSSQL.BACKUP_FULL.CRIT:"{#DBNAME}"} | HIGH ⛔ | MSSQL DB '{#DBNAME}': Last full backup (time ago) |
| MSSQL DB '{#DBNAME}': Full backup is old | The full backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.full["{#DBNAME}"])>{$MSSQL.BACKUP_FULL.WARN:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Last full backup (time ago) |
| MSSQL DB '{#DBNAME}': Log backup is old | The log backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.log["{#DBNAME}"])>{$MSSQL.BACKUP_LOG.CRIT:"{#DBNAME}"} | HIGH ⛔ | MSSQL DB '{#DBNAME}': Last log backup |
| MSSQL DB '{#DBNAME}': Log backup is old | The log backup has not been executed for a long time. | last(/MSSQL by ODBC/mssql.backup.log["{#DBNAME}"])>{$MSSQL.BACKUP_LOG.WARN:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Last log backup |
| MSSQL DB '{#DBNAME}': Number of commits waiting for the log flush is high | Too many commits are waiting for the log flush. | min(/MSSQL by ODBC/mssql.db.log_flush_waits_sec.rate["{#DBNAME}"],5m)>{$MSSQL.LOG_FLUSH_WAITS.MAX:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Log flush waits per second |
| MSSQL DB '{#DBNAME}': Total wait time to flush the log is high | The wait time to flush the log is too long. | min(/MSSQL by ODBC/mssql.db.log_flush_wait_time["{#DBNAME}"],5m)>{$MSSQL.LOG_FLUSH_WAIT_TIME.MAX:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Log flush wait time |
| MSSQL DB '{#DBNAME}': Percent of log using is high | There's not enough space left in the log. | min(/MSSQL by ODBC/mssql.db.percent_log_used["{#DBNAME}"],5m)>{$MSSQL.PERCENT_LOG_USED.MAX:"{#DBNAME}"} | WARNING 📢 | MSSQL DB '{#DBNAME}': Percent log used |
| MSSQL DB '{#DBNAME}': State is {ITEM.VALUE} | The DB has a non-working state. | last(/MSSQL by ODBC/mssql.db.state["{#DBNAME}"])>1 | HIGH ⛔ | MSSQL DB '{#DBNAME}': State |
Discovery rule №3
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Job discovery | Scanning jobs in DBMS. | ODBC | 1h | db.odbc.discovery[jobname,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL Job '{#JOBNAME}': Last run date-time | The last date-time of the job run. | DEPENDENT | - | mssql.job.lastrundatetime["{#JOBNAME}"] |
| MSSQL Job '{#JOBNAME}': Last run status message | The informational message about the last run of the job. | DEPENDENT | - | mssql.job.lastrunstatusmessage["{#JOBNAME}"] |
| MSSQL Job '{#JOBNAME}': Next run date-time | The next date-time of the job run. | DEPENDENT | - | mssql.job.nextrundatetime["{#JOBNAME}"] |
| MSSQL Job '{#JOBNAME}': Run status | The job status possible values: 0 ⇒ Failed 1 ⇒ Succeeded 2 ⇒ Retry 3 ⇒ Canceled 4 ⇒ Running | DEPENDENT | - | mssql.job.runstatus["{#JOBNAME}"] |
| MSSQL Job '{#JOBNAME}': Run duration | Duration of the last run job. | DEPENDENT | - | mssql.job.run_duration["{#JOBNAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL Job '{#JOBNAME}': Failed to run | The last run of the job has failed. | last(/MSSQL by ODBC/mssql.job.runstatus["{#JOBNAME}"])=0 | WARNING 📢 | MSSQL Job '{#JOBNAME}': Run status |
| MSSQL Job '{#JOBNAME}': Job duration is high | The job is taking too long. | last(/MSSQL by ODBC/mssql.job.run_duration["{#JOBNAME}"])>{$MSSQL.BACKUP_DURATION.WARN:"{#JOBNAME}"} | WARNING 📢 | MSSQL Job '{#JOBNAME}': Run duration |
Discovery rule №4
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Local database discovery | Discovery of the local availability databases. | ODBC | 1h | db.odbc.discovery[local_db,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': Get local DB states | Getting the states of the local availability database. | ODBC | - | db.odbc.get["{#GROUP_NAME}_{#DBNAME}_local_db.states","{$MSSQL.DSN}"] |
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': Suspended | Database state: 0 = Resumed 1 = Suspended | DEPENDENT | - | mssql.local_db.is_suspended["{#DBNAME}"] |
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': State | 0 = Online 1 = Restoring 2 = Recovering 3 = Recovery pending 4 = Suspect 5 = Emergency 6 = Offline | DEPENDENT | - | mssql.local_db.state["{#DBNAME}"] |
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': Synchronization health | Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica and the availability mode of the availability replica (synchronous-commit or asynchronous-commit mode): 0 = Not healthy. The synchronization_state of the database is 0 (NOT SYNCHRONIZING). 1 = Partially healthy. A database on a synchronous-commit availability replica is considered partially healthy if synchronization_state is 1 (SYNCHRONIZING). 2 = Healthy. A database on an synchronous-commit availability replica is considered healthy if synchronization_state is 2 (SYNCHRONIZED), and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 (SYNCHRONIZING). | DEPENDENT | - | mssql.local_db.synchronization_health["{#DBNAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': "{#DBNAME}" is {ITEM.VALUE} | The local availability database has a non-working state. | last(/MSSQL by ODBC/mssql.local_db.state["{#DBNAME}"])>0 | WARNING 📢 | MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': State |
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': "{#DBNAME}" is Not healthy | The synchronization state of the local availability database is NOT SYNCHRONIZING. | last(/MSSQL by ODBC/mssql.local_db.synchronization_health["{#DBNAME}"])=0 | HIGH ⛔ | MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': Synchronization health |
| MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': "{#DBNAME}" is Partially healthy | A database on a synchronous-commit availability replica is considered partially healthy if synchronization state is SYNCHRONIZING. | last(/MSSQL by ODBC/mssql.local_db.synchronization_health["{#DBNAME}"])=1 | AVERAGE ⚠ | MSSQL AG '{#GROUP_NAME}' Local DB '{#DBNAME}': Synchronization health |
Discovery rule №5
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Mirroring discovery | To see the row for a database other than master or tempdb, you must either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE permission in the master database. To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role. | ODBC | 1h | db.odbc.discovery[mirrors,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL Mirroring '{#DBNAME}': Get the mirror state | Getting mirrors state | ODBC | - | db.odbc.get["{#DBNAME}_mirroring_state","{$MSSQL.DSN}"] |
| MSSQL Mirroring '{#DBNAME}': Role | Current role of the local database plays in the database mirroring session. 1 = Principal 2 = Mirror | DEPENDENT | - | mssql.mirroring.role["{#DBNAME}"] |
| MSSQL Mirroring '{#DBNAME}': Role sequence | The number of times that mirroring partners have switched the principal and mirror roles due to a failover or forced service. | DEPENDENT | - | mssql.mirroring.role_sequence["{#DBNAME}"] |
| MSSQL Mirroring '{#DBNAME}': Safety level | Safety setting for updates on the mirror database: 0 = Unknown state 1 = Off [asynchronous] 2 = Full [synchronous] | DEPENDENT | - | mssql.mirroring.safety_level["{#DBNAME}"] |
| MSSQL Mirroring '{#DBNAME}': State | State of the mirror database and of the database mirroring session. 0 = Suspended 1 = Disconnected from the other partner 2 = Synchronizing 3 = Pending Failover 4 = Synchronized 5 = The partners are not synchronized. Failover is not possible now. 6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for the failover, see Database Mirroring Operating Modes. | DEPENDENT | - | mssql.mirroring.state["{#DBNAME}"] |
| MSSQL Mirroring '{#DBNAME}': Witness state | State of the witness in the database mirroring session of the database: 0 = Unknown 1 = Connected 2 = Disconnected | DEPENDENT | - | mssql.mirroring.witness_state["{#DBNAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL Mirroring '{#DBNAME}': "{#DBNAME}" is {ITEM.VALUE} | The state of the mirror database and of the database mirroring session is "Pending Failover". | last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])=3 | WARNING 📢 | MSSQL Mirroring '{#DBNAME}': State |
| MSSQL Mirroring '{#DBNAME}': "{#DBNAME}" is {ITEM.VALUE} | The state of the mirror database and of the database mirroring session is "Not synchronized". The partners are not synchronized. A failover is not possible now. | last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])=5 | HIGH ⛔ | MSSQL Mirroring '{#DBNAME}': State |
| MSSQL Mirroring '{#DBNAME}': "{#DBNAME}" is {ITEM.VALUE} | The state of the mirror database and of the database mirroring session is "Suspended", "Disconnected from the other partner", or "Synchronizing". | last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])>=0 and last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])<=2 | INFO 🔔 | MSSQL Mirroring '{#DBNAME}': State |
| MSSQL Mirroring '{#DBNAME}': "{#DBNAME}" Witness is disconnected | The state of the witness in the database mirroring session of the database is "Disconnected". | last(/MSSQL by ODBC/mssql.mirroring.witness_state["{#DBNAME}"])=2 | WARNING 📢 | MSSQL Mirroring '{#DBNAME}': Witness state |
Discovery rule №6
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Non-local database discovery | Discovery of the non-local (not local to the SQL Server instance) availability databases. | ODBC | 1h | db.odbc.discovery[non-local_db,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Get non-local DB states | Getting the states of the non-local availability database. | ODBC | - | db.odbc.get["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}_non-local_db.states","{$MSSQL.DSN}"] |
| MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Log queue size | Amount of the log records of the primary database that has not been sent to the secondary databases. | DEPENDENT | - | mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"] |
| MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Redo log queue size | Amount of log records in the log files of the secondary replica that has not yet been redone. | DEPENDENT | - | mssql.non-local_db.redo_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Log queue size is growing | The log records of the primary database are not sent to the secondary databases. | last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#1)>last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#2) and last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#2)>last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#3) | HIGH ⛔ | MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Log queue size |
| MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Redo log queue size is growing | The log records in the log files of the secondary replica have not yet been redone. | last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#1)>last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#2) and last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#2)>last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}{#REPLICA_NAME}{#DBNAME}"],#3) | HIGH ⛔ | MSSQL AG '{#GROUP_NAME}' Non-Local DB '{#REPLICA_NAME}{#DBNAME}': Redo log queue size |
Discovery rule №7
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| Replication discovery | Discovery of the database replicas. | ODBC | 1h | db.odbc.discovery[replicas,"{$MSSQL.DSN}"] |
Item prototypes
| Name | Description | Type | Interval | Key and additional info |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Get the replica state | Getting the database replica states. | ODBC | - | db.odbc.get["{#GROUP_NAME}_{#REPLICA_NAME}_replica.state","{$MSSQL.DSN}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Connected state | Whether a secondary replica is currently connected to the primary replica: 0 : Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role: On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect; On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica. 1 : Connected. Each primary replica tracks the connection state for every secondary replica in the same availability group. Secondary replicas track the connection state of only the primary replica. | DEPENDENT | - | mssql.replica.connected_state["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Is local | Whether the replica is local: 0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. This value occurs only on the primary replica location. 1 = Indicates a local replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs. | DEPENDENT | - | mssql.replica.is_local["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Join state | 0 = Not joined 1 = Joined, standalone instance 2 = Joined, failover cluster instance | DEPENDENT | - | mssql.replica.join_state["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Operational state | Current operational state of the replica: 0 = Pending failover 1 = Pending 2 = Online 3 = Offline 4 = Failed 5 = Failed, no quorum 6 = Not local | DEPENDENT | - | mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Recovery health | Rollup of the database_state column of the sys.dm_hadr_database_replica_states dynamic management view: 0 : In progress. At least one joined database has a database state other than ONLINE (database_state is not 0). 1 : Online. All the joined databases have a database state of ONLINE (database_state is 0). | DEPENDENT | - | mssql.replica.recovery_health["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Role | Current Always On availability groups role of a local replica or a connected remote replica: 0 = Resolving 1 = Primary 2 = Secondary | DEPENDENT | - | mssql.replica.role["{#GROUP_NAME}_{#REPLICA_NAME}"] |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Sync health | Reflects a rollup of the database synchronization state (synchronization_state)of all joined availability databases (also known as replicas) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state of the databases on the replica: 0 : Not healthy. At least one joined database is in the NOT SYNCHRONIZING state. 1 : Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing. 2 : Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing. | DEPENDENT | - | mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"] |
Trigger prototypes
| Name | Description | Expression | Priority | Dependencies |
|---|---|---|---|---|
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} is {ITEM.VALUE} | The operational state of the replica in a given availability group is "Pending" or "Offline". | last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}{#REPLICA_NAME}"])=0 or last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}{#REPLICA_NAME}"])=1 or last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=3 | WARNING 📢 | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Operational state |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} is {ITEM.VALUE} | The operational state of the replica in a given availability group is "Failed". | last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=4 | AVERAGE ⚠ | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Operational state |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} is {ITEM.VALUE} | The operational state of the replica in a given availability group is "Failed, no quorum". | last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=5 | HIGH ⛔ | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Operational state |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} Recovery in progress | At least one joined database has a database state other than ONLINE. | last(/MSSQL by ODBC/mssql.replica.recovery_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=0 | INFO 🔔 | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Recovery health |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} is Not healthy | At least one joined database is in the NOT SYNCHRONIZING state. | last(/MSSQL by ODBC/mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=0 | AVERAGE ⚠ | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Sync health |
| MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': {#REPLICA_NAME} is Partially healthy | Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing. | last(/MSSQL by ODBC/mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=1 | WARNING 📢 | MSSQL AG '{#GROUP_NAME}' Replica '{#REPLICA_NAME}': Sync health |