[SQL] 管理錯誤記錄檔案

這篇記錄 xp_readerrorlog（undocumented extended stored procedure）搭配 Database Mail 檢查每天是否有錯誤訊息並發信通知

-- 宣告變數
DECLARE @text NVARCHAR(MAX)
DECLARE @ErrorLog TABLE (ID INT IDENTITY, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))
DECLARE @StartDate AS datetime
DECLARE @EndDate AS datetime
SET @StartDate = CONVERT(char(10),getdate(),120)
SET @EndDate = CONVERT(char(10),DATEADD(dd,1,getdate()),120)

-- 把 SQL Server ErrorLog 資料塞進 @ErrorLog
INSERT @ErrorLog EXEC master.dbo.xp_readerrorlog 0,1,NULL,NULL,@StartDate,@EndDate

-- 把 SQL Server Agent ErrorLog 資料塞進 @ErrorLog
INSERT @ErrorLog EXEC master.dbo.xp_readerrorlog 0,2,NULL,NULL,@StartDate,@EndDate

IF EXISTS 
    (
        SELECT 1 
        FROM @ErrorLog 
        WHERE Text LIKE N'%錯誤%' OR 
            (Text LIKE N'%Error%' AND Text NOT LIKE N'%SPN%')
    )
        BEGIN
        
            -- 整理 HTML Table 資料
            SET @text =
                N' ＜H1＞錯誤檔案＜/H1＞' +
                N'＜table border="1"＞' +
                N'＜tr＞＜th＞ID＜/th＞＜th＞LogDate＜/th＞＜th＞ProcessInfo＜/th＞＜th＞Text＜/th＞＜/tr＞' +
                ( 
                    SELECT
                        td = ID, '' ,
                        td = LogDate, '' ,
                        td = ProcessInfo, '' ,
                        td = Text
                    FROM @ErrorLog 
                    WHERE Text LIKE N'%錯誤%' OR 
                        (Text LIKE N'%Error%' AND Text NOT LIKE N'%SPN%')
                    FOR XML PATH('tr')
                ) +
                N'＜/table＞'

            -- 進行發信通知  
            EXEC msdb.dbo.sp_send_dbmail 
                @profile_name = 'ProfileName' ,
                @recipients = '收件人 EMail' ,
                @subject = 'SQL Server 通知 - 錯誤檔案訊息',
                @body = @text ,
                @body_format = 'HTML'

        END

故意利用 sa 登入造成登入失敗，再利用上述語法來測試是否發信成功

參考資料
• 使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」
• 資料庫例行管理
  
• sp_send_dbmail - 範例 B
  
• Super SQL Server -  【管理 Life Saver 】之 【稽核誰登入失敗之一】