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事件日志中错误信息:

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 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.

  1. Login is valid, but server access failed.

  2. Login is valid login, but server access failed.

  3. 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放在一个作业里。