無(wú)論是
操作系統(tǒng) (Unix 或者
Windows)，還是應(yīng)用程序 (Web 服務(wù)，
數(shù)據(jù)庫(kù)系統(tǒng)等等) ，通常都有自身的日志機(jī)制，以便故障時(shí)追溯現(xiàn)場(chǎng)及原因。Windows Event Log和
SQL Server Error Log就是這樣的日志， PS: SQL Server 中的錯(cuò)誤日志 (Error Log) 類似于
Oracle中的alert 文件。
一. 錯(cuò)誤日志簡(jiǎn)介
1. Windows事件日志與SQL Server 錯(cuò)誤日志
Windows事件日志中，應(yīng)用程序里的SQL Server和SQL Server Agent服務(wù)，分別對(duì)應(yīng)來(lái)源自MSSQLSERVER和SQLSERVERAGENT的日志信息；
SQL Server錯(cuò)誤日志中信息，與Windows事件日志里來(lái)源自MSSQLSERVER的日志信息基本一致，不同的是，Windows事件日志里信息為應(yīng)用程序級(jí)，較為簡(jiǎn)潔些，而SQL Server錯(cuò)誤日志里通常有具體的數(shù)據(jù)庫(kù)錯(cuò)誤信息。比如：
Windows事件日志中錯(cuò)誤信息：
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.213.20.8]
SQL Server錯(cuò)誤日志中錯(cuò)誤信息：
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.213.20.8]
Error: 18456, Severity: 14, State: 8.
2. 如何理解SQL Server的Error message？
以上面的Error: 18456, Severity: 14, State: 8.為例:
(1) Error，錯(cuò)誤編號(hào)，可以在系統(tǒng)表里查到對(duì)應(yīng)的文本信息；
select * From sys.messages where message_id = 18456
(2) Severity，錯(cuò)誤級(jí)別，表明這個(gè)錯(cuò)誤的嚴(yán)重性，一共有25個(gè)等級(jí)，級(jí)別越高，就越需要我們?nèi)プ⒁馓幚恚?0~25級(jí)別的錯(cuò)誤會(huì)直接報(bào)錯(cuò)并跳出執(zhí)行，用SQL語(yǔ)句的TRY…CATCH是捕獲不到的；
(3) State，錯(cuò)誤狀態(tài)，比如18456錯(cuò)誤，幫助文檔記載了如下?tīng)顟B(tài)，不同狀態(tài)代表不同錯(cuò)誤原因：
1. Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information.
2. User ID is not valid.
5. User ID is not valid.
6. An attempt was made to use a Windows login name with SQL Server Authentication.
7. Login is disabled, and the password is incorrect.
8. The password is incorrect.
9. Password is not valid.
11. Login is valid, but server access failed.
12. Login is valid login, but server access failed.
18. Password must be changed.
還有文檔未記載的State: 10, State: 16，通常是SQL Server啟動(dòng)帳號(hào)權(quán)限問(wèn)題，或者重啟SQL Server服務(wù)就好了。
3. SQL Server 錯(cuò)誤日志包含哪些信息
SQL Server錯(cuò)誤日志中包含SQL Server開(kāi)啟、運(yùn)行、終止整個(gè)過(guò)程的：運(yùn)行環(huán)境信息、重要操作、級(jí)別比較高的錯(cuò)誤等：
(1) SQL Server/Windows基本信息，如：版本、進(jìn)程號(hào)、IP/主機(jī)名、端口、CPU個(gè)數(shù)等；
(2) SQL Server啟動(dòng)參數(shù)及認(rèn)證模式、內(nèi)存分配；
(3) SQL Server實(shí)例下每個(gè)數(shù)據(jù)打開(kāi)狀態(tài)(包括系統(tǒng)和用戶數(shù)據(jù)庫(kù))；
(4) 數(shù)據(jù)庫(kù)或服務(wù)器配置選項(xiàng)變更，KILL操作，開(kāi)關(guān)DBCC跟蹤，登錄失敗等等
(5) 數(shù)據(jù)庫(kù)備份/還原的記錄；
(6) 內(nèi)存相關(guān)的錯(cuò)誤和警告，可能會(huì)DUMP很多信息在錯(cuò)誤日志里；
(7) SQL Server調(diào)度異常警告、IO操作延遲警告、內(nèi)部訪問(wèn)越界 (也就是下面說(shuō)到的Error 0)；
(8) 數(shù)據(jù)庫(kù)損壞的相關(guān)錯(cuò)誤，以及DBCC CHECKDB的結(jié)果；
(9) 實(shí)例關(guān)閉時(shí)間；
另外，可以手動(dòng)開(kāi)關(guān)一些跟蹤標(biāo)記(trace flags)，來(lái)自定義錯(cuò)誤日志的內(nèi)容，比如：記錄如用戶登入登出記錄(login auditing)，查詢的編譯執(zhí)行等信息，比較常用的可能是用于檢查死鎖時(shí)的1204/1222 跟蹤標(biāo)記。
通常錯(cuò)誤日志不會(huì)記錄SQL語(yǔ)句的性能問(wèn)題，如：阻塞、超時(shí)的信息，也不會(huì)記錄Windows層面的異常(這會(huì)在windows事件日志中記載)。
SQL Server Agent錯(cuò)誤日志中同樣也包括：信息/警告/錯(cuò)誤這幾類日志，但要簡(jiǎn)單很多。
4. SQL Server 錯(cuò)誤日志存放在哪里
假設(shè)SQL Server被安裝在X:\Program Files\Microsoft SQL Server，則SQL Server 與SQL Server Agent的錯(cuò)誤日志文件默認(rèn)被放在：
X:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ ERRORLOG ~ ERRORLOG.n
X:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.n and SQLAGENT.out.
如果錯(cuò)誤日志路徑被管理員修改，可以通過(guò)以下某種方式找到：
(1) 操作系統(tǒng)的應(yīng)用程序日志里，SQL Server啟動(dòng)時(shí)會(huì)留下錯(cuò)誤日志文件的路徑；
(2) 通過(guò)SSMS/管理/錯(cuò)誤日志，SQL Server啟動(dòng)時(shí)會(huì)留下錯(cuò)誤日志文件的路徑；
(3) SQL Server配置管理器里，點(diǎn)擊SQL Server實(shí)例/屬性/高級(jí)/啟動(dòng)參數(shù) (Startup parameters) ；
(4) 通過(guò)一個(gè)未記載的SQL語(yǔ)句 (在SQL Server 2000中測(cè)試無(wú)效，2005及以后可以)：
SELECT SERVERPROPERTY('ErrorLogFileName')
5. SQL Server 錯(cuò)誤日志目錄下的其他文件
在錯(cuò)誤日志目錄下除了SQL Server和SQL Server Agent的日志，可能還會(huì)有以下文件：
(1) 維護(hù)計(jì)劃產(chǎn)生的report文件 (SQL Server 2000的時(shí)候，后來(lái)的維護(hù)計(jì)劃log記錄在msdb)；
(2) 默認(rèn)跟蹤(default trace) 生成的trace文件，PS: 審計(jì)(Audit) 產(chǎn)生的trace文件在\MSSQL\DATA下；
(3) 全文索引的錯(cuò)誤、日志文件；
(4) SQLDUMP文件，比如：exception.log/SQLDump0001.txt/SQLDump0001.mdmp，大多是發(fā)生Error 0時(shí)DUMP出來(lái)的，同時(shí)在錯(cuò)誤日志里通常會(huì)有類似如下記錄：
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 232 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
順便說(shuō)下ERROR 0 的解釋：
You've hit a bug of some kind - an access violation is an unexpected condition. You need to contact Product Support (http://support.microsoft.com/sql) to help figure out what happened and whether there's a fix available.
Is your server up to date with service packs? If not, you might try updating to the latest build. This error is an internal error in sql server. If you are up to date, you should report it to MS.
二. 錯(cuò)誤日志維護(hù)
1. 錯(cuò)誤日志文件個(gè)數(shù)
1.1 SQL Server錯(cuò)誤日志
SQL Server錯(cuò)誤日志文件數(shù)量默認(rèn)為7個(gè)：1個(gè)正在用的(ERRORLOG)和6個(gè)歸檔的(ERRORLOG.1 – ERRORLOG.6)，可以配置以保留更多(最多99個(gè))；
(1) 打開(kāi)到SSMS/管理/SQL Server Logs文件夾/右擊/配置；
(2) 通過(guò)未記載的擴(kuò)展存儲(chǔ)過(guò)程，直接讀寫注冊(cè)表也行：
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50
GO
--Check current errorlog amout
USE [master]
GO
DECLARE @i int
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @i OUTPUT
SELECT @i
SQL Server作為一個(gè)Windows下的應(yīng)用程序，很多信息是寫在注冊(cè)表里的，自然也可以手動(dòng)打開(kāi)注冊(cè)表編輯器或?qū)慡HELL去修改注冊(cè)表來(lái)作配置。
最后，可以通過(guò) 如下SQL語(yǔ)句查看已存在的錯(cuò)誤日志編號(hào)、起止時(shí)間、當(dāng)前大小。
EXEC master..xp_enumerrorlogs
1.2 SQL Server Agent錯(cuò)誤日志
SQL Server Agent錯(cuò)誤日志文件數(shù)量共為10個(gè)：1個(gè)正在用的(SQLAGENT.OUT)，9個(gè)歸檔的(SQLAGENT.1 - SQLAGENT.9)，個(gè)數(shù)不可以修改，但可以配置日志所記載的信息類型：信息、警告、錯(cuò)誤。
(1) 打開(kāi)到SSMS/SQL Server Agent/Error Logs文件夾/右擊/配置；
(2) 未記載的擴(kuò)展存儲(chǔ)過(guò)程：
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7
GO
至于@errorlogging_level各個(gè)值的意思，由于沒(méi)有文檔記載，需要自己測(cè)試并推算下。
2. 錯(cuò)誤日志文件歸檔
2.1 為什么要?dú)w檔錯(cuò)誤日志？
假設(shè)SQL Server實(shí)例從來(lái)沒(méi)被重啟過(guò)，也沒(méi)有手動(dòng)歸檔過(guò)錯(cuò)誤日志，那么錯(cuò)誤日志文件可能會(huì)變得很大，尤其是有內(nèi)部錯(cuò)誤時(shí)會(huì)DUMP很多信息，一來(lái)占空間，更重要的是：想要查看分析也會(huì)不太方便。
SQL Server/SQL Server Agent 錯(cuò)誤日志有2種歸檔方式，即：創(chuàng)建一個(gè)新的日志文件，并將最老的日志刪除。
(1) 自動(dòng)歸檔：在SQL Server/ SQL Server Agent服務(wù)重啟時(shí)；
(2) 手動(dòng)歸檔：定期運(yùn)行如下系統(tǒng)存儲(chǔ)過(guò)程
EXEC master..sp_cycle_errorlog; --DBCC ERRORLOG 亦可
EXEC msdb.dbo.sp_cycle_agent_errorlog;--SQL Agent 服務(wù)需在啟動(dòng)狀態(tài)下才有效
2.2 可不可以根據(jù)文件大小來(lái)歸檔？
可能有人會(huì)覺(jué)得，雖然很久沒(méi)歸檔，但是錯(cuò)誤日志確實(shí)不大，沒(méi)必要定期歸檔，最好可以根據(jù)文件大小來(lái)判斷。有以下幾種方法：
(1) 有些監(jiān)控工具，比如：SQL Diagnostic manager，就有檢測(cè)錯(cuò)誤日志文件大小，并根據(jù)大小來(lái)決定是否歸檔的功能；
(2) 自定義腳本也可以，比如：powershell, xp_enumerrorlogs 都可以檢查錯(cuò)誤日志大小；
(3) SQL Server 2012支持一個(gè)注冊(cè)表選項(xiàng)，以下語(yǔ)句限制每個(gè)錯(cuò)誤日志文件為5M，到了5M就會(huì)自動(dòng)歸檔，在2008/2008 R2測(cè)試無(wú)效：
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 5120;
三. 錯(cuò)誤日志查看及告警
錯(cuò)誤日志以文本方式記錄，記事本就可以查看，如果錯(cuò)誤日志很大，可以選擇Gvim/UltraEdit /DOS窗口type errorlog等，這些方式都會(huì)“分頁(yè)”加載，不會(huì)卡住。
1. 錯(cuò)誤日志查看
SQL Server提供了以下2種方式查看：
(1) 日志查看器 (log viewer)，除了可以查看SQL Server 與SQL Server Agent的錯(cuò)誤日志，還可以查看操作系統(tǒng)日志、數(shù)據(jù)庫(kù)郵件日志。不過(guò)當(dāng)日志文件太大時(shí)，圖形界面非常慢；
(2) 未記載的擴(kuò)展存儲(chǔ)過(guò)程xp_readerrorlog，另外還有一個(gè)名為sp_readerrorlog的存儲(chǔ)過(guò)程，它是對(duì)xp_readerrorlog的簡(jiǎn)單封裝，并且只提供了4個(gè)參數(shù)，直接使用xp_readerrorlog即可：
在SQL Server 2000里，僅支持一個(gè)參數(shù)，即錯(cuò)誤日志號(hào)，默認(rèn)為0~6：
exec dbo.xp_readerrorlog --寫0或null都會(huì)報(bào)錯(cuò)，直接運(yùn)行即可 exec dbo.xp_readerrorlog 1 exec dbo.xp_readerrorlog 6 --sql server 2000 read error log if OBJECT_ID('tempdb..#tmp_error_log_all') is not null drop table #tmp_error_log_all create table #tmp_error_log_all ( info varchar(8000)，--datetime + processinfo + text num int ) insert into #tmp_error_log_all exec dbo.xp_readerrorlog --split error text if OBJECT_ID('tempdb..#tmp_error_log_split') is not null drop table #tmp_error_log_split create table #tmp_error_log_split ( logdate datetime,--datetime processinfo varchar(100),--processinfo info varchar(7900)--text ) insert into #tmp_error_log_split select CONVERT(DATETIME,LEFT(info,22),120), LEFT(STUFF(info,1,23,''),CHARINDEX(' ',STUFF(info,1,23,'')) - 1), LTRIM(STUFF(info,1,23 + CHARINDEX(' ',STUFF(info,1,23,'')),'')) from #tmp_error_log_all where ISNUMERIC(LEFT(info,4)) = 1 and info <> '.' and substring(info,11,1) = ' ' select * from #tmp_error_log_split where info like '%18456%' |
在SQL Server 2005及以后版本里，支持多達(dá)7個(gè)參數(shù)，說(shuō)明如下：
exec dbo.xp_readerrorlog 1,1,N'string1',N'string2',null,null,N'desc'
參數(shù)1.日志文件號(hào): 0 = 當(dāng)前, 1 = Archive #1, 2 = Archive #2, etc...
參數(shù)2.日志文件類型: 1 or NULL = SQL Server 錯(cuò)誤日志, 2 = SQL Agent 錯(cuò)誤日志
參數(shù)3.檢索字符串1: 用來(lái)檢索的字符串
參數(shù)4.檢索字符串2: 在檢索字符串1的返回結(jié)果之上再做過(guò)濾
參數(shù)5.日志開(kāi)始時(shí)間
參數(shù)6.日志結(jié)束時(shí)間
參數(shù)7.結(jié)果排序: N'asc' = 升序， N'desc' = 降序
--sql server 2005 read error log if OBJECT_ID('tempdb..#tmp_error_log') is not null drop table #tmp_error_log create table #tmp_error_log ( logdate datetime, processinfo varchar(100), info varchar(8000) ) insert into #tmp_error_log exec dbo.xp_readerrorlog select * from #tmp_error_log where info like '%18456%' |
2. 錯(cuò)誤日志告警
可以通過(guò)對(duì)某些關(guān)鍵字做檢索：錯(cuò)誤(Error)，警告(Warn)，失敗(Fail)，停止(Stop)，而進(jìn)行告警 (database mail)，以下腳本檢索24小時(shí)內(nèi)的錯(cuò)誤日志：
declare @start_time datetime ,@end_time datetime set @start_time = CONVERT(char(10),GETDATE() - 1,120) set @end_time = GETDATE() if OBJECT_ID('tempdb..#tmp_error_log') is not null drop table #tmp_error_log create table #tmp_error_log ( logdate datetime, processinfo varchar(100), info varchar(8000) ) insert into #tmp_error_log exec dbo.xp_readerrorlog 0,1,NULL,NULL,@start_time,@end_time,N'desc' select COUNT(1) as num, MAX(logdate) as logdate,info from #tmp_error_log where (info like '%ERROR%' or info like '%WARN%' or info like '%FAIL%' or info like '%STOP%') and info not like '%CHECKDB%' and info not like '%Registry startup parameters%' and info not like '%Logging SQL Server messages in file%' and info not like '%previous log for older entries%' group by info |
當(dāng)然，還可以添加更多關(guān)鍵字：kill, dead, victim, cannot, could, not, terminate, bypass, roll, truncate, upgrade, recover, IO requests taking longer than，但當(dāng)中有個(gè)例外，就是DBCC CHECKDB，它的運(yùn)行結(jié)果中必然包括Error字樣，如下：
DBCC CHECKDB (xxxx) executed by sqladmin found 0 errors and repaired 0 errors.
所以對(duì)0 errors要跳過(guò)，只有在發(fā)現(xiàn)非0 errors時(shí)才作告警。
小結(jié)
如果沒(méi)有監(jiān)控工具，那么可選擇擴(kuò)展存儲(chǔ)過(guò)程，結(jié)合數(shù)據(jù)庫(kù)郵件的方式，作自動(dòng)檢查及告警，并定期歸檔錯(cuò)誤日志文件以避免文件太大。大致步驟如下 ：
(1) 部署數(shù)據(jù)庫(kù)郵件；
(2) 部署作業(yè)：定時(shí)檢查日志文件，如檢索到關(guān)鍵字，發(fā)郵件告警；
(3) 部署作業(yè)：定期歸檔錯(cuò)誤日志，可與步驟(2) 合并作為兩個(gè)step放在一個(gè)作業(yè)里。
English » | | | | | | | | |
Text-to-speech function is limited to 100 characters