English

Español

English

Español

Alerts ​

0. AlertsSQL Server

UID: alertsVersion: 1.3.3 Panels: ~10 Datasource: MSSQL

Purpose ​

This dashboard centralizes the critical alerts of the Coyote monitoring system, providing:

  • System uptime monitoring
  • Disk space status
  • User connections
  • Transaction log size
  • SQL services status
  • Block indicators

Configured Alerts ​

System Up Time ​

System Up Time timeseries

System uptime has dropped below 24 hours.

Alert condition: The system may have experienced a restart, scheduled maintenance, or an unexpected outage.

Importance: An unexpected restart may indicate hardware problems, uncontrolled automatic updates, critical operating system errors, or power failures. If not investigated, recurring restarts may occur affecting service availability, loss of in-flight transactions, and data corruption if the shutdown was not clean.

Response Runbook

Diagnosis:

  1. Review Windows Event Viewer under System > search for events with ID 1074 (planned shutdown) or 6008 (unexpected shutdown)
  2. Check SQL Server logs for errors prior to restart
  3. Verify if there was a pending Windows Update
  4. Review hardware: temperature, voltage, disks (SMART status)

Remediation:

  1. If it was planned maintenance: document and close alert
  2. If it was unexpected: review memory dumps in C:\Windows\Minidump
  3. Verify database integrity with DBCC CHECKDB
  4. Confirm all SQL services are running
  5. Validate that SQL Agent jobs resumed correctly

Escalation:

  • Do not escalate: If it was documented planned maintenance
  • Escalate to Level 2: If the restart was unexpected and there is no clear cause in logs
  • Escalate to Level 3/Infrastructure: If there are signs of hardware failure or recurring restarts (more than 2 in a week)

Recommended actions:

  1. Verify the cause of the restart
  2. Review Windows event logs
  3. Confirm all services are functioning

Disk Free Space ​

Disk Free Space bargauge

Free disk space has dropped below 20%.

Alert condition: Available space may indicate accelerated growth, inefficient usage, or unexpected data accumulation.

Importance: Insufficient disk space is one of the most common causes of unplanned SQL Server outages. Without available space, databases cannot grow, transaction logs fill up causing transactions to fail, and backups cannot complete. In extreme cases, SQL Server may stop completely, causing loss of service for all dependent applications.

Thresholds:

Free SpaceColorStatus
< 10%RedCritical
10% - 20%OrangeWarning
> 20%GreenNormal

Response Runbook

Diagnosis:

  1. Identify which disk is affected using the [Maintenance].[vwDisksFreeSpace] view
  2. Analyze what is consuming space: data, logs, backups, tempdb
  3. Review growth trend over the last 30 days
  4. Check if there are old backup files that haven't been purged

Immediate remediation (< 10%):

  1. Free up space by deleting old backups (maintain minimum required retention)
  2. Truncate transaction logs if there are recent log backups
  3. Empty system temporary folders
  4. Move backup files to secondary storage

Planned remediation (10-20%):

  1. Implement backup retention policies
  2. Configure automatic log shrink (with caution)
  3. Evaluate storage capacity expansion
  4. Review duplicate or unused indexes consuming space

Escalation:

  • Do not escalate: If space > 15% and there is a cleanup plan in progress
  • Escalate to Level 2: If space < 15% and cleanup does not free enough space
  • Escalate to Infrastructure/Storage: If space < 10% and urgent storage expansion is required
  • Escalate to Management: If growth indicates need for infrastructure investment

Recommended actions:

  1. Review disk usage
  2. Clean up unnecessary files
  3. Extend capacity if needed
  4. Verify log growth

User Connections ​

User Connections timeseries

Active connections have exceeded 25,000.

Alert condition: Excess connections may indicate:

  • Unusually high application activity
  • Connection pooling problems
  • Runaway process

Importance: SQL Server has a maximum limit of 32,767 connections. Reaching this limit prevents new users or applications from connecting, causing immediate errors in applications. Additionally, an excessive number of connections consumes server memory (each connection uses approximately 1-2 MB), can degrade overall performance, and makes system resource management difficult.

Thresholds:

ConnectionsStatus
< 25,000Normal
>= 25,000Critical (SQL limit: 32,767)

Response Runbook

Diagnosis:

  1. Identify applications with the most connections using sys.dm_exec_sessions grouped by program_name and host_name
  2. Check if there are orphaned connections or those in prolonged sleeping state
  3. Verify connection pooling configuration in .NET applications (Min/Max Pool Size)
  4. Analyze if there are batch processes running that open multiple connections

