深入降解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。

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

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

 四. 配置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 代理毛病日志列表。脚本以下所示:

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⑴0⑵7 19:00点 至2018⑴0⑵7 20:00之间的毛病，排序方式为时间的倒排序，为了满足上面的要求,脚本以下:

EXEC xp_readerrorlog 1,1,null,null,'2018⑴0⑵7 19:00','2018⑴0⑵7 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中定期履行。

总结

一 .概述

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。

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

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

 四. 配置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 代理毛病日志列表。脚本以下所示:

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⑴0⑵7 19:00点 至2018⑴0⑵7 20:00之间的毛病，排序方式为时间的倒排序，为了满足上面的要求,脚本以下:

EXEC xp_readerrorlog 1,1,null,null,'2018⑴0⑵7 19:00','2018⑴0⑵7 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中定期履行。

总结

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