当前位置: 澳门新濠3559 > 数据库 > 正文

并不是每个错误日志都会发送邮件出来,另外在

时间:2019-11-08 05:43来源:数据库
最近突然发现我们部署在数据库上面的告警(Alert),当错误日志里面出现错误时,并不是每个错误日志都会发送邮件出来。如下所示,设置了告警“SQLServer Severity Event 14” 日志传送(

最近突然发现我们部署在数据库上面的告警(Alert),当错误日志里面出现错误时,并不是每个错误日志都会发送邮件出来。如下所示,设置了告警“SQL Server Severity Event 14”

日志传送(log shipping)主要基于SQL Server代理,使用定时作业来完成,另外在配置日志传送之前必须要创建共享文件夹,用于辅助服务器访问。这里我们假设有数据库logTrans1需要进行日志传送,共享文件夹为“C:data”,在T-SQL中配置日志传送主要有以下几步操作:

USE [msdb]

 

(1)备份主数据库并在辅助服务器上还原主数据库的完整备份,初始化辅助数据库。具体操作如代码:

GO

USE [msdb]

澳门新濠3559 1backup database logTrans1 --在主数据库上备份 
澳门新濠3559 2to disk='c:logt.bak' 
澳门新濠3559 3--以下是将数据库还原到辅助数据库上 
澳门新濠3559 4restore database logTrans2 
澳门新濠3559 5from disk='c:logt.bak' 
澳门新濠3559 6with NORECOVERY, 
澳门新濠3559 7move 'logTrans' to 'c:logTrans2.mdf', 
澳门新濠3559 8move 'logTrans_log' to 'c:logTrans2.ldf' 

--创建操作员7*24值班

GO

(2)在主服务器上,执行sp_add_log_shipping_primary_database以添加主数据库。存储过程将返回备份作业ID和主ID,具体SQL脚本如代码:

/****** 对象:  Operator [qiu_zhengqiang]    脚本日期: 06/08/2012 12:58:42 ******/

 

DECLARE @LS_BackupJobId AS uniqueidentifier 
DECLARE @LS_PrimaryId AS uniqueidentifier 
EXEC master.dbo.sp_add_log_shipping_primary_database 
@database = N'logTrans1' 
,@backup_directory = N'D:data' 
,@backup_share = N'\10.101.10.66data' 
,@backup_job_name = N'LSBackup_logTrans1' 
,@backup_retention_period = 1440 
,@monitor_server = N'localhost' 
,@monitor_server_security_mode = 1 
,@backup_threshold = 60 
,@threshold_alert_enabled = 0 
,@history_retention_period = 1440 
,@backup_job_id = @LS_BackupJobId OUTPUT 
,@primary_id = @LS_PrimaryId OUTPUT 
,@overwrite = 1 

--EXEC msdb.dbo.sp_delete_operator @name=N'qiu_zhengqiang'

 

(3)在主服务器上,执行sp_add_jobschedule以添加使用备份作业的计划。为了能够尽快看到日志传送的效果,这里将日志备份的频率设置为2分钟一次。但是在实际生产环境中,一般是用不到这么高的执行频率的。添加计划的脚本如代码:

EXEC msdb. dbo.sp_add_operator

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)

澳门新濠3559 9DECLARE @schedule_id int 
澳门新濠3559 10EXEC msdb.dbo.sp_add_jobschedule @job_name =N'LSBackup_logTrans1', 
澳门新濠3559 11@name=N'BackupDBEvery2Min', 
澳门新濠3559 12@enabled=1, 
澳门新濠3559 13@freq_type=4, 
澳门新濠3559 14@freq_interval=1, 
澳门新濠3559 15@freq_subday_type=4, 
澳门新濠3559 16@freq_subday_interval=2, 
澳门新濠3559 17@freq_relative_interval=0, 
澳门新濠3559 18@freq_recurrence_factor=1, 
澳门新濠3559 19@active_start_date=20080622, 
澳门新濠3559 20@active_end_date=99991231, 
澳门新濠3559 21@active_start_time=0, 
澳门新濠3559 22@active_end_time=235959, 
澳门新濠3559 23@schedule_id = @schedule_id OUTPUT 
澳门新濠3559 24select @schedule_id 
澳门新濠3559 25

            @name =N'qiu_zhengqiang' , -- 操作员(通知收件人)的名称。该名称必须唯一,且不能含有百分比 (%) 字符。name 的数据类型为 sysname,无默认值。

BEGIN

【注意:sp_add_jobschedule存储过程是在msdb数据库中,在其他数据库中是没有该存储过程的。】

            @enabled =1,

 