Remediation:

  1. Contact development teams of applications with the most connections
  2. Close inactive connections longer than X minutes using KILL (with caution)
  3. Review and adjust connection pooling configuration:
    • Reduce Max Pool Size in connection strings
    • Enable Connection Lifetime to recycle connections
  4. Implement retry logic with exponential backoff in applications
  5. Consider Resource Governor to limit connections per workload group

Escalation:

  • Do not escalate: If connections are between 20,000-25,000 and stable
  • Escalate to Development: If a specific application is identified as causing the problem
  • Escalate to Level 2: If connections exceed 28,000 and continue growing
  • Escalate to Management: If third-party (vendor) intervention is required to adjust applications

Recommended actions:

  1. Review workload patterns
  2. Verify connection pooling
  3. Identify applications with many connections

Used Log Size ​

Used Log Size timeseries

Transaction log size has exceeded 80 GB.

Alert condition: May occur due to:

  • Long-running transactions
  • Insufficient log backups
  • High-logging operations

Importance: An excessively large transaction log can fill the disk, causing the database to stop accepting transactions (error 9002). This completely stops write operations on the database. Additionally, log backups will take longer, restores will be slower, and if the log continues growing uncontrolled, it may indicate a more serious problem such as an orphaned transaction or broken replication.

Threshold:

Log SizeColorStatus
< 80 GBGreenNormal
>= 80 GBRedCritical

Response Runbook

Diagnosis:

  1. Identify which database has the large log using sys.master_files
  2. Verify the recovery model (FULL requires frequent log backups)
  3. Look for long-running active transactions with DBCC OPENTRAN
  4. Check if there is replication, mirroring, or AlwaysOn with delays
  5. Check the log_reuse_wait_desc status in sys.databases

Remediation:

  1. If log_reuse_wait_desc = 'LOG_BACKUP': execute log backup immediately
  2. If log_reuse_wait_desc = 'ACTIVE_TRANSACTION': identify and resolve the blocking transaction
  3. If log_reuse_wait_desc = 'REPLICATION': verify Log Reader Agent status
  4. After freeing space, consider log shrink (only if necessary)
  5. Adjust log backup frequency (every 15-30 minutes for active databases)

Escalation:

  • Do not escalate: If log backup resolves the issue and the log shrinks
  • Escalate to Level 2: If the log continues growing after backup or there is a blocking transaction that cannot be resolved
  • Escalate to Senior DBA: If related to replication or AlwaysOn
  • Escalate to Infrastructure: If the disk is at risk of filling before resolution

Recommended actions:

  1. Validate log backup jobs
  2. Monitor active transactions
  3. Ensure appropriate log maintenance

SQL Alerts Global ​

SQL Alerts Global table

Summary table of SQL alert status.

Indicators:

IndicatorOKKO
Blocked0>= 1
Long_Blocked0>= 1
SQL Agent Status0>= 1
SQL Mail Status0>= 1

Blocked / Long_Blocked ​

Importance: Blocks in SQL Server can cause application timeouts, performance degradation, and in severe cases, deadlocks that abort transactions. Prolonged blocks (Long_Blocked) are especially critical because they affect multiple users and may indicate application design issues or missing indexes.

Response Runbook - Blocks

Diagnosis:

  1. Identify the blocking chain using sys.dm_exec_requests and sys.dm_os_waiting_tasks
  2. Get the blocking process query with sys.dm_exec_sql_text
  3. Check if the block is due to lock escalation or specific page/row

Remediation:

  1. If it's a forgotten transaction: contact the user/application to commit or rollback
  2. If it's a batch process: consider running it during low-usage hours
  3. Implement READ_COMMITTED_SNAPSHOT to reduce read blocks
  4. Review indexes to reduce lock escalation
  5. As a last resort: KILL the blocking process (coordinating with the business)

Escalation for Blocks:

  • Do not escalate: Minor blocks under 30 seconds that resolve themselves
  • Escalate to Level 2: Blocks longer than 5 minutes or affecting multiple users
  • Escalate to Development: If the block is caused by poor application code
  • Escalate to Management: If a critical business process needs to be killed

SQL Agent Status ​

Importance: SQL Server Agent is responsible for running scheduled jobs including backups, index maintenance, and ETL processes. If the service is stopped, no jobs will run, which can result in missing backups (RPO non-compliance), outdated data in reports, and accumulation of maintenance tasks.

Response Runbook - SQL Agent

Diagnosis:

  1. Check the service status in Windows Services
  2. Review Windows Event Log for service errors
  3. Check the SQL Server Agent Error Log
  4. Verify that the service account has valid permissions

Remediation:

  1. Try restarting the SQL Server Agent service
  2. If startup fails, review logs to identify the cause
  3. Verify that the service account is not locked or password expired
  4. Check connectivity to the msdb database
  5. If it persists, restart SQL Server as a last resort (coordinating the impact)

