2. SQL Server資料庫狀态監控 - 錯誤日志

一. 錯誤日志簡介；

二. 錯誤日志維護；

三. 錯誤日志檢視及告警；

無論是作業系統 (Unix 或者Windows)，還是應用程式 (Web 服務，資料庫系統等等) ，通常都有自身的日志機制，以便故障時追溯現場及原因。Windows Event Log和 SQL Server Error Log就是這樣的日志， PS: SQL Server 中的錯誤日志 (Error Log) 類似于 Oracle中的alert 檔案。

一. 錯誤日志簡介

1. Windows事件日志與SQL Server 錯誤日志

Windows事件日志中，應用程式裡的SQL Server和SQL Server Agent服務，分别對應來源自MSSQLSERVER和SQLSERVERAGENT的日志資訊；

SQL Server錯誤日志中資訊，與Windows事件日志裡來源自MSSQLSERVER的日志資訊基本一緻，不同的是，Windows事件日志裡資訊為應用程式級，較為簡潔些，而SQL Server錯誤日志裡通常有具體的資料庫錯誤資訊。比如：

Windows事件日志中錯誤資訊：

SQL Server錯誤日志中錯誤資訊：

Error: 18456, Severity: 14, State: 8.

2. 如何了解SQL Server的Error message？

以上面的Error: 18456, Severity: 14, State: 8.為例:

(1) Error，錯誤編号，可以在系統表裡查到對應的文本資訊；

select * From sys.messages where message_id = 18456

(2) Severity，錯誤級别，表明這個錯誤的嚴重性，一共有25個等級，級别越高，就越需要我們去注意處理，20~25級别的錯誤會直接報錯并跳出執行，用SQL語句的TRY…CATCH是捕獲不到的；

(3) State，錯誤狀态，比如18456錯誤，幫助文檔記載了如下狀态，不同狀态代表不同錯誤原因：

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啟動帳号權限問題，或者重新開機SQL Server服務就好了。

3. SQL Server 錯誤日志包含哪些資訊

SQL Server錯誤日志中包含SQL Server開啟、運作、終止整個過程的：運作環境資訊、重要操作、級别比較高的錯誤等：

(1) SQL Server/Windows基本資訊，如：版本、程序号、IP/主機名、端口、CPU個數等；

(2) SQL Server啟動參數及認證模式、記憶體配置設定；

(3) SQL Server執行個體下每個資料打開狀态(包括系統和使用者資料庫)；

(4) 資料庫或伺服器配置選項變更，KILL操作，開關DBCC跟蹤，登入失敗等等

(5) 資料庫備份/還原的記錄；

(6) 記憶體相關的錯誤和警告，可能會DUMP很多資訊在錯誤日志裡；

(7) SQL Server排程異常警告、IO操作延遲警告、内部通路越界 (也就是下面說到的Error 0)；

(8) 資料庫損壞的相關錯誤，以及DBCC CHECKDB的結果；

(9) 執行個體關閉時間；

另外，可以手動開關一些跟蹤标記(trace flags)，來自定義錯誤日志的内容，比如：記錄如使用者登入登出記錄(login auditing)，查詢的編譯執行等資訊，比較常用的可能是用于檢查死鎖時的1204/1222 跟蹤标記。

通常錯誤日志不會記錄SQL語句的性能問題，如：阻塞、逾時的資訊，也不會記錄Windows層面的異常(這會在windows事件日志中記載)。

SQL Server Agent錯誤日志中同樣也包括：資訊/警告/錯誤這幾類日志，但要簡單很多。

4. SQL Server 錯誤日志存放在哪裡

假設SQL Server被安裝在

X:\Program Files\Microsoft SQL Server

，則SQL Server 與SQL Server Agent的錯誤日志檔案預設被放在：

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

如果錯誤日志路徑被管理者修改，可以通過以下某種方式找到：

(1) 作業系統的應用程式日志裡，SQL Server啟動時會留下錯誤日志檔案的路徑；

(2) 通過SSMS/管理/錯誤日志，SQL Server啟動時會留下錯誤日志檔案的路徑；

