星期二, 2月 17, 2015

[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 登入造成登入失敗,再利用上述語法來測試是否發信成功
[SQL] 管理錯誤記錄檔案

沒有留言:

張貼留言