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中定期执行。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。