(4)在监视服务器上,执行sp_add_log_shipping_alert_job以添加警报作业。此存储过程用于检查是否已在此服务器上创建了警报作业。如果警报作业不存在,此存储过程将创建警报作业并将其作业ID添加到log_shipping_monitor_alert表中。默认情况下,将启用警报作业并按计划每两分钟运行一次。添加警报作业如代码:

            @weekday_pager_start_time =80000,

EXEC msdb.dbo.sp_add_category

澳门新濠3559 26USE master 
澳门新濠3559 27GO 
澳门新濠3559 28EXEC sp_澳门新濠3559,add_log_shipping_alert_job; 

            @weekday_pager_end_time =80000,

    @class=N'ALERT',

(5)在主服务器上,启用备份作业。启用作业使用sp_update_job存储过程,只需要输入作业名并设置状态为1即可。具体SQL脚本如代码:

            @saturday_pager_start_time =80000,

    @type=N'NONE',

澳门新濠3559 29EXEC msdb.dbo.sp_update_job 
澳门新濠3559 30@job_name='LSBackup_logTrans1', 
澳门新濠3559 31@enabled=1 

            @saturday_pager_end_time =80000,

    @name=N'DBA_MONITORING' ;

(6)在辅助服务器上,执行sp_add_log_shipping_secondary_primary,提供主服务器和数据库的详细信息。此存储过程返回辅助ID以及复制和还原作业ID,具体SQL脚本如代码:

            @sunday_pager_start_time =80000,

 

澳门新濠3559 32DECLARE @LS_Secondary__CopyJobId uniqueidentifier 
澳门新濠3559 33DECLARE @LS_Secondary__RestoreJobId uniqueidentifier 
澳门新濠3559 34DECLARE @LS_Secondary__SecondaryId uniqueidentifier 
澳门新濠3559 35EXEC master.dbo.sp_add_log_shipping_secondary_primary 
澳门新濠3559 36@primary_server = N'10.101.10.66' 
澳门新濠3559 37,@primary_database = N'logTrans1' 
澳门新濠3559 38,@backup_source_directory = N'\10.101.10.66data' 
澳门新濠3559 39,@backup_destination_directory = N'D:log' 
澳门新濠3559 40,@copy_job_name = N'LSCopy_logTrans1' 
澳门新濠3559 41,@restore_job_name = N'LSRestore_logTrans2' 
澳门新濠3559 42,@file_retention_period = 1440 
澳门新濠3559 43,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
澳门新濠3559 44,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
澳门新濠3559 45,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

            @sunday_pager_end_time =80000,

END

(7)在辅助服务器上,执行sp_add_jobschedule以设置复制和还原作业的计划。这里一般将复制和还原作业计划的频率设置来和日志备份的作业频率相同,所以此处将这两个作业的频率设置为每2分钟执行一次,具体SQL脚本如代码:

            @pager_days =127, --从星期日到星期六 1+2+4+8+16+32+64

GO

澳门新濠3559 46DECLARE @schedule_id int 
澳门新濠3559 47--设置复制作业计划 
澳门新濠3559 48EXEC msdb.dbo.sp_add_jobschedule 
澳门新濠3559 49@job_name=N'LSCopy_logTrans1', 
澳门新濠3559 50@name=N'CopyEvery2Min', 
澳门新濠3559 51@enabled=1, 
澳门新濠3559 52@freq_type=4, 
澳门新濠3559 53@freq_interval=1, 
澳门新濠3559 54@freq_subday_type=4, 
澳门新濠3559 55@freq_subday_interval=2, 
澳门新濠3559 56@freq_relative_interval=0, 
澳门新濠3559 57@freq_recurrence_factor=1, 
澳门新濠3559 58@active_start_date=20080622, 
澳门新濠3559 59@active_end_date=99991231, 
澳门新濠3559 60@active_start_time=0, 
澳门新濠3559 61@active_end_time=235959, 
澳门新濠3559 62@schedule_id = @schedule_id OUTPUT 
澳门新濠3559 63select @schedule_id 
澳门新濠3559 64--设置还原作业的计划 
澳门新濠3559 65EXEC msdb.dbo.sp_add_jobschedule 
澳门新濠3559 66@job_name=N'LSCopy_logTrans1', 
澳门新濠3559 67@name=N'RestoreEvery2Min', 
澳门新濠3559 68@enabled=1, 
澳门新濠3559 69@freq_type=4, 
澳门新濠3559 70@freq_interval=1, 
澳门新濠3559 71@freq_subday_type=4, 
澳门新濠3559 72@freq_subday_interval=2, 
澳门新濠3559 73@freq_relative_interval=0, 
澳门新濠3559 74@freq_recurrence_factor=1, 
澳门新濠3559 75@active_start_date=20080622, 
澳门新濠3559 76@active_end_date=99991231, 
澳门新濠3559 77@active_start_time=0, 
澳门新濠3559 78@active_end_time=235959, 
澳门新濠3559 79@schedule_id = @schedule_id OUTPUT 
澳门新濠3559 80select @schedule_id 

            @email_address =N'qiuzhengqiang@qq.com'

 

