2. SQL Server数据库状态监控 - 错误日志

wslhs

　　无论是操作系统 (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事件日志中错误信息：
　　Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.213.20.8]
　　SQL Server错误日志中错误信息：
　　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，错误编号，可以在系统表里查到对应的文本信息；
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>
　　5.  User>　　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) 通过未记载的扩展存储过程，直接读写注册表也行：

http://common.cnblogs.com/images/copycode.gif

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

http://common.cnblogs.com/images/copycode.gif

　　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_readerrorlog1
　　
exec dbo.xp_readerrorlog6

http://common.cnblogs.com/images/copycode.gif

--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
　　
(
　　
infovarchar(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%'

http://common.cnblogs.com/images/copycode.gif

　　在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' = 降序

http://common.cnblogs.com/images/copycode.gif

--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%'

http://common.cnblogs.com/images/copycode.gif

　　2. 错误日志告警
　　可以通过对某些关键字做检索：错误(Error)，警告(Warn)，失败(Fail)，停止(Stop)，而进行告警 (database mail)，以下脚本检索24小时内的错误日志：

http://common.cnblogs.com/images/copycode.gif

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

http://common.cnblogs.com/images/copycode.gif

　　当然，还可以添加更多关键字：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放在一个作业里。