SQL Server Alerts and Monitoring

These alerts and monitoring will require action to be done to remediate potential issues.

Alerts

• Utilize proactive alerting to prevent outages.
• Alerts should be configured for low disk space, database growth over % of current size, cluster failovers, failed jobs, failed SQL Server Services, prolonged high CPU utilization, failed database backups and any other outages.
• When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
• Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
• Create SQL Server alerts to notify the DBAs, Developers or other support personnel of potential problems, and have them e-mailed to the appropriate team to take action as needed.
• Create default alerts for severities 16 through 25.
• SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.

The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘DB Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,

@message_id=0,

@severity=16,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N’00000000-0000-0000-0000-000000000000′

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’DB Team’, @notification_method = 7

GO

Monitoring

• Perform daily verification that all scheduled jobs have run successfully.
• Confirm that backups have been made and successfully saved to a secure location.
• Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should have 20% or more of free space.
• Use Management Studio or Profiler to help monitor and identify blocking issues.
• Throughout the day, periodically monitor your server’s performance. Use System Monitor, Profiler, DMVs, or the SQL Server Performance Data Collector.