德瑞克：SQL Server 學習筆記: 設定SQL Server「錯誤記錄檔(Error log file)」的保存份數

2010-04-03

設定SQL Server「錯誤記錄檔(Error log file)」的保存份數

SQL Server 將特定系統事件和使用者自訂事件記錄到 SQL Server 錯誤記錄檔和 Microsoft Windows 應用程式記錄檔中。
這兩種記錄檔都會自動替所有記錄的事件加入時間戳記。

請使用 SQL Server 錯誤記錄檔中的資訊來解決 SQL Server 的相關問題。
請參考下圖所示：

檢視 SQL Server 錯誤記錄檔，以確定處理序順利完成 (例如，備份與還原作業、批次命令或其他指令碼和處理序)。
這有助於偵測任何目前的或潛在的問題區域，包括自動復原訊息 (尤其是 SQL Server 的執行個體已停止又重新啟動時)、核心訊息或其他伺服器層級的錯誤訊息。

使用 SQL Server Management Studio 或任何文字編輯器來檢視 SQL Server 錯誤記錄檔。
根據預設，錯誤記錄檔是位於 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log 資料夾內。

每次啟動 SQL Server 的執行個體時，就會建立新的錯誤記錄檔，不過，sp_cycle_errorlog 系統預存程序可用來循環錯誤記錄檔，而不必重新啟動 SQL Server 的執行個體。
通常，SQL Server 會保留前 6 個記錄檔的備份，並提供副檔名 .1 給最新的記錄檔備份，提供副檔名 .2 給第二新的備份...依此類推。目前的錯誤記錄檔並沒有副檔名。

請參考下圖所示：

Windows 應用程式記錄檔可針對 Windows 作業系統上所發生的事件，以及 SQL Server 與 SQL Server Agent 中的事件，提供整體描述。
使用「Windows 事件檢視器」可檢視 Windows 應用程式記錄檔，以及篩選資訊。
例如，您可以篩選事件，例如資訊、警告、錯誤、成功稽核與失敗稽核。

您可以同時使用 SQL Server 錯誤記錄檔和 Windows 應用程式記錄檔，來找出問題的原因。
例如，在監視 SQL Server 錯誤記錄檔時，您可能會收到不含原因資訊的錯誤訊息。

藉由比較這些記錄之間的事件日期和時間，可以縮小可能原因的範圍。
SQL Server Management Studio 記錄檔檢視器可讓您將 SQL Server、SQL Server Agent 與 Windows 記錄檔整合為一份清單，以便輕鬆地了解相關的伺服器事件與 SQL Server 事件。

-- 若要查詢SQL Server錯誤記錄檔，所存放的路徑
SELECT ServerProperty('ErrorLogFileName')

請參考下圖所示：

-- 查詢SQL Server錯誤記錄檔的檔案資料
exec master.dbo.sp_enumerrorlogs

請參考下圖所示：

-- 設定 SQL Server 錯誤記錄檔，調整為 10 份(至多為 99 份)。
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs', REG_DWORD, 10
GO

請參考下圖所示：

/* 使用 sp_cycle_errorlog

關閉目前的錯誤記錄檔，依照類似伺服器重新啟動的方式來循環處理錯誤記錄副檔名的號碼。
新的錯誤記錄包含版本和著作權資訊，還會有一行指出已建立新記錄。

每次啟動 SQL Server 時，都會將目前的錯誤記錄重新命名為 errorlog.1；errorlog.1  會成為 errorlog.2，errorlog.2 會成為 errorlog.3，依此類推。
您可以利用 sp_cycle_errorlog，在不停止和啟動伺服器的情況下，循環處理錯誤記錄檔。
*/
EXEC sp_cycle_errorlog ;
GO

請參考下圖所示：

-- 查詢 SQL Server Agent 錯誤記錄檔，至多 9 份 
exec master.dbo.sp_enumerrorlogs 2

請參考下圖所示：

/*
關閉目前的 SQL Server Agent 錯誤記錄檔，
依照類似伺服器重新啟動的方式來循環處理 SQL Server Agent 錯誤記錄副檔名的號碼。
新的 SQL Server Agent 錯誤記錄會有一行指出已建立新記錄。

*/
exec msdb.dbo.sp_cycle_agent_errorlog

請參考下圖所示：