德瑞克：SQL Server 學習筆記: 使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」

2011-01-06

使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」

SQL Server 將特定系統事件和使用者自訂事件記錄到 SQL Server 「錯誤記錄檔」內。
請使用 SQL Server 「錯誤記錄檔」中的資訊來解決 SQL Server 的相關問題。

雖然可以使用 SQL Server Management Studio 管理工具來檢視 SQL Server 「錯誤記錄檔」。
但是每次啟動 SQL Server 的執行個體時，就會建立新的錯誤記錄檔。
若需要進一步篩選資料等分析處理，就需要自行查詢多份「錯誤記錄檔」來比對資料，十分不便。
請參考下圖所示：

適用環境：SQL Server 2005、2008、2008 R2 版本。

若要以程式方式來分析 SQL Server 「錯誤記錄檔」，可以使用 sp_readerrorlog。

sp_readerrorlog 此為 undocumented 的預存程序，可以用來讀取 SQL Server 「錯誤記錄檔」的資料。
但其內部係利用 xp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」的資料，所以，在此我們直接使用 xp_readerrorlog 來讀取 SQL Server 「錯誤記錄檔」。

xp_readerrorlog 此為 undocumented 的「擴充預存程序(extended stored procedure)」。
所以在參數上的使用上，不是很能確認，或是與未來新的版本之相容性，要請各位讀者留意此事。
筆者將收集到的資料，整理如下：

使用語法：
EXEC master.dbo.xp_readerrorlog @p1, @p2, @p3, @p4, @p5, @p6, @p7

參數說明：
@p1 參數：0 表示目前的錯誤記錄檔，1 表示前一次的錯誤記錄檔之備份，2 表示前二次的錯誤記錄檔之備份，依此類推。
@p2 參數：1 或是 NULL 表示 SQL Server 錯誤記錄檔；2 表示 SQL Server Agent 錯誤記錄檔
@p3 參數：作為要查詢條件字串1。
@p4 參數：作為要查詢條件字串2，與 @p3 參數為 AND 條件式。
@p5 參數：指定錯誤記錄檔要讀取的起始日期。
@p6 參數：指定錯誤記錄檔要讀取的終止日期。
@p7 參數：可以設定查詢出來的資料之排序方式，依據「LogDate」資料行來排序，可以使用「ASC」或「DESC」參數值。

請參考以下的範例程式碼的說明：

--EX1 查詢目前的 SQL Server 「錯誤記錄檔」
EXEC master.dbo.xp_readerrorlog
-- 或是
EXEC master.dbo.xp_readerrorlog 0

請參考下圖所示：

--EX2 篩選查詢在目前的 SQL Server 「錯誤記錄檔」上，所發生的錯誤事件，使用的關鍵字為：Failed 或 錯誤
EXEC master.dbo.xp_readerrorlog 0,1, N'Failed'
EXEC master.dbo.xp_readerrorlog 0,1, N'錯誤'

請參考下圖所示：

--EX3 篩選查詢在目前的 SQL Server 「錯誤記錄檔」上，所發生的錯誤事件，使用的關鍵字為：Failed 或 錯誤，並且加入時間排序
EXEC master.dbo.xp_readerrorlog 0,1, N'Failed',NULL,NULL,NULL,'DESC'
EXEC master.dbo.xp_readerrorlog 0,1, N'錯誤',NULL,NULL,NULL,'DESC'

請參考下圖所示：

--EX4  將目前的 SQL Server「錯誤記錄檔」資料，存放到資料表變數內
DECLARE @tbLog TABLE (rid INT IDENTITY, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))

INSERT @tbLog EXEC master.dbo.xp_readerrorlog 0,1

SELECT * FROM @tbLog
ORDER BY 2

請參考下圖所示：

--EX5 匯入並讀取全部的 SQL Server「錯誤記錄檔」
DECLARE @fliecnt int
DECLARE @tbFile TABLE (myfilecnt int, myfiledate datetime, myfilesize int)
DECLARE @tbLog TABLE (rid INT IDENTITY, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))

INSERT @tbFile EXEC master.dbo.sp_enumerrorlogs

SELECT @fliecnt =MAX(myfilecnt) FROM @tbFile

WHILE (@fliecnt >=0)
BEGIN
 INSERT @tbLog EXEC master.dbo.xp_readerrorlog @fliecnt,1
 SET @fliecnt = @fliecnt -1
END

SELECT LogDate N'記錄時間', ProcessInfo N'來源', Text N'訊息' 
FROM @tbLog
ORDER BY rid DESC

請參考下圖所示：

--EX6 匯入並讀取全部的 SQL Server「錯誤記錄檔」，篩選出有發生錯誤的相關資訊
DECLARE @fliecnt int
DECLARE @tbFile TABLE (myfilecnt int, myfiledate datetime, myfilesize int)
DECLARE @tbLog TABLE (rid INT IDENTITY, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))
DECLARE @tbLog2 TABLE (rid INT, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))

INSERT @tbFile EXEC master.dbo.sp_enumerrorlogs

SELECT @fliecnt =MAX(myfilecnt) FROM @tbFile

WHILE (@fliecnt >=0)
BEGIN
 INSERT @tbLog EXEC master.dbo.xp_readerrorlog @fliecnt,1, NULL,NULL,NULL,NULL,'ASC'
 SET @fliecnt = @fliecnt -1
END

INSERT  @tbLog2 SELECT * FROM @tbLog WHERE Text LIKE N'%錯誤%' OR Text LIKE N'%Error%' AND Text NOT LIKE N'%SPN%'

SELECT LogDate N'記錄時間', ProcessInfo N'來源', Text N'訊息'
FROM (   SELECT * FROM @tbLog WHERE rid IN (SELECT rid+1 FROM @tbLog2)
    UNION ALL
    SELECT * FROM @tbLog2) tbunion
ORDER BY LogDate DESC ,rid DESC

請參考下圖所示：

--EX7 讀取目前的 SQL Server「錯誤記錄檔」資料，查詢 SQL Server 最近一次的啟動日期時間
DECLARE @tbLog TABLE (rid INT IDENTITY, LogDate datetime, ProcessInfo nvarchar(10),Text nvarchar(3950))
DECLARE @BootDate datetime

INSERT @tbLog EXEC master.dbo.xp_readerrorlog 0,1,'Starting up database ''master'''

SELECT @BootDate=LogDate FROM @tbLog

SELECT @BootDate N'最近一次的啟動日期時間'

請參考下圖所示：