如何有效管理SQL Server日志

迪鲁宾
990 阅读8分钟

这篇文章涵盖了对SQL Server日志的概述，用于监测和排除SQL Server中的问题。

绪论

日志是数据库管理员在排除任何问题时的最好资源。这些问题可能与服务器配置、启动、恢复、性能、跟踪标志、死锁、IO或检查点延迟有关。例如，假设你的SQL Server实例由于未知的原因而重新启动，在启动后，SQL服务已经启动；但是，你的应用程序不能访问数据库。因此，为了调查问题，你可以查看最新的SQL Server日志，监测数据库恢复过程和估计完成时间。

数据库管理员还可以配置SQL Server，让它向错误日志中提供额外的日志记录。例如，我们可以启用一个跟踪标志来捕获死锁信息。DBA应该主动审查这些日志，寻找潜在的问题。你可以通过查看日志来识别诸如备份失败、登录失败、IO错误等信息。这些错误日志对于寻找SQL Server实例中现有的或潜在的问题是非常好的。

SQL Server日志被称为SQL Server错误日志。这种错误日志有信息、警告和关键错误信息。你也可以在Windows事件查看器日志中查看其中的一些日志。然而，建议使用SQL Server日志来获取详细信息。

SQL Server日志和它的位置

一旦你在SSMS中连接到一个SQL Server实例，导航到管理->SQL Server日志。如下图所示，它有当前日志和六个归档日志（归档#1到归档#6）。

SQL Server Logs location

方法1：使用xp_readerrorlog扩展过程

当前日志是最新的错误日志文件，你可以用它们来查看自SQL Server启动以来的最近活动或手动回收日志文件。SQL Server错误日志是一个文本文件，存储在SQL Server实例的日志目录中。你可以使用扩展过程xp_readerrorlog来找出错误日志的当前位置。

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages', NULL, NULL,NULL

GO

这个查询有以下参数。

  1. 错误日志文件：当前值为0，1为存档#1，2为存档#2
  2. 日志文件类型。值0为SQL Server错误日志，1为SQL Server代理日志
  3. 搜索字符串1
  4. 搜索字符串2
  5. 从时间
  6. 结束时间
  7. 排序结果 - 升序（N'ASC）或降序（N'Desc）。

对于我的演示实例，错误日志文件的位置是 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG。

xp_readerrorlog extended procedure

方法2：使用SERVERPROPERTY()函数

我们也可以查询SERVERPROPERTY函数来确定SQL Server ERRORLOG的位置。

SELECT SERVERPROPERTY('ErrorLogFileName') AS '错误日志位置

SERVERPROPERTY() function

方法3：使用SQL Server配置管理器

另外，打开SQL Server配置管理器，查看启动参数。日志文件的位置是用-e开关指定的。

SQL Server Configuration Manager

你可以浏览日志目录，查看当前存档的错误日志文件。这些错误日志可以在文本编辑器中打开，如记事本或Visual Studio Code。

View logs in the LOG directory

配置SQL Server日志文件的数量和它们的大小

默认情况下，SQL Server维护一个当前和六个归档的日志文件。要验证该值，请在SSMS中右击SQL Server Logs文件夹，然后配置。

Configure the number of SQL Server Log file and their size

无论日志文件大小如何，SQL Server都会记录当前日志文件中的所有信息。在一个繁忙的系统或有许多错误的实例中，你可能会发现在SSMS中查看日志文件是一个挑战。在以下情况下，SQL Server会创建一个新的日志文件，并将当前文件存档。

  • 在SQL服务重新启动期间
  • 手动错误日志回收

然而，如果你由于未知的原因经常重启服务器，你可能会失去所有的历史归档日志数据，因为它只维护六个归档日志。由于错误包含有价值的信息，可以帮助你排除故障，我们可能不想失去这些关键数据。相反，我们可能想在一个关键的生产系统上保留一周甚至一个月的日志文件。

SQL Server允许配置多达6至99个错误日志文件。我们不能指定一个小于6的值，因为在任何情况下，它都会保留6个存档错误日志。

要改变错误日志文件的默认数量，请在标有--"限制错误日志文件被回收前的数量 " 的方框中打勾**。** 例如，下面的截图显示最多有30个错误日志文件。

Limit the number of error log files before they are recycled

