什么是SQL Server虚拟日志文件以及如何对其进行监视

Microsoft SQL Server has many important features when it comes to file structures. Generally speaking, DBAs often ignore file structures and growth configurations. More specifically, the SQL Server Log files are often left at default values and never touched, even when they begin to cause problems caused. These Log files are important and critical part of the database, especially when it comes to large systems.

Microsoft SQL Server在文件结构方面具有许多重要功能。 一般来说，DBA通常会忽略文件结构和增长配置。 更具体地说，即使SQL Server日志文件开始引起问题，它们也经常保留默认值，并且从未被使用过。 这些日志文件是数据库的重要组成部分，尤其是在大型系统中。

Designing a good file structure for the SQL Server database is as important as designing a physical infrastructure on which these database files will be residing.

为SQL Server数据库设计一个好的文件结构与设计将要驻留这些数据库文件的物理基础结构一样重要。

I will not go into the details of designing and maintaining all files as it will be a very wide topic, rather I will try to enlighten you with the internals of SQL Server Log files and more specifically the Virtual Log Files.

我将不讨论设计和维护所有文件的细节，因为这将是一个非常广泛的主题，而我将尝试通过SQL Server日志文件的内部知识来启发您，尤其是虚拟日志文件 。

After you have read this article, you will be able to answer some of the tricky questions asked by many DBAs, like why you need to have a Log for SQL Server. Why is the SQL Server crash recovery is so slow? Why do I have to change the default settings for file structures as it was set by Microsoft? And so on.

阅读本文之后，您将能够回答许多DBA提出的一些棘手问题，例如为什么需要具有SQL Server日志。 为什么SQL Server崩溃恢复如此缓慢？ 为什么必须更改Microsoft设置的文件结构的默认设置？ 等等。

Microsoft SQL Server has many things (not just the file growth) which Senior DBAs need to change. These settings need to be changed from the default to customized values to manage medium or large SQL Server loads. These settings and configurations directly impacts the optimal performance of SQL Server.

Microsoft SQL Server有许多高级DBA需要更改的内容（不仅仅是文件的增长）。 这些设置需要从默认值更改为自定义值，以管理中型或大型SQL Server负载。 这些设置和配置直接影响SQL Server的最佳性能。

Enough about the general talks, let’s get technical.

足够多的一般性讨论，让我们开始技术讨论。

什么是交易日志文件？ (What is a Transaction Log File?)

Transaction Log file is the only place where all the database changes are recorded. These files are used to write/log each and every event happening in the database in a manner that first the changes are logged and then those changes or events are actually executed. This phenomenon is known as “Write Ahead Logging”. Kendra Little has explained it in detail here and it’s a must watch video. This helps the SQL Server recover in case of any failure. Let me explain this with an example.

事务日志文件是唯一记录所有数据库更改的位置。 这些文件用于写入/记录数据库中发生的每个事件，首先记录更改，然后实际执行这些更改或事件。 这种现象称为“提前写入日志”。 肯德拉·利特尔（Kendra Little）在这里详细解释了它，这是必看的视频。 如果出现任何故障，这有助于SQL Server恢复。 让我用一个例子解释一下。

For instance, if you have started a transaction and it has to update some records in the table(s). So, SQL Server will first write the change in the Log file that it is going to update those specific records and then execute the changes in the memory. After the data has been updated, log is updated again that the transaction has been completed. So, if the SQL Server crashes in between the transaction, SQL Server recovery process starts the database after the crash and reads from the log to recover accordingly. This is the reason that if the SQL Server log file is damaged or lost, then it is almost impossible to recover the database and we have to apply more methods to recover the database in a stable possible which might include data loss.

例如，如果您已经开始一个事务，并且它必须更新表中的某些记录。 因此，SQL Server将首先在日志文件中写入更改，以更新这些特定记录，然后在内存中执行更改。 数据更新后，将再次更新日志，表明事务已完成。 因此，如果SQL Server在事务之间崩溃，则SQL Server恢复过程将在崩溃后启动数据库，并从日志中读取以进行相应的恢复。 这就是如果SQL Server日志文件已损坏或丢失的原因，则几乎不可能恢复数据库，并且我们必须采用更多的方法来稳定地恢复数据库，这可能包括数据丢失。

Anyhow, whenever a record is changed, the log file is maintained in that event and this requires a space inside the SQL Server Log. The SQL Server Log file is working in a circular motion. When the SQL Server Log file is full it starts to write from the beginning and overwrites whatever was written before. This will only happen if all the mentioned below conditions are met otherwise the Log needs to grow.

