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

　　三. 错误日志查看及告警
　　错误日志以文本方式记录，记事本可以查看，如果错误日志很大，可以选择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放在一个作业里。