其等效的T-SQL脚本使用xp_instance_regwrite扩展存储过程并更新注册表的值。

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30

GO

  • ***注意：*必须重新启动SQL服务，以便这些变化生效。

如前所述，在默认情况下，错误日志的大小是无限的。例如，如果你较长时间不启动SQL Server，也不手动回收日志文件，这个文件就会变得很大。因此，在错误日志配置中，值0显示日志大小是无限的。

Log file size

你可以根据你的要求，指定以KB为单位的大小来限制错误日志的大小。例如，在这里，我们将日志文件的大小限制为1GB。

Set Log file maximum size

其等效的T-SQL脚本更新了SQL Server注册表中的ErrorLogSizeInKb。

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1048576

GO

手动回收错误日志

SQL Server允许手动回收错误日志以有效地管理它们。例如，假设你将错误日志文件的数量增加到30个。因此，我们可以创建一个SQL Server代理工作，在午夜回收错误日志。这样，我们每天都有一个错误日志文件，除非中间重启了SQL Service。要手动回收，执行系统存储过程sp_cycle_errorlog。具有sysadmin固定服务器角色的用户可以执行这个存储过程。

SQL服务器代理日志文件

SQL Server代理也有一个类似于SQL Server日志的独立错误日志。你可以在SQL Server Agent - > Error logs文件夹下找到它。

Manual recycle error logs

右键单击错误日志文件夹和配置。它给出了代理错误日志的位置和代理日志级别。

代理日志文件的扩展名是*.OUT，按照默认配置存储在日志文件夹中。例如，在我的系统中，日志文件目录是C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT。

默认情况下，代理日志文件记录了错误和警告；但是，我们可以包括信息消息。

  • 警告信息。这些信息提供了关于潜在问题的信息。例如，"作业X在运行时被删除"
  • 错误信息。它给出了需要DBA立即干预的信息，例如无法启动一个邮件会话

SQL Server Agent logs

要添加信息消息，在标有信息的复选框上打勾。

Agent log level

SQL Server最多使用9个SQL Server代理日志文件。当前的文件名是SQLAGENT.OUT。扩展名为.1的文件表示第一个存档的代理错误日志。同样，扩展名为.9的文件表示第9个（最老的）归档错误日志。

每次SQL Server Agent重新启动时，SQL Server代理日志文件都会被回收。要手动操作，请右键单击错误日志文件夹并回收。

Recycle agent logs manually

或者，使用存储过程sp_cycle_agent_errorlog来手动回收SQL Server代理日志文件。

使用 msdb 。

去

EXEC dbo.sp_cycle_agent_errorlog 。

GO

存储采购使用以下过程对当前代理错误日志进行存档。

  • 一个新的当前代理错误日志被创建
  • 当前代理错误日志SQLAgent.out转换为SQLAgent.1
  • SQLAgent.1 转换为 SQLAgent.2

结论

SQL Server 错误日志文件有一个实例的信息、警告和关键信息。它对于故障排除、审计登录（成功、失败）很有帮助。数据库管理员可以配置所需的归档错误日志的数量和存储这些文件的目录。

你应该主动寻找被记录的信息，作为你每天和每周健康检查报告的一部分。

Rajendra Gupta

你好！我是Rajendra Gupta，数据库专家和架构师，帮助企业快速有效地实施Microsoft SQL Server、Azure、Couchbase、AWS解决方案，修复相关问题，并以超过14年的经验进行性能调试。

我是《DP-300 Administering Relational Database on Microsoft Azure》一书的作者。我在MSSQLTips、SQLShack、Quest、CodingSight和SeveralNines上发表了650多篇技术文章。

我是最大的关于单一主题的免费在线文章集之一的创建者，他的50篇关于SQL ServerAlways On Availability Groups的系列文章。

基于我对SQL Server社区的贡献，我在2019年、2020年和2021年连续被评为SQLShack著名的年度最佳作者（排名第二），并在2020年获得MSSQLTIPS的冠军奖。

个人博客：
www.dbblogger.comI，我一直对新的挑战感兴趣，所以如果你需要咨询帮助，请联系我[：rajendra.gupta16@gmail.com](mailto:rajendra.gupta16@gmail.com)

查看Rajendra Gupta发表的所有文章

Rajendra Gupta

avatar
文章
阅读
粉丝