无论如何，无论何时更改记录，日志文件都会保留在该事件中，并且这需要在SQL Server日志中保留一个空间。 SQL Server日志文件正在循环运动。 当SQL Server日志文件已满时，它将从头开始写入，并覆盖之前写入的内容。 仅当满足以下所有条件时，才会发生这种情况，否则日志需要增长。

1. The transactions are committed which were written to Log file initially.
   提交事务，这些事务最初已写入日志文件。
2. Database checkpoint has cleared the pages from the memory.
   数据库检查点已从内存中清除了页面。
3. The log record is not required for any further activity like Backup, mirroring, replication or Availability groups.
   诸如备份，镜像，复制或可用性组之类的任何其他活动都不需要日志记录。

什么是虚拟日志文件？ (What is a Virtual Log File?)

SQL Server internally manages the Log file into multiple smaller chunks called Virtual Log Files or VLFs. A Virtual Log File is a smaller file inside Log file which contains the actual log records which are actively written inside them. New Virtual Log Files are created when the existing ones are already active and new space is required. This brings us to the point where the value of the Virtual Log Files is created. So, whenever there is a crash and recovery condition, SQL Server first needs to read the Virtual Log File. Certainly, if the number of Virtual Log Files is huge then the time taken by the recovery will also be huge which we do not want.

SQL Server在内部将日志文件管理为多个较小的块，称为虚拟日志文件或VLF。 虚拟日志文件是日志文件中的一个较小文件，其中包含实际写入其中的实际日志记录。 当现有虚拟日志文件已经处于活动状态并且需要新空间时，将创建新的虚拟日志文件。 这使我们到达创建虚拟日志文件的值的地步。 因此，每当发生崩溃和恢复情况时，SQL Server首先需要读取虚拟日志文件。 当然，如果虚拟日志文件的数量很大，那么恢复所花费的时间也将很大，这是我们不希望的。

如何监视虚拟日志文件？ (How to monitor Virtual Log File?)

Monitoring Virtual Log Files is very easy. There is a very simple script from Kev Riley on Microsoft TechNet site here. The script will help you monitor the Virtual Log Files for your databases. The result of the query is pretty simple, i.e. the database name and the Virtual Log File count.

监视虚拟日志文件非常容易。 有一个从千电子伏莱利一个非常简单的脚本在Microsoft TechNet网站在这里 。 该脚本将帮助您监视数据库的虚拟日志文件。 查询的结果非常简单，即数据库名称和虚拟日志文件计数。

So, we should keep the monitoring the number of Virtual Log Files and keep them at a low number. We cannot say for Virtual Log Files that a specific number is accurate. Generally speaking, for every 10 Gigs of Log file we should not exceed 50 Virtual Log Files.

因此，我们应继续监视虚拟日志文件的数量，并将其数量保持在较低水平。 对于虚拟日志文件，我们不能说特定数字是准确的。 一般来说，对于日志文件的每10个Gig，我们不应超过50个虚拟日志文件。

So, after reviewing these files, if you have more than 100 Virtual Log Files then try to check the growth settings.

因此，在检查了这些文件之后，如果您有超过100个虚拟日志文件，则尝试检查增长设置。

If you have more than 1,000 Virtual Log Files, then first apply the fix and take preventive measures as well. The fix is also mentioned later in this article.

如果您有超过1,000个虚拟日志文件，请首先应用此修复程序并采取预防措施。 本文后面还会提到此修复程序。

Sample result:

样本结果：

如何解决虚拟日志文件问题 (How to fix Virtual Log File problems)

So, now you have a pretty good idea about what is Virtual Log File and what problems it could cause. And you must have run the script to check your database VLFs. If you found that you have a high number of Virtual Log Files, then you might be at risk, but don’t worry, the solution is pretty simple.

因此，现在您对什么是虚拟日志文件及其可能引起的问题有了一个很好的了解。 而且您必须已运行脚本来检查数据库VLF。 如果发现虚拟日志文件数量很多，则可能会面临风险，但不用担心，解决方案非常简单。

Just shrink the Log File and re-grow it again. But there is a catch that might require a downtime so just keep it in mind it’s not really very easy to shrink the Log file all the time. So follow mentioned below steps and you will most probably be in a safe position.

只需收缩日志文件，然后再次重新增长即可。 但是有一个问题可能需要停机，因此请记住，始终缩小日志文件并不是很容易。 因此，请遵循以下提到的步骤，您很可能会处于安全的位置。

