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

 

Windows事件日志中,应用程序里的SQL Server和SQL Server
Agent服务,分别对应来源自MSSQLSE奥德赛VE索罗德和SQLSESportageVERAGENT的日记新闻;

(4)
SQLDUMP文件,比如:exception.log/SQLDump0001.txt/SQLDump0001.mdmp,大多是爆发Error
0时DUMP出来的,同时在错误日志里经常会有接近如下记录:

(4) 数据库或服务器配置选项变更,KILL操作,开关DBCC跟踪,登录退步等等

(3) SQL Server配置管理器里,点击SQL Server实例/属性/高级/运行参数
(Startup parameters) ;

7.  Login is disabled, and the password is incorrect.

(9) 实例关闭时间;

二. 谬误日志维护

(1) 操作系统的应用程序日志里,SQL Server运营时会留下错误日志文件的路径;

(6) 内部存款和储蓄器相关的荒谬和警戒,可能会DUMP很多音信在错误日志里;

(1) 打开到SSMS/管理/SQL Server Logs文件夹/右击/配置;

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

SQL
Server作为3个Windows下的应用程序,很多音讯是写在注册表里的,自然也得以手动打开注册表编辑器或写SHELL去修改注册表来作配置。

SQL Server

 

X:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.n and SQLAGENT.out.

(1) 维护布置爆发的report文件 (SQL Server
两千的时候,后来的保卫安全陈设log记录在msdb);

SELECT SERVERPROPERTY('ErrorLogFileName')

(1) Error,错误编号,能够在系统表里查到相应的文书音信;

(3) SQL Server实例下各种数据打开状态(包罗系统和用户数据库);

 

(5) 数据库备份/还原的笔录;

select * From sys.messages where message_id = 18456

无论操作系统 (Unix 或然Windows),依然使用程序 (Web
服务,数据库系统等等)
,经常都有本身的日志机制,以便故障时追溯现场及原因。Windows 伊夫nt Log和
SQL Server Error Log就是那样的日志, PS: SQL Server 中的错误日志 (Error
Log) 类似于 Oracle中的alert 文件。

Login failed for user ‘sa’. Reason: Password did not match that for the
login provided. [CLIENT: 10.213.20.8]

此外,能够手动开关一些跟踪标记(trace
flags),来自定义错误日志的内容,比如:记录如用户登入登出记录(login
auditing),查询的编写翻译执行等音信,比较常用的恐怕是用于检查死锁时的1204/1222
跟踪标记。

(2) 通过未记载的恢宏存款和储蓄进程,直接读写注册表也行:

1. 谬误日志文件个数

5.  User ID is not valid.

(2) 默许跟踪(default trace) 生成的trace文件,PS: 审计(奥迪(Audi)t)
产生的trace文件在\MSSQL\DATA下;

Login failed for user ‘sa’. Reason: Password did not match that for the
login provided. [CLIENT: 10.213.20.8]

 

1.2 SQL Server Agent错误日志

EXEC master..xp_enumerrorlogs

常见错误日志不会记录SQL语句的习性难题,如:阻塞、超时的音信,也不会记录Windows层面包车型客车老大(那会在windows事件日志中记载)。

  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.

9.  Password is not valid.

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.

借使不当日志路径被管理员修改,能够因而以下某种格局找到:

以地点的Error: 18456, Severity: 14, State: 8.为例:

Error: 0, Severity: 19, State: 0

(7) SQL Server调度非凡警告、IO操作延迟警告、内部访问越界
(也正是上边说到的Error 0);

顺便说下EEnclaveROCR-V 0 的分解:

 

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

(2)
Severity,错误级别,评释那一个荒唐的重点,一共有2多个阶段,级别越高,就越供给大家去留意处理,20~25级其余荒谬会直接报错并跳出执行,用SQL语句的T揽胜Y…CATCH是捕获不到的;

(3) 全文索引的荒谬、日志文件;

一. 荒谬日志简介

