代码改变世界

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

2014-10-28 12:09  潇湘隐者  阅读(3571)  评论(9编辑  收藏

    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]