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

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

    clipboard

    clipboard[1]

    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账号错误密码登录数据库，一分钟后，我立马回收到一封告警邮件

    clipboard[2]

    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 错误日志

     

    clipboard[3]

     

    Windows事件日志

    clipboard[4]

     

    我们依次建立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
     

     

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

    clipboard[5]

     

  • 相关阅读:
    第一阶段冲刺第三天
    第一阶段冲刺第二天
    第一阶段冲刺第一天
    典型用户和场景
    第十周学习进度条
    第九周学习进度条
    会议视频
    课堂练习……找水王
    小组项目需求——NABCD
    第二阶段个人总结三
  • 原文地址：https://www.cnblogs.com/kerrycode/p/4056491.html
Copyright © 2020-2023  润新知