2.  User ID 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.

4. SQL Server
破绽百出日志存放在什么地方

Agent错误日志文件数量共为11个:二个正在用的(SQLAGENT.OUT),8个归档的(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个新的日志文件,并将最老的日记删除。

(1) 自动归档:在SQL Server/ SQL Server Agent服务重启时;

ACCESS,(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
二〇一二帮助贰个报了名表选项,以下语句限制种种错误日志文件为5M,到了5M就会自动归档,在二〇〇九/二〇〇八R2测试无效:

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

 

三. 谬误日志查看及报告警方

不当日志以文件情势记录,记事本就能够查阅,假设不当日志极大,能够挑选Gvim/Ultra艾德it
/DOS窗口type errorlog等,这个情势都会“分页”加载,不会卡住。

1. 不当日志查看

SQL Server提供了以下2种方式查看:

(1) 日志查看器 (log viewer),除了能够查阅SQL Server 与SQL Server
Agent的荒唐日志,还是可以查看操作系统日志、数据库邮件日志。不过当日志文件太大时,图形界面非常的慢;

(2)
未记载的壮大存款和储蓄进度xp_readerrorlog,此外还有二个名为sp_readerrorlog的蕴藏进度,它是对xp_readerrorlog的简短包装,并且只提供了6个参数,直接运用xp_readerrorlog即可:

在SQL Server 3000里,仅扶助三个参数,即错误日志号,暗许为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 二〇〇七及未来版本里,协理多达8个参数,表明如下:

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

 

SQL
Server错误日志文件数量默许为七个:3个正在用的(EQashqaiROCR-VLOG)和伍个归档的(E福睿斯RO宝马7系LOG.1
– E大切诺基RORubiconLOG.6),能够配备以保留越多(最多九十多个);

末尾,能够因而 如下SQL语句查看已存在的不当日志编号、起止时间、当前高低。

1. Windows事件日志与SQL Server
荒唐日志

X:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\
ERRORLOG ~ ERRORLOG.n

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.

再有文档未记载的State: 10, State: 16,平日是SQL
Server运维帐号权限难题,也许重启SQL Server服务就好了。

倘若SQL Server棉被服装置在X:\Program Files\Microsoft SQL Server,则SQL
Server 与SQL Server Agent的荒唐日志文件默许被放在:

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

8.  The password is incorrect.

1.1 SQL Server错误日志

SQL Server错误日志中含有SQL
Server开启、运营、终止整个进程的:运营条件消息、重要操作、级别比较高的谬误等:

5. SQL Server
荒谬日志目录下的任何文件

SQL
Server错误日志中国国投息,与Windows事件日志里来自自MSSQLSE途乐VE哈弗的日记消息基本一致,分化的是,Windows事件日志里音讯为使用程序级,较为简单些,而SQL
Server错误日志里日常有切实的数据库错误音信。比如:

SQL Server
Agent错误日志中相同也囊括:信息/警告/错误这几类日志,但要不难很多。

(3)
State,错误状态,比如18456不当,扶助文书档案记载了如下状态,不一样情况代表差别错误原因:

在错误日志目录下除了SQL Server和SQL Server
Agent的日记,恐怕还会有以下文件:

3. SQL Server
错误日志包蕴怎么着消息

 

(1)  SQL
Server/Windows基本新闻,如:版本、进度号、IP/主机名、端口、CPU个数等;

2. 何以精通SQL Server的Error
message?

SQL Server错误日志中错误音信:

(2) SQL Server运行参数及表明情势、内部存款和储蓄器分配;

6.  An attempt was made to use a Windows login name with SQL Server
Authentication.

(4) 通过1个未记载的SQL语句 (在SQL Server
3000中测试无效,2006及其后能够):

Windows事件日志中错误音讯:

(2) 通过SSMS/管理/错误日志,SQL Server运行时会留下错误日志文件的路线;

(8) 数据库损坏的连锁错误,以及DBCC CHECKDB的结果;