监控-错误日志

原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！

使用以下语句获取【数据库错误日志】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_ErrorLog]    脚本日期: 02/08/2017 13:51:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2015/01/19 14:27
    <中文名称>    获取实例错误日志信息
    <功能说明>    采集当前SQL实例下面的错误日志信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：暂无
    <调用示例>  exec DBA_Pro_Get_ErrorLog 100233,'UestMail@163.com'
【修订记录】
-------------------------------------------------------------------
    <2015/01/19 14:27>  <DBA>: 创建 
******************************************************************/
ALTER proc [dbo].[DBA_Pro_Get_ErrorLog]
    @InstanceID    int,
    @Recipients    VARCHAR(200)=null
AS
BEGIN
    Declare @now datetime,                --当前时间
            @starttime datetime,        --一个小时前
            @html_tb VARCHAR(MAX),        --邮件内容
            @sub varchar(100),            --邮件标题
            @ip INT,                    --IP地址最后的段
            @html_tb1 VARCHAR(MAX),        --邮件内容
            @html_tb2 VARCHAR(MAX)        --邮件内容

    select @now=GETDATE()
    select @starttime=DATEADD(HH,-1,@now)

    SET NOCOUNT ON

    IF (@InstanceID<100000 or @InstanceID>999999)
    begin
        print '数据库实例编号参数错误！'
        return
    end

    BEGIN TRY

    --错误日志临时纪录
    If OBJECT_ID('tempdb..#ErrorLog')is not null
        drop table #ErrorLog
    Create table #ErrorLog(logDate datetime,Processinfo varchar(64),LogText varchar(1024))

    insert into #ErrorLog
    exec master.dbo.xp_readerrorlog 0,1,'error','磁盘空间不足',@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_readerrorlog 0,1,'memory','pressure',@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_readerrorlog 0,1,'NET Framework execution','memory',@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_readerrorlog 0,1,'connections','to increase the maximum value',@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_readerrorlog 0,1,'Could not create AppDomain manager',null,@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_ReadErrorLog 0,1,'Failed','login',@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_ReadErrorLog 0,1,'log cannot be reused',null,@startTime,@now
    insert into #ErrorLog
    exec master.dbo.xp_ReadErrorLog 0,1,'ran out of internal resources',null,@startTime,@now

    insert into dbo.Info_ErrorLog(InstanceID,LogDate,ProcessInfo,LogText,Flag)
    select @InstanceID,logDate,Processinfo,LogText,1
    from #ErrorLog

    --错误代理日志临时纪录
    If OBJECT_ID('tempdb..#AgentErrorLog')is not null
        drop table #AgentErrorLog
    Create table #AgentErrorLog(LogDate datetime,Errorlevel tinyint,LogText varchar(1024))

    insert into #AgentErrorLog
    exec master.dbo.xp_readerrorlog 0,2,'错误',null,@startTime,@now
    insert into #AgentErrorLog
    exec master.dbo.xp_readerrorlog 0,2,'error',null,@startTime,@now
    insert into #AgentErrorLog
    exec master.dbo.xp_readerrorlog 0,2,'Unable',null,@startTime,@now

    insert into dbo.Info_ErrorLog(InstanceID,LogDate,ProcessInfo,LogText,Flag)
    select @InstanceID,logDate,Errorlevel,LogText,2
    from #AgentErrorLog

    /*--发送邮件
    select @IP=Reverse(left(Reverse(IntranetIP),charindex('.',Reverse(IntranetIP))-1)) FROM Info_Hardware where ServerID=left(@InstanceID,4)
    declare @HostName varchar(64)
    select @HostName=HostName From dbo.Info_Instance where InstanceID=@InstanceID

    if @ip is not null
        set @sub='['+cast(@ip as varchar)+'][重要] 数据库错误日志预警'
    else
        set @sub='[重要] 数据库错误日志预警'

    --#ErrorLog
    IF EXISTS(SELECT TOP 1 1 FROM #ErrorLog AS ih)
    BEGIN
        SET @html_tb1=''

        SET @html_tb1 =
               N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
               <H2>'+@HostName+'数据库错误日志预警</H2>
               报表时间:'+Convert(varchar(19),getdate(),121)+
               N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
               <tr><th>序号</th><th>实例编号</th><th>错误日志时间</th><th>错误进程信息</th><th>错误日志详情</th>
               </tr>' + 
               CAST (( select td = ROW_NUMBER()over(order by logdate desc),'',
                              td = @InstanceID,'',
                              td = convert(varchar(20),logDate,120),'',
                              td = Processinfo,'',
                              td = LogText ,''
                        FROM 
                            #ErrorLog AS ih
                        FOR XML PATH('tr')
                        ) AS NVARCHAR(MAX) 
                    ) +
                N'</table><br>'
    END

    --#AgentErrorLog
    IF EXISTS(SELECT TOP 1 1 FROM #AgentErrorLog AS ih)
    BEGIN
        SET @html_tb2=''

        SET @html_tb2 =
               N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
               <H2>'+@HostName+'数据库代理错误日志预警</H2>
               报表时间:'+Convert(varchar(19),getdate(),121)+
               N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
               <tr><th>序号</th><th>实例编号</th><th>错误日志时间</th><th>错误级别</th><th>错误日志详情</th>
               </tr>' + 
               CAST (( select td = ROW_NUMBER()over(order by logdate desc),'',
                              td = @InstanceID,'',
                              td = convert(varchar(20),logDate,120),'',
                              td = Errorlevel,'',
                              td = LogText ,''
                        FROM 
                            #AgentErrorLog AS ih
                        FOR XML PATH('tr')
                        ) AS NVARCHAR(MAX) 
                    ) +
                N'</table><br>'
    END

    IF (@html_tb1 is null and @html_tb2 is null)
        return
    SET @html_tb=isnull(@html_tb1,'')+isnull(@html_tb2,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'LKProfile',
        @recipients = @Recipients,
        @subject = @sub,
        @body = @html_tb,
        @body_format = 'HTML' ;*/

    If OBJECT_ID('tempdb..#ErrorLog')is not null
        drop table #ErrorLog
    If OBJECT_ID('tempdb..#ErrorLog')is not null
        drop table #AgentErrorLog
    END TRY
    BEGIN CATCH
        INSERT INTO ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

