sql server中錯誤日志errorlog的深入講解

一 .概述

SQL Server 將某些系統(tǒng)事件和用戶定義事件記錄到 SQL Server 錯誤日志和 Microsoft Windows 應用程序日志中。 這兩種日志都會自動給所有記錄事件加上時間戳。 使用 SQL Server 錯誤日志中的信息可以解決SQL Server的相關問題。

查看 SQL Server 錯誤日志可以確保進程（例如，備份和還原操作、批處理命令或其他腳本和進程）成功完成。 此功能可用于幫助檢測任何當前或潛在的問題領域，包括自動恢復消息（尤其是在 SQL Server 實例已停止并重新啟動時）、內核消息或其他服務器級錯誤消息。

使用 SQL Server 或任何文本編輯器可以查看 SQL Server Management Studio 錯誤日志。默認情況下，錯誤日志位于 Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG 和 ERRORLOG.n 文件中。例如: 我電腦win7上將sql server 2008 r2數據庫安裝在D:\Program Files目錄下,錯誤日志路徑為 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

每當啟動 SQL Server 實例時，將創(chuàng)建新的錯誤日志信息，sqlserver系統(tǒng)自動調用 exec sp_cycle_errorlog 系統(tǒng)存儲過程，該存儲過程會關閉當前的錯誤日志文件，并循環(huán)錯誤日志擴展編號來循環(huán)使用錯誤日志文件，而不必重新啟動 SQL Server實例 (區(qū)別mysql下 有四種日志文件，文件會一直增長，需要管理員去清除，避免影響磁盤空間) 。通常， SQL Server 保留前六個日志的備份, 按照時間順序，依次用文件擴展名.1 .2…  .6表示。每重啟一次服務，文件擴展名都會加一, 最早那份會被刪除。 

二. Errorlog日志內容

2.1  日志自動記錄的信息大概有如下：

   (1) SQL SERVER 的啟動參數，以及認證模式，內存分配模式。

   (2) 每個數據庫是否能夠被正常打開。如果不能，原因是什么?

   (3) 數據庫損壞相關的錯誤

   (4) 數據庫備份與恢復動作記錄

   (5) DBCC CHECKDB記錄

   (6) 內存相關的錯誤和警告

   (7) SQL調度出現(xiàn)異常時的警告。一般SERVER Hang 服務器死機會伴隨著有這些警告

   (8) SQL I/O操作遇到長時間延遲的警告

   (9) SQL在運行過程中遇到的其他級別比較高的錯誤

   (10) SQL內部的訪問越界錯誤（Access Violation）

   (11) SQL服務關閉時間

   (12) SQL SERVER版本，以及windows和processor基本信息。

2.2  日志開啟跟蹤能看到的信息

   (1) 所有用戶成功或失敗的登入

   (2) 死鎖及其參與者的信息。跟蹤標志1222 或1204

2.3 日志不能記錄的問題

   (1) 阻塞問題。只要阻塞還沒有嚴重到影響線程調度，日志里是不會體現(xiàn)的。

   (2) 普通性能問題,超時問題。

   (3) windows層面異常。

所以在檢查sqlserver 相關問題的時候，總是從error log著手，如果error log里有一些錯誤或警告，就要確認排查，如果記錄問題的時間與軟件系統(tǒng)出問題時間對得上，就需要著重分析。

三 .跟蹤標志

使用DBCC TRACEON來指定要打開的跟蹤標記的編號，跟蹤標記用于通過控制 SQL Server 的運行方式來自定義某些特征。 啟用的跟蹤標記將在服務器中一直保持啟用狀態(tài)，直到執(zhí)行 DBCC TRACEOFF 語句將其禁用為止。在 SQL Server 中，有兩種跟蹤標志：會話和全局。 會話跟蹤標志對某個連接是有效的，只對該連接可見。 全局跟蹤標志在服務器級別上進行設置，對服務器上的每一個連接都可見。 若要確定跟蹤標記的狀態(tài)，請使用 DBCC TRACESTATUS。 若要禁用跟蹤標記，請使用 DBCC TRACEOFF。

-- 下面示例是記錄死鎖，跟蹤標志1222 或1204, 腳本如下所示：
 --指定打開當前會話的跟蹤標志1222 或1204
DBCC TRACEON (1222,1204)
--以全局方式打開跟蹤標志1222 或1204
DBCC TRACEON (1222,1204, -1);
-- 查看跟蹤標志狀態(tài),如下圖所示
DBCC TRACESTATUS

 -- 以全局方式關閉跟蹤標志狀態(tài)
 DBCC TRACEOFF(1222,1204, -1)

其它跟蹤標志號可參考官方文檔：

https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

 四. 配置errorlog

