sql server中差错日志errorlog的深入解说

一 .概述

SQL Server 将某些系统事件和会员定义事件记载到 SQL Server 差错日志和 Microsoft Windows 利用程序日志中。 这两种日志都会主动给所有记载事件加上工夫戳。 运用 SQL Server 差错日志中的信息可以解决SQL Server的相干题目。

查看 SQL Server 差错日志可以确保进程（例如，备份和复原操纵、批处置下令或其他脚本和进程）成功完成。 此功能可用于帮忙检测任何目前或潜在的题目领域，包括主动恢复新闻（尤为是在 SQL Server 实例已休止并从新启动时）、内核新闻或其他服务器级差错新闻。

运用 SQL Server 或任何文本编辑器可以查看 SQL Server Management Studio 差错日志。默许状况下，差错日志位于 Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG 和 ERRORLOG.n 文件中。例如: 我电脑win7上将sql server 2008 r2数据库安装在D:\Program Files目录下,差错日志途径为 D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log

每当启动 SQL Server 实例时，将创建新的差错日志信息，sqlserver系统主动调用 exec sp_cycle_errorlog 系统存储历程，该存储历程会关闭目前的差错日志文件，并轮回差错日志扩展编号来轮回运用差错日志文件，而无须从新启动 SQL Server实例 (区别mysql下 有四种日志文件，文件会不断增长，需要治理员去革除，以免影响磁盘空间) 。平常， SQL Server 保存前六个日志的备份, 按照工夫次序，顺次用文件扩展名.1 .2…  .6表示。每重启一次服务，文件扩展名都会加一, 最早那份会被删除。 

二. Errorlog日志内容

2.1  日志主动记载的信息大约有如下：

   (1) SQL SERVER 的启动参数，以及认证模式，内存分配模式。

   (2) 每个数据库是否能够被正常打开。要是不能，缘由是什么?

   (3) 数据库损坏相干的差错

   (4) 数据库备份与恢复行动记载

   (5) DBCC CHECKDB记载

   (6) 内存相干的差错和告诫

   (7) SQL调度涌现异样时的告诫。个别SERVER Hang 服务器死时机陪伴着有这些告诫

   (8) SQL I/O操纵碰到长工夫推迟的告诫

   (9) SQL在运转历程中碰到的其他级别比较高的差错

   (10) SQL内部的访问越界差错（Access Violation）

   (11) SQL服务关闭工夫

   (12) SQL SERVER版本，以及windows和processor根本信息。

2.2  日志开启跟踪能看到的信息

   (1) 所有会员成功或失败的登入

   (2) 死锁及其参与者的信息。跟踪标记1222 或1204

2.3 日志不能记载的题目

   (1) 阻塞题目。只有阻塞尚无重大到影响线程调度，日志里是不会表现的。

   (2) 普通机能题目,超时题目。

   (3) windows层面异样。

所以在检查sqlserver 相干题目的时候，总是从error log着手，要是error log里有一些差错或告诫，就要确认排查，要是记载题目的工夫与软件系统出题目工夫对得上，就需要着重剖析。

三 .跟踪标记

运用DBCC TRACEON来指定要打开的跟踪标志的编号，跟踪标志用于通过控制 SQL Server 的运转方式来自定义某些特征。 启用的跟踪标志将在服务器中不断维持启用状态，直到施行 DBCC TRACEOFF 语句将其禁用为止。在 SQL Server 中，有两种跟踪标记：会话和全局。 会话跟踪标记对某个连贯是有效的，只对该连贯可见。 全局跟踪标记在服务器级别上进行设置，对服务器上的每一个连贯都可见。 若要肯定跟踪标志的状态，请运用 DBCC TRACESTATUS。 若要禁用跟踪标志，请运用 DBCC TRACEOFF。

-- 下面示例是记载死锁，跟踪标记1222 或1204, 脚本如下所示：
 --指定打开目前会话的跟踪标记1222 或1204
DBCC TRACEON (1222,1204)
--以全局方式打开跟踪标记1222 或1204
DBCC TRACEON (1222,1204, -1);
-- 查看跟踪标记状态,如下图所示
DBCC TRACESTATUS

　　　　　　

 -- 以全局方式关闭跟踪标记状态
 DBCC TRACEOFF(1222,1204, -1)

其它跟踪标记号可参考官方文档：

https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

 四. 配置errorlog

在对象资源治理器中，展开 SQL Server 的实例，展开“治理”，右键单击“SQL Server 日志”，再单击“配置” 如下图所示：