(3) SQL Server配置管理器裡，點選SQL Server執行個體/屬性/進階/啟動參數 (Startup parameters) ；

(4) 通過一個未記載的SQL語句 (在SQL Server 2000中測試無效，2005及以後可以)：

SELECT SERVERPROPERTY('ErrorLogFileName')

5. SQL Server 錯誤日志目錄下的其他檔案

在錯誤日志目錄下除了SQL Server和SQL Server Agent的日志，可能還會有以下檔案：

(1) 維護計劃産生的report檔案 (SQL Server 2000的時候，後來的維護計劃log記錄在msdb)；

(2) 預設跟蹤(default trace) 生成的trace檔案，PS: 審計(Audit) 産生的trace檔案在\MSSQL\DATA下；

(3) 全文索引的錯誤、日志檔案；

(4) SQLDUMP檔案，比如：exception.log/SQLDump0001.txt/SQLDump0001.mdmp，大多是發生Error 0時DUMP出來的，同時在錯誤日志裡通常會有類似如下記錄：

Error: 0, Severity: 19, State: 0

SqlDumpExceptionHandler: Process 232 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

順便說下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.

二. 錯誤日志維護

1. 錯誤日志檔案個數

1.1 SQL Server錯誤日志

SQL Server錯誤日志檔案數量預設為7個：1個正在用的(ERRORLOG)和6個歸檔的(ERRORLOG.1 – ERRORLOG.6)，可以配置以保留更多(最多99個)；

(1) 打開到SSMS/管理/SQL Server Logs檔案夾/右擊/配置；

(2) 通過未記載的擴充存儲過程，直接讀寫系統資料庫也行：

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作為一個Windows下的應用程式，很多資訊是寫在系統資料庫裡的，自然也可以手動打開系統資料庫編輯器或寫SHELL去修改系統資料庫來作配置。

最後，可以通過如下SQL語句檢視已存在的錯誤日志編号、起止時間、目前大小。

EXEC master..xp_enumerrorlogs

1.2 SQL Server Agent錯誤日志

SQL Server Agent錯誤日志檔案數量共為10個：1個正在用的(SQLAGENT.OUT)，9個歸檔的(SQLAGENT.1 - SQLAGENT.9)，個數不可以修改，但可以配置日志所記載的資訊類型：資訊、警告、錯誤。

(1) 打開到SSMS/SQL Server Agent/Error Logs檔案夾/右擊/配置；

(2) 未記載的擴充存儲過程：

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7
GO

至于@errorlogging_level各個值的意思，由于沒有文檔記載，需要自己測試并推算下。

2. 錯誤日志檔案歸檔

2.1 為什麼要歸檔錯誤日志？

假設SQL Server執行個體從來沒被重新開機過，也沒有手動歸檔過錯誤日志，那麼錯誤日志檔案可能會變得很大，尤其是有内部錯誤時會DUMP很多資訊，一來占空間，更重要的是：想要檢視分析也會不太友善。

SQL Server/SQL Server Agent 錯誤日志有2種歸檔方式，即：建立一個新的日志檔案，并将最老的日志删除。

(1) 自動歸檔：在SQL Server/ SQL Server Agent服務重新開機時；

(2) 手動歸檔：定期運作如下系統存儲過程

EXEC master..sp_cycle_errorlog; --DBCC ERRORLOG 亦可
EXEC msdb.dbo.sp_cycle_agent_errorlog;--SQL Agent 服務需在啟動狀态下才有效

2.2 可不可以根據檔案大小來歸檔？

可能有人會覺得，雖然很久沒歸檔，但是錯誤日志确實不大，沒必要定期歸檔，最好可以根據檔案大小來判斷。有以下幾種方法：

(1) 有些監控工具，比如：SQL Diagnostic manager，就有檢測錯誤日志檔案大小，并根據大小來決定是否歸檔的功能；

(2) 自定義腳本也可以，比如：powershell, xp_enumerrorlogs 都可以檢查錯誤日志大小；

