[SQL]透過 SQL 指令讀取 Log 紀錄

[SQL]透過 SQL 指令讀取 Log 紀錄

在 DBA 的工作中,有不少時候都是在讀取 SQL Server Error Log、SQL Agent Error Log 和 Windows Event Log,當然也有很多人嫌麻煩而選擇置之不理,等有異常的時候再來查看。今天的文章中我整理幾個自己常用的方式提供給大家參考。

 

1. sp_readerrorlog

從 SQL Server 2005 之後就有提供這樣的指令,這個指令是存放在 master 資料庫下面,從他的 Source 中可以看出來

ALTER proc [sys].[sp_readerrorlog](
    @p1        int = 0,
    @p2        int = NULL,
    @p3        nvarchar(4000) = NULL,
    @p4        nvarchar(4000) = NULL)
as
begin
 
    if (not is_srvrolemember(N'securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, N'securityadmin')
       return (1)
    end
    
    if (@p2 is NULL)
        exec sys.xp_readerrorlog @p1
    else
        exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end

 

這個預存程序最多是接收四個參數,並且一定要具有 securityadminsysadmin 伺服器角色的權限才能執行,而四個參數所代表的意義如下 :

參數

預設值

意義

@p1

0

指定所讀取的 Log 檔案的編號,0 表示是目前正在使用的,可指定其他數字去讀取其他的歷史的 Loh 檔案

@p2

NULL

1 或者是 NULL 值表示讀取 SQL Server 錯誤記錄檔;2 表示讀取 SQL Agent Log

@p3

NULL

搜尋字串,過濾讀取的錯誤紀錄

@p4

NULL

搜尋字串,過濾讀取的錯誤紀錄

 

下面的範例中我們利用 sp_readerrorlog 來讀取 SQL Server Error Log 中有包含 connection 的訊息

image


 

2. xp_readerrorlog

這個是擴充預存程序,基本上 sp_readerrorlog 就是使用該程序去讀取 Log,因此基本上它的功能和前面所介紹的 sp_readerrorlog 極為類似,除了前四個參數功能相同的之外,xp_readerrorlog另外還增加三個參數,因此一共是七個參數,參數的意義如下:

參數

預設值

意義

@p5

NULL

讀取 Log 檔內紀錄的開始時間

@p6

NULL

讀取 Log 檔內紀錄的結束時間

@p7

ASC

輸出資料的時間排序方式 ( ASC or DESC )

 

一般狀況下我們通常都會比較多去使用 sp_readerrorlog,如果有特別需要去過濾時間條件下,是可以考慮來搭配 xp_readerrorlog 來使用。

image

 


 

3. sp_cycle_errorlog 和 sp_cycle_agent_errorlog

這兩個是有點類似的指令,有些時候我們避免長時間運行 SQL Server 之後,造成 SQL Server Errorlog 過大不方便讀取的狀況,因此可以定時使用 sp_cycle_errorlog 來關閉目前的錯誤紀錄檔,就如同伺服器像是重新啟動 SQL Server 的方式來循環處理錯誤紀錄檔案的編號,而該指令是存放在 master 系統資料庫之下,必須具備 sysadmin 的伺服器角色才有權限可以執行,在使用過程中可以搭配另外一個指令 sp_enumerrorlogs 來使用,就可以看出當我們使用 sp_cycle_errorlog 之後,原有個 Log 檔案都會往後遞增一個號碼。

image

 

而另外一個指令 sp_cycle_agent_errorlog 功能和使用上也都類似,但它是針對 SQL Server Agent Log 去作用,但這個指令是放在 msdb 的資料庫下,因此要使用的時候要稍微注意一下,下面的範例我們也是搭配 sp_enumerrorlogs 來使用。只是這裡也要注意一下,如果該預存程序沒有加參數或者是參數值是 1 的話,則表示是 SQL Server Error Log;如果要讀取 SQL Agent Error Log 的話,則要在後面傳入參數值 2 才可以。

image


 

在前面的說明中,我們介紹一些讀取 SQL Server 的錯誤紀錄檔和 SQL Agent 錯誤紀錄檔的方式,如果您可以搭配 SSIS 的封裝和 SQL Agent 的排程執行的功能,相信可以簡化您不少平常查看這些記錄檔的時間,也可以做到比較更好的管理方式。