4.1 限定差错日志文件在回购以前的数量

若选择此选项，将限定在差错日志回购前可以创建的差错日志数。 每次启动 SQL Server 实例时都将创建新的差错日志。 SQL Server 将保存前六个日志的备份，除非选择此选项并在下面指定一个不一样的最大差错日志文件数。

4.2  最大差错日志文件数

指定差错日志文件回购前创建的最大差错日志文件数。 默许值为 6，即 SQL Server 在回购备份日志前保存的之前备份日志的数目。

五. SQL Server 代理差错日志

SQL Server 默许状况下，代理创建差错日志来记载告诫和差错。 SQL Server 最多可以保护九个 SQL Server 代理差错日志。 每个存档日志都有一个扩展名，指挥该日志的相对存在工夫。 例如上图，目前表示最新的存档差错日志，而编号1 表示最旧的存档差错日志。

默许状况下，施行跟踪新闻不写入 SQL Server 代理日志差错，由于它们会将日志填满。 要是差错日志已满，会降低选中和剖析更重大的差错的能力。 由于日志会添加服务器的处置负荷，所以请务必细心考虑是否值得将施行跟踪新闻捕捉到差错日志中。 平常，最佳仅在调试某个特定题目时捕捉所有新闻。

六.查看errorlog

Errorlog 文件以文本方式记载，用任何文件编辑器都能打开。下面介绍二个存储历程来过滤查看日志文件。

6.1  xp_enumerrorlogs

通过xp_enumerrorlogs可以查看差错日志文件的存档和占用空间大小， 默许参数是1, 表示查看sql server日志。 参数2 表示查看sql server 代理差错日志列表。脚本如下所示:

-- 查看sql server日志列表
EXEC xp_enumerrorlogs

 -- 查看代理差错日志
 EXEC xp_enumerrorlogs 2

6.2 xp_readerrorlog

通过系统存储历程：xp_readerrorlog,能前提过滤日志内容查看，它一共有7个参数，离别是：
(1). 存档编号（0~99）

(2). 日志类型（1为SQL Server日志，2为SQL Server Agent日志）

(3). 查询包含的字符串

(4). 查询包含的字符串

(5). LogDate开端工夫

(6). LogDate完毕工夫

(7). 效果排序，按LogDate排序（Desc、Asc）

例1 ：查看目前sql server差错日志文件内容。存档编号默许值是0，日志类型默许是1，要是要查看目前sql server差错日志文件内容有三种写法,脚本如下:

EXEC xp_readerrorlog
EXEC xp_readerrorlog 0
EXEC xp_readerrorlog 0,1

例 2：查看SQL Server日志历史存档为编号1的文件中，产生的工夫为2018-10-27 19:00点 至2018-10-27 20:00之间的差错，排序方式为工夫的倒排序，为了知足上面的要求,脚本如下:

EXEC xp_readerrorlog 1,1,null,null,'2018-10-27 19:00','2018-10-27 20:00','DESC'

例3： 查看目前SQL Server日志文件中，差错内容里面包含字符串：“Login failed for user 'sa'”  的差错,脚本如下:

EXEC xp_readerrorlog 0,1,'Login failed for user ''sa'''

收缩Errorlog文件

生产服务器上的ErrorLog文件有时候会遇到文件很大的状况，尤为将登录认证状况记载到差错日志的状况之下，此时运用SQL Server Management Studio或者文本编辑器查看差错日志查看的时候速度会是个题目，关于这种状况，可以在不从新启动服务器的状况下，通过存储历程sp_cycle_errorlog来生成新的日志文件，并轮回差错日志扩展编号，就犹如从新启动服务时候同样。除了 Execute sp_cycle_errorlog以外，也可以运用DBCC ERRORLOG来实现一样的功能。在现实操纵中，也可以通过创立一个Job定时去施行该存储历程，这样将日志文件大小控制在合理的范畴以内。

注意事项：旧的 ErrorLog 文件中的数据将被遮盖！要是必须保留旧的 ErrorLog 文件中的数据，则可将这些旧的 ErrorLog 文件复制到某个外部存储介质中。

Exec('DBCC ErrorLog')  或 exec sp_cycle_errorlog，或者可以通过下列下令，将sp 放在Job中按期施行。

总结

以上就是这篇文章的全部内容了，但愿本文的内容对大家的学习或者工作拥有一定的参考学习价值，要是有疑难大家可以留言交换，感谢大家对我们的支撑。