(8)在辅助服务器上,执行sp_add_log_shipping_secondary_database以添加辅助数据库,具体操作脚本如代码:

go

IF EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'SQL Server Severity Event 14')

 

--创建邮件发送作业调度

 

澳门新濠3559 81EXEC master.dbo.sp_add_log_shipping_secondary_database 
澳门新濠3559 82@secondary_database = N'logTrans2' 
澳门新濠3559 83,@primary_server = N'10.101.10.66' 
澳门新濠3559 84,@primary_database = N'logTrans1' 
澳门新濠3559 85,@restore_delay = 0 
澳门新濠3559 86,@restore_mode = 1 
澳门新濠3559 87,@disconnect_users = 0 
澳门新濠3559 88,@restore_threshold = 45 
澳门新濠3559 89,@threshold_alert_enabled = 0 
澳门新濠3559 90,@history_retention_period = 1440 

--需要启动数据库对应实例的 SQL Server Agent服务

    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Severity Event 14'

(9)在主服务器上,执行sp_add_log_shipping_primary_secondary向主服务器添加有关新辅助数据库的必需信息,具体SQL脚本如代码:

/****** 对象:  Job [WorkLog_MailNotice]    脚本日期: 06/08/2012 12:57:13 ******/

GO

澳门新濠3559 91EXEC master.dbo.sp_add_log_shipping_primary_secondary 
澳门新濠3559 92@primary_database = N'logTrans1' 
澳门新濠3559 93, @secondary_server = N'10.101.10.67' --辅助数据库的IP 
澳门新濠3559 94, @secondary_database = N'logTrans2' 
澳门新濠3559 95

BEGIN TRANSACTION

 

(10)在辅助服务器上,启用复制和还原作业。启用作业仍然使用sp_update_job存储过程,具体操作如代码:

DECLARE @ReturnCode INT

 

澳门新濠3559 96EXEC msdb.dbo.sp_update_job 
澳门新濠3559 97@job_name='LSCopy_logTrans1', 
澳门新濠3559 98@enabled=1 
澳门新濠3559 99
澳门新濠3559 100EXEC msdb.dbo.sp_update_job 
澳门新濠3559 101@job_name='LSRestore_logTrans2', 
澳门新濠3559 102@enabled=1 

SELECT @ReturnCode = 0

EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Severity Event 14',

通过以上10部操作就完成了对日志传送的配置。现在每隔2分钟,系统将会把主服务器中的日志备份到共享文件夹中,辅助服务器访问共享文件夹将日志备份复制到本地硬盘上,然后由还原作业将复制到本地的日志还原到数据库,从而完成了日志的传送。用户可以在共享文件夹和辅助服务器的本地复制文件夹中看到备份的日志文件。

DECLARE @jobId BINARY (16)

        @message_id=0,

【说明:在SSMS中可以通过右击对应的作业,在弹出的右键菜单中选择“查看历史记录”选项来查看该作业是否正常运行。如果所有日志传送正常运行,则说明日志传送正常。】

EXEC @ReturnCode =   msdb.dbo .sp_add_job

        @severity=14,

            @job_name =N'WorkLog_MailNotice' ,

        @enabled=1,

            @enabled =1,

        @delay_between_responses=60,

            @description =N' 每周五:给未完成本周工时填报的项目经理发送本周工时填报情况的邮件。调用WorkLogNotice存储过程。',

        @include_event_description_in=1,

            @notify_level_email =2, --失败后

        @category_name=N'DBA_MONITORING',

            @start_step_id = 1,

        @job_id=N'00000000-0000-0000-0000-000000000000'

            @notify_email_operator_name =N'qiu_zhengqiang' , @job_id = @jobId OUTPUT -- 作业失败,发送邮件。需创建对应操作员

GO

IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** 对象:  Step [SendMail]    脚本日期: 06/08/2012 12:57:13 ******/

 

EXEC @ReturnCode = msdb.dbo .sp_add_jobstep

EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Server Severity Event 14', @operator_name=N'YourSQLDba_Operator', @notification_method = 1

            @job_id =@jobId,