Escalation for SQL Agent:

  • Do not escalate: If restarting the service resolves the issue
  • Escalate to Level 2: If the service does not start after restart
  • Escalate to Infrastructure: If there are service account or permission issues
  • Escalate to Management: If SQL Server restart is required during production hours

SQL Mail Status ​

Importance: Database Mail is the mechanism for sending alert notifications, scheduled reports, and job confirmations. If it's failing, the operations team will not receive critical alerts, which can result in undetected problems and SLA violations.

Response Runbook - SQL Mail

Diagnosis:

  1. Check Database Mail status with sysmail_help_status_sp
  2. Review the failed mail queue in sysmail_faileditems
  3. Check SMTP configuration in sysmail_help_account_sp
  4. Verify network connectivity to the SMTP server

Remediation:

  1. Restart Database Mail: EXEC msdb.dbo.sysmail_stop_sp; EXEC msdb.dbo.sysmail_start_sp;
  2. Verify that the SMTP server accepts connections from SQL Server
  3. Check for SMTP credential changes
  4. Review sending limits (some SMTP servers have rate limiting)
  5. Resend failed emails from the queue if necessary

Escalation for SQL Mail:

  • Do not escalate: If restarting Database Mail resolves the issue
  • Escalate to Infrastructure/Networks: If there are SMTP connectivity issues
  • Escalate to Level 2: If there are configuration or credential issues

Alert Color Codes ​

ColorCodeMeaning
Green#00db88 / #6be38dOK - Normal status
Orange#ff780aWarning - Warning
Magenta#ad21d8e8KO - Requires attention
Red#E02F44Critical - Immediate action

Alert Response Guide ​

High Priority ​

AlertImmediate Action
Disk < 10%Free space urgently
Log > 80 GBExecute log backup
User Connections > 25000Investigate applications

Medium Priority ​

AlertPlanned Action
Disk 10-20%Plan cleanup
Uptime < 24hInvestigate restart cause
SQL Agent KORestart service

Alert Configuration Best Practices ​

Avoiding Alert Fatigue ​

"Alert fatigue" occurs when the team receives so many notifications that they begin to ignore them, including critical ones. To avoid this:

  1. Classify alerts by severity: Not all alerts should notify the same way

    • Critical: Immediate notification (SMS, call, Teams)
    • Warning: Email with review within 4 hours
    • Informational: Dashboard only, no active notification

  2. Configure progressive thresholds: Escalate urgency based on severity

    • Disk 20%: Dashboard warning
    • Disk 15%: Email to team
    • Disk 10%: Teams notification + automatic ticket
    • Disk 5%: Call to on-call

  3. Group related alerts: A disk failure can generate multiple alerts (disk, log, backup). Configure suppression of secondary alerts.

  4. Review and adjust thresholds regularly: What was critical for a small server may be normal for a larger one.

MetricWarningCriticalNotes
Free disk< 20%< 10%Adjust based on total size
Transaction log> 50 GB> 80 GBDepends on transaction volume
Connections> 20,000> 25,000Consider normal baseline
Blocks> 30 sec> 5 minLong_Blocked is always critical
SQL CPU> 80% (5 min)> 95% (5 min)Sustained, not spikes
PLE< 300 sec< 100 secDepends on total memory

Documentation and Runbooks ​

  1. Keep runbooks updated: Each alert should have clear diagnosis and remediation steps
  2. Record actions taken: Document what was done to resolve each alert
  3. Incident post-mortem: After major incidents, review if alerts were effective
  4. Train the team: Ensure everyone knows the runbooks and when to escalate

Schedules and On-Call ​

  1. Define notification schedules: Some warning alerts can wait until business hours
  2. On-call rotation: Distribute the load to avoid burnout
  3. Automatic escalation: If a critical alert is not acknowledged in X minutes, escalate automatically

Views Used ​

ViewDescription
[Maintenance].[vwDisksFreeSpace]Free disk space
[Alert].[vwSQLAlertsGlobal]Global SQL alert status

Performance Counters Tables ​

CounterDescription
Seconds Since StartTime since system start
User ConnectionsActive connections
Log File(s) Used Size (KB)Used log size

Notification Configuration ​

Grafana alerts can be configured to send notifications through:

  • Email: Direct delivery to administrators
  • Teams/Slack: Notification channels
  • Webhook: Integration with external systems

Microsoft References ​

Official Monitoring Documentation ​

Space and Log Management ​

Blocks and Performance ​

SQL Server Agent ​

Database Mail ​

Best Practices ​

© 2025 Coyote Monitor by Aleson ITC