Rock的SQL筆記本

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

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

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

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

肥大的檔案產生。

 

 

 

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

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

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

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

上限為99個。

 

 

 

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

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

的時間也就越久。

 

 

 

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

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

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

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

 

 

 

 

利用下面語法可以讓我們快速取得目前ERRORLOG的Size。

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;

 

 

 

 

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

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

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

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

成0MB了。

 

 

 

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

 

 

 

 

 

 

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

 

 

 

 

參考資料來源

sp_cycle_errorlog

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

 

我是ROCK

rockchang@mails.fju.edu.tw