MS SQL 监控错误日志的告警信息

简介: 原文:MS SQL 监控错误日志的告警信息    SQL Server的错误消息(Error Message)按照消息的严重级别一共划分25个等级,级别越高,表示严重性也越高。但是如果你统计sys.messages,你会发现,实际上只有16(SQL SERVER 2008/2012)或17个(SQL SERVER 2005)个级别。
原文: MS SQL 监控错误日志的告警信息

    SQL Server的错误消息(Error Message)按照消息的严重级别一共划分25个等级,级别越高,表示严重性也越高。但是如果你统计sys.messages,你会发现,实际上只有16(SQL SERVER 2008/2012)或17个(SQL SERVER 2005)个级别。猜测应该是一些留作扩展用,一些留作用户自定义错误消息的级别。

 

sys.messages中有个字段is_event_logged,取值为1时表示出现错误时将消息记入事件日志。 对于 message_id 中的所有消息语言,此参数都是相同的。所以也就是说有些错误信息(Error Message)会写入事件日志,有些就不会写入错误日志。因此监控错误日志时,我们只能监控那些写入错误日志的错误信息。一般而言,我们应该监控严重级别在16~25之间的错误信息,另外严重级别14的也应该监控(主要是Error: 18456, Severity: 14)。

   监控错误日志有很多方式,下面介绍一种非常简单、有效的监控错误日志的方法.我们可以通过sp_add_alert创建一个警报(Alerts),如下所示,我们创建一个名称为SQL Server Severity Event 14的警报(配置前需要配置邮件、ProfileName,Operators,下面脚本也要根据具体情况调整)。关于创建警报的具体信息,可以参考http://msdn.microsoft.com/zh-cn/library/ms189531.aspx

 

USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Severity Event 14')
 
 EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 14'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 14',
  @message_id=0,
  @severity=14,
  @enabled=1,
  @delay_between_responses=60,
  @include_event_description_in=1,
  @category_name=N'DBA_MONITORING',
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 14', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO

如下所示,在UAT服务器,我故意用dw账号错误密码登录数据库,一分钟后,我立马回收到一封告警邮件

DATE/TIME: ;        2014/10/28 9:21:42
 
DESCRIPTION: ;    Login failed for user 'dw'. Reason: Password did not match that for the login provided. [CLIENT: 192.xxx.xxx.xxx]
 
COMMENT: ;        (None)
 
JOB ;RUN:              (None)

 

SQL Server 错误日志

 

 

Windows事件日志

 

我们依次建立16-25级别的告警来监控数据库错误日志的错误信息。如下所示:

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Severity Event 16')
 
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 16'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 16', 
        @message_id=0, 
        @severity=16, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 16', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 17')
EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 17'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 17', 
        @message_id=0, 
        @severity=17, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 17', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 18')
 
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 18'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 18', 
        @message_id=0, 
        @severity=18, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 18', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 19')
 
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 19'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 19', 
        @message_id=0, 
        @severity=19, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 19', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 20')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 20'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 20', 
        @message_id=0, 
        @severity=20, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 20', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 21')
 
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 21'
GO
 
USE [msdb]
GO
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 21', 
        @message_id=0, 
        @severity=21, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 21', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 22')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 22'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 22', 
        @message_id=0, 
        @severity=22, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 22', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 23')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 23'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 23', 
        @message_id=0, 
        @severity=23, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 23', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 23')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 23'
GO
 
USE [msdb]
GO
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 23', 
        @message_id=0, 
        @severity=23, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 23', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 24')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 24'
GO
 
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 24', 
        @message_id=0, 
        @severity=24, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 24', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 
 
 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'SQL Server Severity Event 25')
    
        EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 25'
GO
 
 
 
 
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 25', 
        @message_id=0, 
        @severity=25, 
        @enabled=1, 
        @delay_between_responses=60, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 25', @operator_name=N'YourSQLDba_Operator', @notification_method = 1
GO
 

 

执行完上面脚本后,就会建立下面几个告警。当数据库的错误日志出现这些级别的错误信息时,就会收到告警邮件。相当的方便、简单、高效。

 

相关实践学习
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
【涂鸦即艺术】基于云应用开发平台CAP部署AI实时生图绘板
目录
相关文章
|
SQL 传感器 人工智能
生成更智能,调试更轻松,SLS SQL Copilot 焕新登场!
阿里云日志服务(SLS)推出智能分析助手 SLS SQL Copilot,融合 AI 技术与日志分析最佳实践,将自然语言转换为 SQL 查询,降低使用门槛,提升查询效率。其具备原生集成、智能语义理解与高效执行能力,助力用户快速洞察日志数据价值,实现智能化日志分析新体验。
828 1
|
SQL 存储 监控
SQL日志优化策略:提升数据库日志记录效率
通过以上方法结合起来运行调整方案, 可以显著地提升SQL环境下面向各种搜索引擎服务平台所需要满足标准条件下之数据库登记作业流程综合表现; 同时还能确保系统稳健运行并满越用户体验预期目标.
602 6
|
存储 监控 API
【Azure App Service】分享使用Python Code获取App Service的服务器日志记录管理配置信息
本文介绍了如何通过Python代码获取App Service中“Web服务器日志记录”的配置状态。借助`azure-mgmt-web` SDK,可通过初始化`WebSiteManagementClient`对象、调用`get_configuration`方法来查看`http_logging_enabled`的值,从而判断日志记录是否启用及存储方式(关闭、存储或文件系统)。示例代码详细展示了实现步骤,并附有执行结果与官方文档参考链接,帮助开发者快速定位和解决问题。
491 22
|
SQL 缓存
【YashanDB知识库】YashanDB run.log中有slow log queue is full信息
【YashanDB知识库】YashanDB run.log中有slow log queue is full信息
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
369 2
|
SQL 数据库
为什么 SQL 日志文件很大,我应该如何处理?
为什么 SQL 日志文件很大,我应该如何处理?
|
SQL 监控 安全
员工上网行为监控软件:SQL 在数据查询监控中的应用解析
在数字化办公环境中,员工上网行为监控软件对企业网络安全和管理至关重要。通过 SQL 查询和分析数据库中的数据,企业可以精准了解员工的上网行为,包括基础查询、复杂条件查询、数据统计与分析等,从而提高网络管理和安全防护的效率。
533 0