# 检查SQL Server Agent状态
EXEC xp_servicecontrol ‘querystate’, ‘SQLSERVERAGENT’;
GO

Current Service State:
RUNNING

# 启用SQL Server Agent
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

对于告警环境，需要确保SQL Server Agent服务正常运行，并且配置了正确的通知渠道。from SQLServer视频:www.itpux.com。

2.2 SQLServer告警策略设计

SQLServer告警策略设计应考虑以下因素：

1. 告警覆盖范围：确保覆盖所有关键数据库操作和系统状态

2. 告警阈值设置：根据系统实际情况设置合理的阈值

3. 告警通知方式：选择合适的通知方式，如电子邮件、短信等

4. 告警响应流程：建立明确的告警响应流程，确保问题及时处理

5. 告警抑制机制：避免重复告警和误报

更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 SQLServer告警配置实施

# 创建告警运算符
USE msdb;
GO

EXEC dbo.sp_add_operator
@name = N’DBA Team’,
@enabled = 1,
@email_address = N’dba@fgedu.net.cn’,
@pager_address = N’13800138000′,
@weekday_pager_start_time = 090000,
@weekday_pager_end_time = 180000,
@saturday_pager_start_time = 090000,
@saturday_pager_end_time = 120000,
@sunday_pager_start_time = 090000,
@sunday_pager_end_time = 120000,
@pager_days = 127;
GO

# 创建事件告警
EXEC dbo.sp_add_alert
@name = N’Database Backup Failed’,
@message_id = 3041,
@severity = 0,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1,
@notification_message = N’Database backup failed. Please check the backup job immediately.’,
@event_description_keyword = N’BACKUP failed’,
@job_id = NULL;
GO

# 创建性能条件告警
EXEC dbo.sp_add_alert
@name = N’High CPU Usage’,
@performance_condition = N’Processor(*)\% Processor Time > 80 for 5 minutes’,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1,
@notification_message = N’CPU usage is high. Please check the system performance.’,
@job_id = NULL;
GO

# 创建WMI事件告警
EXEC dbo.sp_add_alert
@name = N’Database Growth’,
@wmi_namespace = N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,
@wmi_query = N’SELECT * FROM DEADLOCK_GRAPH’,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1,
@notification_message = N’Deadlock detected. Please check the deadlock graph.’,
@job_id = NULL;
GO

# 配置数据库邮件
EXEC msdb.dbo.sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO

EXEC msdb.dbo.sp_configure ‘Database Mail XPs’, 1;
RECONFIGURE;
GO

# 创建数据库邮件配置文件
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = N’DBA Alert Profile’,
@description = N’Profile for DBA alerts’;
GO

# 创建数据库邮件账户
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = N’fgedu-mail’,
@description = N’Mail account for DBA alerts’,
@email_address = N’dba@fgedu.net.cn’,
@display_name = N’DBA Alert System’,
@replyto_address = N’dba@fgedu.net.cn’,
@mailserver_name = N’smtp.fgedu.net.cn’,
@mailserver_type = N’SMTP’,
@port = 25,
@username = N’dba@fgedu.net.cn’,
@password = N’password’,
@use_default_credentials = 0,
@enable_ssl = 0;
GO

# 将账户添加到配置文件
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = N’DBA Alert Profile’,
@account_name = N’fgedu-mail’,
@sequence_number = 1;
GO

# 设置SQL Server Agent使用的邮件配置文件
EXEC msdb.dbo.sp_set_sqlagent_properties
@email_save_in_sent_folder = 1,
@alert_replace_runtime_tokens = 1;
GO

EXEC msdb.dbo.sp_update_operator
@name = N’DBA Team’,
@email_address = N’dba@fgedu.net.cn’;
GO

# 为告警添加通知
EXEC msdb.dbo.sp_add_notification
@alert_name = N’Database Backup Failed’,
@operator_name = N’DBA Team’,
@notification_method = 1; — 1 = Email
GO

EXEC msdb.dbo.sp_add_notification
@alert_name = N’High CPU Usage’,
@operator_name = N’DBA Team’,
@notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification
@alert_name = N’Database Growth’,
@operator_name = N’DBA Team’,
@notification_method = 1;
GO

学习交流加群风哥QQ113257174。

Part04-生产案例与实战讲解

4.1 SQLServer告警配置实战案例

# 案例1：数据库备份失败告警

# 创建备份作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Backup fgedudb’,
@enabled = 1,
@description = N’Backup fgedudb database’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Backup fgedudb’,
@step_name = N’Full backup’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb TO DISK = ”/sqlserver/backup/fgedudb_full.bak” WITH COMPRESSION;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Daily Backup Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 230000;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Backup fgedudb’,
@schedule_name = N’Daily Backup Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Backup fgedudb’,
@server_name = N'(local)’;
GO

# 测试备份失败告警
# 模拟备份失败（删除备份目录）
EXEC xp_cmdshell ‘rmdir /s /q /sqlserver/backup’;
GO

# 运行备份作业
EXEC msdb.dbo.sp_start_job N’Backup fgedudb’;
GO

# 查看告警历史
SELECT
alert_name,
message_id,
severity,
notification_message,
date_modified
FROM msdb.dbo.sysalerts;
GO

# 案例2：磁盘空间不足告警

