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

总结

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