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

2011-01-07

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

依預設，SQL Server Agent 會建立「錯誤記錄檔」來記錄警告與錯誤。

「錯誤記錄檔」中會顯示下列警告和錯誤：
(1)提供有關潛在問題的警告訊息，例如「作業已於執行時刪除」。
(2)通常需要系統管理員介入的錯誤訊息，例如「無法啟動郵件工作階段」。錯誤訊息可以藉由網路傳送來傳送給特定的使用者或電腦。

SQL Server 至多可以維護九個 SQL Server Agent 錯誤記錄檔。
每個已封存之記錄檔的副檔名都會指出記錄檔的相對存在時間。

例如，.1 的副檔名表示它是最近新封存的錯誤記錄檔，而 .9 的副檔名則表示是最早封存的錯誤記錄檔。

因為執行追蹤訊息會填滿 SQL Server Agent 錯誤記錄檔，所以在預設情況下，並不會寫入錯誤記錄檔。
當錯誤記錄檔滿了，會降低您選取與分析更困難錯誤的能力。

因為記錄檔會增加伺服器的處理負擔，請務必仔細考慮將執行追蹤訊息擷取到錯誤記錄檔是否值得。
一般而言，最好只有在為一個特定問題進行偵錯時，您才擷取所有的訊息。

當 SQL Server Agent 停止時，您可以修改 SQL Server Agent 錯誤記錄檔的位置。
您無法開啟空的錯誤記錄檔。您可以隨時循環使用 SQL Server Agent 記錄檔，無須停止 SQL Server Agent。

請參考下圖所示：

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

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

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

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

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

--01 查詢目前的 SQL Server Agent「錯誤記錄檔」
EXEC master.dbo.xp_readerrorlog 0,2

請參考下圖所示：

--EX2 篩選查詢在目前的 SQL Server Agent「錯誤記錄檔」上，使用的關鍵字為：停止
EXEC master.dbo.xp_readerrorlog 0,2, N'停止'

請參考下圖所示：

--EX3  將目前的SQL Server Agent「錯誤記錄檔」資料，存放到資料表變數內
DECLARE @tbAgentLog TABLE (rid INT IDENTITY, LogDate datetime, ErrorLevel int,Text nvarchar(3950))
 
INSERT @tbAgentLog EXEC master.dbo.xp_readerrorlog 0,2
 
SELECT LogDate N'記錄時間',  ErrorLevel N'錯誤等級', Text N'訊息'
FROM @tbAgentLog

請參考下圖所示：

--EX4 匯入並讀取全部的SQL Server Agent「錯誤記錄檔」
DECLARE @fliecnt int
DECLARE @tbAgentFile TABLE (myfilecnt int, myfiledate datetime, myfilesize int)
DECLARE @tbAgentLog TABLE (rid INT IDENTITY, LogDate datetime, ErrorLevel int,Text nvarchar(3950))

INSERT @tbAgentFile EXEC master.dbo.sp_enumerrorlogs 2

SELECT @fliecnt =MAX(myfilecnt) FROM @tbAgentFile

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

SELECT LogDate N'記錄時間', 
 CASE WHEN ErrorLevel = 1 THEN N'錯誤'
  WHEN ErrorLevel = 2 THEN N'警告'
  WHEN ErrorLevel = 3 THEN N'資訊'
 END AS N'錯誤等級', 
Text N'訊息'
FROM @tbAgentLog
ORDER BY rid DESC

請參考下圖所示：