在對象資源管理器中，展開 SQL Server 的實例，展開“管理”，右鍵單擊“SQL Server 日志”，再單擊“配置” 如下圖所示：

4.1 限制錯誤日志文件在回收之前的數目

若選中此選項，將限制在錯誤日志回收前可以創(chuàng)建的錯誤日志數。 每次啟動 SQL Server 實例時都將創(chuàng)建新的錯誤日志。 SQL Server 將保留前六個日志的備份，除非選中此選項并在下面指定一個不同的最大錯誤日志文件數。

4.2  最大錯誤日志文件數

指定錯誤日志文件回收前創(chuàng)建的最大錯誤日志文件數。 默認值為 6，即 SQL Server 在回收備份日志前保留的以前備份日志的數量。

五. SQL Server 代理錯誤日志

SQL Server 默認情況下，代理創(chuàng)建錯誤日志來記錄警告和錯誤。 SQL Server 最多可以維護九個 SQL Server 代理錯誤日志。 每個存檔日志都有一個擴展名，指示該日志的相對存在時間。 例如上圖，當前表示最新的存檔錯誤日志，而編號1 表示最舊的存檔錯誤日志。

默認情況下，執(zhí)行跟蹤消息不寫入 SQL Server 代理日志錯誤，因為它們會將日志填滿。 如果錯誤日志已滿，會降低選擇和分析更嚴重的錯誤的能力。 因為日志會增加服務器的處理負荷，所以請務必仔細考慮是否值得將執(zhí)行跟蹤消息捕獲到錯誤日志中。 通常，最好僅在調試某個特定問題時捕獲所有消息。

六.查看errorlog

Errorlog 文件以文本方式記錄，用任何文件編輯器都能打開。下面介紹二個存儲過程來過濾查看日志文件。

6.1  xp_enumerrorlogs

通過xp_enumerrorlogs可以查看錯誤日志文件的存檔和占用空間大小， 默認參數是1, 表示查看sql server日志。 參數2 表示查看sql server 代理錯誤日志列表。腳本如下所示:

-- 查看sql server日志列表
EXEC xp_enumerrorlogs

 -- 查看代理錯誤日志
 EXEC xp_enumerrorlogs 2

6.2 xp_readerrorlog

通過系統(tǒng)存儲過程：xp_readerrorlog,能條件過濾日志內容查看，它一共有7個參數，分別是：
(1). 存檔編號（0~99）

(2). 日志類型（1為SQL Server日志，2為SQL Server Agent日志）

(3). 查詢包含的字符串

(4). 查詢包含的字符串

(5). LogDate開始時間

(6). LogDate結束時間

(7). 結果排序，按LogDate排序（Desc、Asc）

例1 ：查看當前sql server錯誤日志文件內容。存檔編號默認值是0，日志類型默認是1，如果要查看當前sql server錯誤日志文件內容有三種寫法,腳本如下:

EXEC xp_readerrorlog
EXEC xp_readerrorlog 0
EXEC xp_readerrorlog 0,1

例 2：查看SQL Server日志歷史存檔為編號1的文件中，發(fā)生的時間為2018-10-27 19:00點 至2018-10-27 20:00之間的錯誤，排序方式為時間的倒排序，為了滿足上面的要求,腳本如下:

EXEC xp_readerrorlog 1,1,null,null,'2018-10-27 19:00','2018-10-27 20:00','DESC'

例3： 查看當前SQL Server日志文件中，錯誤內容里面包含字符串：“Login failed for user 'sa'”  的錯誤,腳本如下:

EXEC xp_readerrorlog 0,1,'Login failed for user ''sa'''

收縮Errorlog文件

生產服務器上的ErrorLog文件有時候會碰到文件很大的情況，尤其將登錄認證情況記錄到錯誤日志的情況之下，此時使用SQL Server Management Studio或者文本編輯器查看錯誤日志查看的時候速度會是個問題，對于這種情況，可以在不重新啟動服務器的情況下，通過存儲過程sp_cycle_errorlog來生成新的日志文件，并循環(huán)錯誤日志擴展編號，就如同重新啟動服務時候一樣。除了 Execute sp_cycle_errorlog之外，也可以使用DBCC ERRORLOG來實現(xiàn)同樣的功能。在實際操作中，也可以通過建立一個Job定時去執(zhí)行該存儲過程，這樣將日志文件大小控制在合理的范圍之內。

注意事項：舊的 ErrorLog 文件中的數據將被覆蓋！如果必須保存舊的 ErrorLog 文件中的數據，則可將這些舊的 ErrorLog 文件復制到某個外部存儲介質中。

Exec('DBCC ErrorLog')  或 exec sp_cycle_errorlog，或者可以通過以下命令，將sp 放在Job中定期執(zhí)行。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。