MS SQL 监控错误日志的告警信息-低调大师

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

2018-08-05 656

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

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

微信关注我们

原文链接：https://yq.aliyun.com/articles/678031

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

SQL Server删除distribution数据库

原文: SQL Server删除distribution数据库在数据库服务器删除复制（发布订阅）后，如何删除掉数据库distribution呢？如果你通过SSMS工具去删除数据库distribution，你会发现根本没有删除选项。下面介绍一下删除distribution的步骤步骤1：查看相关订阅服务器的信息，如果还存在对应的订阅服务器，从注册的服务器中删除订阅服务器名称 exec sp_helpsubscriberinfo ; go exec sp_dropsubscriber 'SubscriberName'; go 步骤2：查看分发服务器的发布服务器的属性 exec sp_helpdistpublisher; 步骤3：删除分发发布服务器 exec sp_dropdistpublisher @publisher = 'PublisherName' 步骤4：使用sp_dropdistributiondb删除数据库distribution use master; go exec sp_dropdistributiondb @database = N'distr...

2018-08-06

667

原文: SQL Server删除distribution数据库二以前总结过一遍博文SQL Server删除distribution数据库，里面介绍了如何删除distribution数据库。今天介绍一个删除distribution的特殊案例，在这之前，我不知道这个服务器上的Replication被如何折腾过，在SSMS管理界面的Local Publications和Local Subscriptions里面已经看不到任何关于发布、订阅相关的内容。但是使用命令EXEC sp_helpdistpublisher会看到使用分发服务器的发布服务器的属性下面SQL的参数@publisher对应上面name的值，此处用xxxx替代 USE master; GO exec sp_dropdistpublisher @publisher = 'xxxx' 我们通过下面SQL，发现其在数据库（暂且用A表示）A上面做过发布，执行下面SQL时，报错误，因为这个数据库上有一个数据库级别的触发器safety，禁止任何DDL操作，首先禁用这个触发器后，执行下面SQL，就会将下面截图部分触发...

2018-08-06

636

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。