GO

            @step_name =N'SendMail' ,

 

            @step_id =1,

 

            @on_fail_action =2, --失败后退出

然后我尝试用sa登录(sa已经被禁用)了三次,但是我只收到了一封邮件。特意查看了一下sp_add_alert的官方文档,才知道出现这个原因,是因为参数@delay_between_responses的值设置缘故,通过设置该值,可以防止在在短时间内重复发送一些不需要的电子邮件。如上所示,一分钟内,即使错误日志里面出现了大量类似的错误,也只会发送一封告警邮件。其实只是为了减少发送告警的频率,如果你想当错误日志里出现这个级别的告警时,都必须发送告警邮件,可以将其值设置为0。但是有时候,如果设置为0,你会收到铺天盖地的邮件。其实这个小问题,只是因为以前没有特意留意这个参数而已。存粹属于没有彻底了解这些功能罢了。

            @retry_attempts =1,

 

            @subsystem =N'TSQL' ,

 

            @command =N'EXEC WorkLogNotice' , -- 需创建对应的存储过程

 

            @database_name =N'WorkLog20120419' , -- 数据库名字对应

澳门新濠3559 103

            @flags =16   -- 将日志写入表中(追加到现有历史记录)

 

IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo .sp_add_jobschedule

 

            @job_id =@jobId,

@delay_between_responses = ] delay_between_responses

            @name =N'WorkLogMailNotice' ,

The wait period, in seconds, between responses to the alert. delay_between_responsesis int, with a default of 0, which means there is no waiting between responses (each occurrence of the alert generates a response). The response can be in either or both of these forms:

            @enabled =1,

·          

            @freq_type =8, --每周

·         One or more notifications sent through e-mail or pager.

            @freq_interval =32, --周五

·          

            @freq_subday_type =1, --在指定时间

·         A job to execute.

            @freq_recurrence_factor =1,

·         By setting this value, it is possible to prevent, for example, unwanted e-mail messages from being sent when an alert repeatedly occurs in a short period of time.

            @active_start_date =20120608, --作业开始日期

 

            @active_start_time =120000     -- 作业开始时间小时制 hhmmss

@delay_between_responses =] delay_between_responses

IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

警报响应之间的等待时间 (以秒为单位)。delay_between_responsesis int, 默认值为 0, 这意味着在响应之间没有等待 (每次出现警报都会生成响应)。响应可以是在以下两种形式中的一个, 或者都是:

EXEC @ReturnCode = msdb.dbo .sp_add_jobserver

通过电子邮件或寻呼机发送的一个或多个通知。

            @job_id = @jobId,

要执行的作业。

            @server_name = N'(local)'

通过设置此值, 可以防止例如, 在短时间内重复发生警报时发送不需要的电子邮件。

IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

参考资料:

QuitWithRollback:

 

IF ( @@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

发送邮件示例(游标):

DECLARE @PName VARCHAR (300)

DECLARE @ChineseName VARCHAR (30)

DECLARE @E_Mail VARCHAR (100)

DECLARE @LeftHours FLOAT

DECLARE @Message VARCHAR (300)

DECLARE cMail CURSOR FOR

SELECT ProjectName+ '['+ProjectCode +']' PName,ChineseName, E_Mail,EstHours -RealHours LeftHours FROM #ProjectInfo

OPEN cMail

FETCH NEXT FROM cMail INTO @PName, @ChineseName,@E_Mail ,@LeftHours

WHILE @@FETCH_STATUS =0

BEGIN

IF @LeftHours> 0

BEGIN

SET @Message = '<html><body><tr><td>' + @ChineseName+', 您好:<br>    您负责的项目 '+@PName +' 本周还有 '

+CONVERT( varchar,@LeftHours )+'工时未填报,请您抓紧时间填报。感谢您的支持! ' + '</td></tr>'

+'<tr><td><br><br>MIS团队</td></tr>'

+'<tr><td><br>'+ CONVERT(VARCHAR ,GETDATE(), 120)+'</td></tr>'

+'</body><html>';

EXEC msdb. dbo.sp_send_dbmail

            @profile_name = @profile_name,

            @recipients = @E_Mail, -- 收件人地址

            @subject = @subject, -- 邮件主题

            @importance = @importance,

            @body = @Message,

            @body_format ='HTML';

PRINT @Message

END

FETCH NEXT FROM cMail INTO @PName, @ChineseName,@E_Mail ,@LeftHours

END

CLOSE cMail

DEALLOCATE cMail

END

编辑:数据库 本文来源:并不是每个错误日志都会发送邮件出来,另外在

关键词: