重新產生SQL Server錯誤記錄檔(Error log file),利用sp_cycle_errorlog

重新產生SQL Server錯誤記錄檔(Error log file),利用sp_cycle_errorlog

日前去聽一場演講,講師提到遇見一個客戶由於一年來SQL Server從來沒重開機過。

因此當他開啟『檢視SQL Server紀錄檔』時發生了悲劇(我忘了悲劇是甚麼),因為

檔案資料量實在太大了。因此定期檢查ERRORLOG大小,也是日常管理的重要課題。

所以有很多DBA會設排程定期去重新產生一個ERRORLOG(最多99)來避免過於

肥大的檔案產生。

 

 

 

下圖中我們在SQL Server紀錄檔下可以看見有7ERRORLOG,預設就是6個封存檔

1個目前的ERRORLOG。每當重新啟動SQL Server服務時,就會將『封存 #6

一個檔案刪除,並更名其他6個檔案的檔名,然後重新產生一個目前的ERRORLOG

不斷循環。當然如果您有需求希望能有更多的ERRORLOG暫存檔,也是可以設定,

上限為99個。

clip_image002

 

 

 

下圖是我們開啟『檢視SQL Server紀錄檔』後的畫面,我們可以看見目前的

ERRORLOG中記載了52432筆紀錄,因此當您的檔案越大紀錄越多,開啟檢視

的時間也就越久。

clip_image004

 

 

 

然而我們要如何快速知道目前的ERRORLOG大小呢?如下圖我們可以用xp_enumerrorlogs

這一個指令,該指令會回傳目前所有ERRORLOG的相關資訊,當然包括我們想知道的

檔案大小(單位為位元組)。而目前的ERRORLOG其封存數目這一個欄位值會是0,因此

由下圖可知目前ERRORLOG大小約37MB

clip_image006

 

 

 

 

利用下面語法可以讓我們快速取得目前ERRORLOGSize

create table #tb1(id int,thedate datetime,bytes int)
insert into #tb1(id,thedate,bytes) exec xp_enumerrorlogs
select *,bytes/1024/1024 as mb from #tb1 where id=0
drop table #tb1;

clip_image008

 

 

 

 

一旦資料量太大,我們可以手動去要求建立一個新的ERRORLOG(不用重啟SQL Server服務)

注意:最舊的『封存 #6檔案一樣會被刪除,然後更名其他6個檔後再新生成一個目前

ERRORLOG檔案。而這個指令就是如下圖紅色圈選處 sp_cycle_errorlog這一個預存程序。

如下圖所示當我們完成sp_cycle_errorlog預存程序後,目前ERRORLOG檔案大小就由35MB

0MB了。

clip_image010

 

 

 

如下圖紅色圈選處所示,從UI中我們可以看到產生了新的ERRORLOG

clip_image012

 

 

 

 

 

 

開啟『檢視SQL Server紀錄檔』後的畫面,我們可以看見目前的ERRORLOG中目前只記載了9筆紀錄。

clip_image014

 

 

 

 

參考資料來源

sp_cycle_errorlog

http://databasebestpractices.com/sql-server-error-log-sql-server-agent-error-log/

 

我是ROCK

rockchang@mails.fju.edu.tw