sql server中錯(cuò)誤日志errorlog的深入講解

一 .概述

SQL Server 將某些系統(tǒng)事件和用戶(hù)定義事件記錄到 SQL Server 錯(cuò)誤日志和 Microsoft Windows 應(yīng)用程序日志中。 這兩種日志都會(huì)自動(dòng)給所有記錄事件加上時(shí)間戳。 使用 SQL Server 錯(cuò)誤日志中的信息可以解決SQL Server的相關(guān)問(wèn)題。

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

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

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

二. Errorlog日志內(nèi)容

2.1  日志自動(dòng)記錄的信息大概有如下：

   (1) SQL SERVER 的啟動(dòng)參數(shù)，以及認(rèn)證模式，內(nèi)存分配模式。

   (2) 每個(gè)數(shù)據(jù)庫(kù)是否能夠被正常打開(kāi)。如果不能，原因是什么?

   (3) 數(shù)據(jù)庫(kù)損壞相關(guān)的錯(cuò)誤

   (4) 數(shù)據(jù)庫(kù)備份與恢復(fù)動(dòng)作記錄

   (5) DBCC CHECKDB記錄

   (6) 內(nèi)存相關(guān)的錯(cuò)誤和警告

   (7) SQL調(diào)度出現(xiàn)異常時(shí)的警告。一般SERVER Hang 服務(wù)器死機(jī)會(huì)伴隨著有這些警告

   (8) SQL I/O操作遇到長(zhǎng)時(shí)間延遲的警告

   (9) SQL在運(yùn)行過(guò)程中遇到的其他級(jí)別比較高的錯(cuò)誤

   (10) SQL內(nèi)部的訪問(wèn)越界錯(cuò)誤（Access Violation）

   (11) SQL服務(wù)關(guān)閉時(shí)間

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

2.2  日志開(kāi)啟跟蹤能看到的信息

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

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

2.3 日志不能記錄的問(wèn)題

   (1) 阻塞問(wèn)題。只要阻塞還沒(méi)有嚴(yán)重到影響線程調(diào)度，日志里是不會(huì)體現(xiàn)的。

   (2) 普通性能問(wèn)題,超時(shí)問(wèn)題。

   (3) windows層面異常。

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

三 .跟蹤標(biāo)志

使用DBCC TRACEON來(lái)指定要打開(kāi)的跟蹤標(biāo)記的編號(hào)，跟蹤標(biāo)記用于通過(guò)控制 SQL Server 的運(yùn)行方式來(lái)自定義某些特征。 啟用的跟蹤標(biāo)記將在服務(wù)器中一直保持啟用狀態(tài)，直到執(zhí)行 DBCC TRACEOFF 語(yǔ)句將其禁用為止。在 SQL Server 中，有兩種跟蹤標(biāo)志：會(huì)話和全局。 會(huì)話跟蹤標(biāo)志對(duì)某個(gè)連接是有效的，只對(duì)該連接可見(jiàn)。 全局跟蹤標(biāo)志在服務(wù)器級(jí)別上進(jìn)行設(shè)置，對(duì)服務(wù)器上的每一個(gè)連接都可見(jiàn)。 若要確定跟蹤標(biāo)記的狀態(tài)，請(qǐng)使用 DBCC TRACESTATUS。 若要禁用跟蹤標(biāo)記，請(qǐng)使用 DBCC TRACEOFF。

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

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

其它跟蹤標(biāo)志號(hào)可參考官方文檔：

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

 四. 配置errorlog

在對(duì)象資源管理器中，展開(kāi) SQL Server 的實(shí)例，展開(kāi)“管理”，右鍵單擊“SQL Server 日志”，再單擊“配置” 如下圖所示：

4.1 限制錯(cuò)誤日志文件在回收之前的數(shù)目

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

4.2  最大錯(cuò)誤日志文件數(shù)

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

五. SQL Server 代理錯(cuò)誤日志

SQL Server 默認(rèn)情況下，代理創(chuàng)建錯(cuò)誤日志來(lái)記錄警告和錯(cuò)誤。 SQL Server 最多可以維護(hù)九個(gè) SQL Server 代理錯(cuò)誤日志。 每個(gè)存檔日志都有一個(gè)擴(kuò)展名，指示該日志的相對(duì)存在時(shí)間。 例如上圖，當(dāng)前表示最新的存檔錯(cuò)誤日志，而編號(hào)1 表示最舊的存檔錯(cuò)誤日志。

默認(rèn)情況下，執(zhí)行跟蹤消息不寫(xiě)入 SQL Server 代理日志錯(cuò)誤，因?yàn)樗鼈儠?huì)將日志填滿。 如果錯(cuò)誤日志已滿，會(huì)降低選擇和分析更嚴(yán)重的錯(cuò)誤的能力。 因?yàn)槿罩緯?huì)增加服務(wù)器的處理負(fù)荷，所以請(qǐng)務(wù)必仔細(xì)考慮是否值得將執(zhí)行跟蹤消息捕獲到錯(cuò)誤日志中。 通常，最好僅在調(diào)試某個(gè)特定問(wèn)題時(shí)捕獲所有消息。

六.查看errorlog

Errorlog 文件以文本方式記錄，用任何文件編輯器都能打開(kāi)。下面介紹二個(gè)存儲(chǔ)過(guò)程來(lái)過(guò)濾查看日志文件。

6.1  xp_enumerrorlogs

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

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

 -- 查看代理錯(cuò)誤日志
 EXEC xp_enumerrorlogs 2

6.2 xp_readerrorlog

通過(guò)系統(tǒng)存儲(chǔ)過(guò)程：xp_readerrorlog,能條件過(guò)濾日志內(nèi)容查看，它一共有7個(gè)參數(shù)，分別是：
(1). 存檔編號(hào)（0~99）

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

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

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

(5). LogDate開(kāi)始時(shí)間

(6). LogDate結(jié)束時(shí)間

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

例1 ：查看當(dāng)前sql server錯(cuò)誤日志文件內(nèi)容。存檔編號(hào)默認(rèn)值是0，日志類(lèi)型默認(rèn)是1，如果要查看當(dāng)前sql server錯(cuò)誤日志文件內(nèi)容有三種寫(xiě)法,腳本如下:

EXEC xp_readerrorlog
EXEC xp_readerrorlog 0
EXEC xp_readerrorlog 0,1

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

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

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

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

收縮Errorlog文件

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

注意事項(xiàng)：舊的 ErrorLog 文件中的數(shù)據(jù)將被覆蓋！如果必須保存舊的 ErrorLog 文件中的數(shù)據(jù)，則可將這些舊的 ErrorLog 文件復(fù)制到某個(gè)外部存儲(chǔ)介質(zhì)中。

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

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。