1. Backup the Transaction Log file if the recovery model of the database is FULL (which generally speaking should be FULL for all production databases for good recovery processes).
   如果数据库的恢复模型为FULL（对于良好恢复过程的所有生产数据库通常应为FULL），则备份事务日志文件。
2. Issue a CHECKPOINT manually so that pages from the buffer can be written down to the disk.
   手动发出CHECKPOINT，以便可以将缓冲区中的页面写到磁盘上。
3. Make sure there are no huge transactions running and keeping the Log file full.
   确保没有大事务在运行，并保持日志文件已满。
4. Shrink the Log file to a smaller size
   将日志文件缩小到较小的大小
5. Re-grow the log file to a larger size on which your log file generally keeps on working.
   将日志文件重新增大到更大的大小，以使日志文件通常可以正常工作。

That’s all! You will have a very low number of Virtual Log Files as of now.

就这样！ 截至目前，您的虚拟日志文件数量很少。

如何防止虚拟日志文件问题 (How to prevent Virtual Log File problem)

So far you have seen how to fix the problem of the high number of Virtual Log Files but if your data is growing and you have a high volume of transactions (which most of us have and that is the main purpose of the database) then you might reach to the same position very shortly, if you do not take preventive measures for this problem.

到目前为止，您已经看到了如何解决大量虚拟日志文件的问题，但是如果您的数据在增长并且您有大量的事务（我们大多数人都有，这是数据库的主要目的），那么您如果您不针对此问题采取预防措施，则可能很快就会到达同一位置。

The preventive measures are also very easy and do NOT require any downtime. Just make sure that you have updated the Initial Log file size to at least 1GB or to the normal operation size. This can be 20GB or whatever you might think it is suitable. Also, the most critical part is to fix the Growth Rate of the Log file. This should be at least 1 GB or higher based on your database usage. Under 1 GB is not recommended for medium to high databases.

预防措施也非常容易，不需要任何停机时间。 只要确保已将“初始日志”文件大小更新为至少1GB或正常操作大小即可。 可以是20GB，也可以是您认为合适的任何容量。 另外，最关键的部分是修复日志文件的增长率。 根据您的数据库使用情况，该大小至少应为1 GB或更高。 不建议将1 GB以下的磁盘用于中型到高级数据库。

我创建高VLF并在以后修复它们的示例 (Example where I created high VLFs and fixed them later)

I had a small database which had 1 MB Log file growth set (the core problem). The initial Virtual Log File count, which was 4, was excellent and I had no issues.

我有一个小型数据库，其中设置了1 MB的日志文件增长（核心问题）。 最初的虚拟日志文件计数为4，非常好，而且我没有任何问题。

Then I added a huge amount of data in the database and the Virtual Log Files grew aggressively and it increased to 3,000+. This number was alarming as it could cause high recovery times in case of a crash.

然后，我在数据库中添加了大量数据，虚拟日志文件激增，并增加到3,000多个。 这个数字令人震惊，因为如果发生崩溃，它可能导致高恢复时间。

So, I had to fix this situation. I took a transaction log backup, issued a CHECKPOINT and shrunk the database log file.

因此，我不得不解决这种情况。 我进行了事务日志备份，发出了CHECKPOINT并缩小了数据库日志文件。

After that, the number of Virtual Log Files was fixed as shown below:

此后，虚拟日志文件的数量已固定，如下所示：

Now, after fixing the current situation, I took the preventive measure to make sure this does not happen in future.

现在，在解决当前情况之后，我采取了预防措施以确保将来不会发生这种情况。

I fixed the initial file size and growth rates as down below:

我将初始文件大小和增长率固定如下：

Now I had the fix in place and I added a much larger amount of data into the database, the log file grew aggressively but not the Virtual Log Files which was a success!

现在，我有了修复程序，并向数据库中添加了大量数据，日志文件激增，但虚拟日志文件却没有成功！

摘要 (Summary)

Virtual Log Files are extremely important and are very easily managed. Generally speaking, these are often ignored by DBAs. Now you have a pretty good idea about it and you will always take care about the Virtual Log Files in all the environments in your organization.

虚拟日志文件非常重要，并且易于管理。 一般来说，这些通常被DBA忽略。 现在您已经有了一个很好的主意，并且您将始终在组织中所有环境中关心虚拟日志文件。

翻译自: https://www.sqlshack.com/what-is-sql-server-virtual-log-file-and-how-to-monitor-it/