数据库服务器收集本地数据，然后使用SSIS汇集监控数据，目标服务器使用以下语句发送【数据库错误日志/作业执行预警】邮件

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_ErrorLog_JobInfo]    脚本日期: 02/08/2017 14:00:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：邮件数据库错误日志/作业执行
  最新修改：2016/11/22
  调用示例: exec RPT_ErrorLog_JobInfo 'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_ErrorLog_JobInfo]
@Recipients    VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON
    declare @StartTime datetime
    declare @Subject varchar(64)
    --数据获取后，只需取ETLTime最近几分钟的数据，对应的是自上次后的所有数据
    set    @StartTime=dateadd(mi,-10,getdate())
    set @Subject='[ALL][重要] 数据库错误日志/作业执行预警'

    SELECT * INTO #ErrorLog FROM [Info_ErrorLog] WHERE ETLTime>=@StartTime    
    SELECT * INTO #JobError FROM [Info_JobHistory] WHERE Flag=1 AND ETLTime>=@StartTime
    SELECT * INTO #JobRunLong FROM [Info_JobHistory] WHERE Flag=2 AND ETLTime>=@StartTime

    if exists(select top 1 1 FROM #ErrorLog)
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>数据库错误日志预警</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>错误进程/级别</th><th>日志详情</th><th>错误时间</th><th>日志类别</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY Flag,a.InstanceID,logDate desc),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = ProcessInfo,'',
                    td = LogText,'',
                    td = convert(varchar(19),LogDate,121),'',
                    td = Flag,''
                 FROM #ErrorLog a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 ORDER BY Flag,a.InstanceID,LogDate desc
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：日志类别(1为错误日志，2为代理错误日志)'
        +'<br /><br />邮件发送条件：
        <br />1、错误日志/代理错误日志中有异常信息';
        --print @tableHTML;
    end

    if exists(select top 1 1 FROM #JobError)
    begin
        DECLARE @tableHTML1  NVARCHAR(MAX);
        SET @tableHTML1 =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>数据库作业执行错误预警</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>作业名称</th><th>作业步骤ID</th><th>作业步骤名称</th><th>作业执行结果消息</th><th>作业执行状态</th><th>作业执行时间</th><th>作业执行时长</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID,Run_DateTime desc),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = JobName,'',
                    td = Step_ID,'',
                    td = Step_Name,'',
                    td = case when len(MessageInfo)>160 then left(MessageInfo,160)+'... 该步骤失败。' else MessageInfo end,'',
                    td = Run_Status,'',
                    td = convert(varchar(19),Run_DateTime,121),'',
                    td = Run_Duration,''
                 FROM #JobError a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 ORDER BY a.InstanceID,Run_DateTime desc
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：执行时长格式为[小时:分钟:秒]，作业执行结果消息过长的(len(MessageInfo)>160)有作截断处理'
        +'<br /><br />邮件发送条件：
        <br />1、作业执行失败(状态=0)/取消(状态=3)';
        --print @tableHTML1;
    end

    if exists(select top 1 1 FROM #JobRunLong)
    begin
        DECLARE @tableHTML2  NVARCHAR(MAX);
        SET @tableHTML2 =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>数据库作业执行时长预警</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>作业名称</th><th>作业步骤ID</th><th>作业步骤名称</th><th>作业执行结果消息</th><th>作业执行时间</th><th>本次作业执行时长</th><th>上次作业执行时长</th><th>执行时长增长百分比%</th></tr>' + 
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID,Run_DateTime desc),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = JobName,'',
                    td = Step_ID,'',
                    td = Step_Name,'',
                    td = case when len(MessageInfo)>32 then left(MessageInfo,32)+'... 该步骤成功。' else MessageInfo end,'',
                    td = convert(varchar(19),Run_DateTime,121),'',
                    td = Run_Duration,'',
                    td = Prev_Run_Duration,'',
                    td = [Growth(%)],''
                 FROM #JobRunLong a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 WHERE cast(replace([Growth(%)],'%','') as float)>=20
                 ORDER BY a.InstanceID,Run_DateTime desc
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：执行时长格式为[小时:分钟:秒]'
        +'<br /><br />邮件发送条件：
        <br />1、作业执行成功
        <br />2、上次时长>30秒，本次时长-上次时长>10秒，(本次时长-上次时长)/上次时长>20%';
        --print @tableHTML2;
    end

    IF (@tableHTML is null and @tableHTML1 is null and @tableHTML2 is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')+ISNULL(@tableHTML1,'')+ISNULL(@tableHTML2,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';

    if object_id('tempdb..#ErrorLog')is not null
        drop table #ErrorLog
    if object_id('tempdb..#JobError')is not null
        drop table #JobError
    if object_id('tempdb..#JobRunLong')is not null
        drop table #JobRunLong
END
View Code
复制代码

posted @   Uest  阅读(296)  评论(0)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
点击右上角即可分享
微信分享提示
AI IDE Trae