# 创建磁盘空间告警
EXEC dbo.sp_add_alert
@name = N’Low Disk Space’,
@performance_condition = N’LogicalDisk(*)\% Free Space < 15 for 10 minutes', @enabled = 1, @delay_between_responses = 300, @include_event_description_in = 1, @notification_message = N'Disk space is low. Please check the disk usage.', @job_id = NULL; GO # 为告警添加通知 EXEC msdb.dbo.sp_add_notification @alert_name = N'Low Disk Space', @operator_name = N'DBA Team', @notification_method = 1; GO # 案例3：死锁告警 # 创建死锁告警 EXEC dbo.sp_add_alert @name = N'Deadlock Detected', @message_id = 1205, @severity = 0, @enabled = 1, @delay_between_responses = 300, @include_event_description_in = 1, @notification_message = N'Deadlock detected. Please check the deadlock graph.', @job_id = NULL; GO # 为告警添加通知 EXEC msdb.dbo.sp_add_notification @alert_name = N'Deadlock Detected', @operator_name = N'DBA Team', @notification_method = 1; GO # 测试死锁告警 # 模拟死锁 -- 会话1 BEGIN TRANSACTION; UPDATE dbo.fgedu_user_info SET user_name = 'user1_updated' WHERE user_id = 1; -- 会话2 BEGIN TRANSACTION; UPDATE dbo.fgedu_order SET order_status = 'Processing' WHERE order_id = 1; -- 会话1 UPDATE dbo.fgedu_order SET order_status = 'Processing' WHERE order_id = 1; -- 会话2 UPDATE dbo.fgedu_user_info SET user_name = 'user1_updated' WHERE user_id = 1; # 查看告警历史 SELECT alert_name, message_id, severity, notification_message, date_modified FROM msdb.dbo.sysalerts; GO

# 创建告警自动化脚本
CREATE PROCEDURE dbo.usp_alert_automation
AS
BEGIN
— 检查告警状态
SELECT
a.name AS alert_name,
a.enabled AS alert_enabled,
o.name AS operator_name,
n.notification_method AS notification_method
FROM msdb.dbo.sysalerts a
LEFT JOIN msdb.dbo.sysnotifications n ON a.id = n.alert_id
LEFT JOIN msdb.dbo.sysoperators o ON n.operator_id = o.id;

— 测试数据库邮件
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N’DBA Alert Profile’,
@recipients = N’dba@fgedu.net.cn’,
@subject = N’SQL Server Alert Test’,
@body = N’This is a test email from SQL Server Alert System.’;

— 清理过期告警历史
EXEC msdb.dbo.sp_delete_jobhistory @oldest_date = DATEADD(day, -30, GETDATE());
END;
GO

# 创建自动化作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Alert Automation’,
@enabled = 1,
@description = N’Automate alert tasks’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Alert Automation’,
@step_name = N’Run automation’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.usp_alert_automation;’,
@retry_attempts = 3,
@retry_interval = 5;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’Alert Automation Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 60;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Alert Automation’,
@schedule_name = N’Alert Automation Schedule’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Alert Automation’,
@server_name = N'(local)’;
GO

# 查看告警配置
SELECT
a.name AS alert_name,
a.enabled AS alert_enabled,
a.alert_type AS alert_type,
a.message_id AS message_id,
a.severity AS severity,
a.performance_condition AS performance_condition,
o.name AS operator_name,
n.notification_method AS notification_method
FROM msdb.dbo.sysalerts a
LEFT JOIN msdb.dbo.sysnotifications n ON a.id = n.alert_id
LEFT JOIN msdb.dbo.sysoperators o ON n.operator_id = o.id;
GO

alert_name alert_enabled alert_type message_id severity performance_condition operator_name notification_method
———————– ————– ———– ———– ——— ——————————————- ————– ——————-
Database Backup Failed 1 1 3041 0 NULL DBA Team 1
High CPU Usage 1 2 0 0 Processor(*)\% Processor Time > 80 for 5 minutes DBA Team 1
Database Growth 1 3 0 0 NULL DBA Team 1
Low Disk Space 1 2 0 0 LogicalDisk(*)\% Free Space < 15 for 10 minutes DBA Team 1 Deadlock Detected 1 1 1205 0 NULL DBA Team 1

风哥提示：定期测试告警配置，确保告警能够正常触发和通知。from SQLServer视频:www.itpux.com。

Part05-风哥经验总结与分享

5.1 SQLServer告警配置最佳实践

1. 制定告警策略，明确告警范围和目标

2. 选择合适的告警类型，根据事件类型选择对应的告警方式

3. 设置合理的告警阈值，避免误报和漏报

4. 配置多种通知方式，确保告警能够及时送达

5. 建立告警响应流程，确保问题及时处理

6. 定期测试告警配置，确保告警能够正常触发

7. 建立告警文档，记录告警配置和操作流程

5.2 SQLServer告警常见问题与解决方案

# 问题1：告警不触发
# 解决方案：检查SQL Server Agent状态、告警配置、事件是否发生

# 问题2：告警通知不发送
# 解决方案：检查数据库邮件配置、网络连接、邮箱地址

# 问题3：告警误报频繁
# 解决方案：调整告警阈值，优化告警逻辑

# 问题4：告警信息不完整
# 解决方案：配置告警包含详细的事件描述

# 问题5：告警历史过多
# 解决方案：定期清理告警历史，设置合理的保留期限

# 查看告警历史
SELECT
alert_name,
message_id,
severity,
notification_message,
date_modified
FROM msdb.dbo.sysalerts;
GO

# 测试数据库邮件
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N’DBA Alert Profile’,
@recipients = N’dba@fgedu.net.cn’,
@subject = N’SQL Server Alert Test’,
@body = N’This is a test email from SQL Server Alert System.’;
GO

更多学习教程公众号风哥教程itpux_com。