(3) SQL Server 2012支援一個系統資料庫選項，以下語句限制每個錯誤日志檔案為5M，到了5M就會自動歸檔，在2008/2008 R2測試無效：

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 5120;

三. 錯誤日志檢視及告警

錯誤日志以文本方式記錄，記事本就可以檢視，如果錯誤日志很大，可以選擇Gvim/UltraEdit /DOS視窗type errorlog等，這些方式都會“分頁”加載，不會卡住。

1. 錯誤日志檢視

SQL Server提供了以下2種方式檢視：

(1) 日志檢視器 (log viewer)，除了可以檢視SQL Server 與SQL Server Agent的錯誤日志，還可以檢視作業系統日志、資料庫郵件日志。不過當日志檔案太大時，圖形界面非常慢；

(2) 未記載的擴充存儲過程xp_readerrorlog，另外還有一個名為sp_readerrorlog的存儲過程，它是對xp_readerrorlog的簡單封裝，并且隻提供了4個參數，直接使用xp_readerrorlog即可：

在SQL Server 2000裡，僅支援一個參數，即錯誤日志号，預設為0~6：

exec dbo.xp_readerrorlog   --寫0或null都會報錯，直接運作即可
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及以後版本裡，支援多達7個參數，說明如下：

exec dbo.xp_readerrorlog 1,1,N'string1',N'string2',null,null,N'desc'

參數1.日志檔案号: 0 = 目前, 1 = Archive #1, 2 = Archive #2, etc...

參數2.日志檔案類型: 1 or NULL = SQL Server 錯誤日志, 2 = SQL Agent 錯誤日志

參數3.檢索字元串1: 用來檢索的字元串

參數4.檢索字元串2: 在檢索字元串1的傳回結果之上再做過濾

參數5.日志開始時間

參數6.日志結束時間

參數7.結果排序: 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. 錯誤日志告警

可以通過對某些關鍵字做檢索：錯誤(Error)，警告(Warn)，失敗(Fail)，停止(Stop)，而進行告警 (database mail)，以下腳本檢索24小時内的錯誤日志：

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

當然，還可以添加更多關鍵字：kill, dead, victim, cannot, could, not, terminate, bypass, roll, truncate, upgrade, recover, IO requests taking longer than，但當中有個例外，就是DBCC CHECKDB，它的運作結果中必然包括Error字樣，如下：

DBCC CHECKDB (xxxx) executed by sqladmin found 0 errors and repaired 0 errors.

是以對0 errors要跳過，隻有在發現非0 errors時才作告警。

小結

如果沒有監控工具，那麼可選擇擴充存儲過程，結合資料庫郵件的方式，作自動檢查及告警，并定期歸檔錯誤日志檔案以避免檔案太大。大緻步驟如下：

(1) 部署資料庫郵件；

(2) 部署作業：定時檢查日志檔案，如檢索到關鍵字，發郵件告警；

(3) 部署作業：定期歸檔錯誤日志，可與步驟(2) 合并作為兩個step放在一個作業裡。

2. SQL Server資料庫狀态監控 - 錯誤日志

繼續閱讀

錯誤日志：spring-test與juint（1）

java.lang.NoSuchMethodError: javax.servlet.http.HttpServletRequest.getHttpServletMapping()Lja

【RocketMQ】使用Prometheus + AlertManager制作RocketMQ告警安裝AlertManager編寫告警規則檔案配置PrometheusWebhook接口

andoid處理未捕獲異常

The superclass “javax.servlet.http.HttpServlet” was not found on the Java Build Path

alertmanager 簡介

sublime之類的編輯器：IndentationError: unindent does not match any outer indentation level

mysql啟動失敗

can't return outside function or method解決方法

spring整合jax-ws時報錯（java.lang.IllegalStateException: BeanFactory not initialized or already closed）

PHP自定義錯誤處理

使用symbolicatecrash對蘋果日志進行解析

清除SQL SERVER錯誤日志出現作業系統錯誤的解決方法

jTopo繪制網絡拓撲圖

Mysql函數、事務、視圖、日志、主從複制

《原則》之讀書筆記