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

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

2018-08-05 818

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-05

799

原文: 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-05

762

资源下载

更多资源

Mario

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

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。

WebStorm

WebStorm 是jetbrains公司旗下一款JavaScript 开发工具。目前已经被广大中国JS开发者誉为“Web前端开发神器”、“最强大的HTML5编辑器”、“最智能的JavaScript IDE”等。与IntelliJ IDEA同源，继承了IntelliJ IDEA强大的JS部分的功能。