請使用 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'最近一次的啟動日期時間'請參考下圖所示:
參考資料:
設定SQL Server「錯誤記錄檔(Error log file)」的保存份數
http://sharedderrick.blogspot.com/2010/04/sql-servererror-log-file.html
FIX: Xp_readerrorlog 命令在 SQL Server 2005 和 SQL Server 2008 中的 [執行會停止回應] 及 [執行] 命令增加為 100%的 CPU 使用量
http://support.microsoft.com/kb/973524
監視錯誤記錄
http://msdn.microsoft.com/zh-tw/library/ms187885.aspx
檢視 SQL Server 錯誤記錄
http://msdn.microsoft.com/zh-tw/library/ms187885.aspx
使用 xp_readerrorlog 或 sp_readerrorlog 來讀取 SQL Server Agent「錯誤記錄檔」
http://sharedderrick.blogspot.com/2011/01/xpreaderrorlog-spreaderrorlog-sql_07.html
沒有留言:
張貼留言