When does SQL SERVER write logs?

Yesterday I saw an SQL server Courseware on the internet, and I downloaded it to see what I was talking about. So I saw the following two PPT pages.

 

Because the content on the first PPT is not accurate (the log file does not contain the "log page" concept. It is only the VLF concept. It may be because we have a deep understanding of the "data page" concept, therefore, the concept of "log page" is implemented, and the PPT says that the data page in the high-speed buffer zone is updated first, and then the transaction log is written to the "log page ", it is easy to understand that the high-speed buffer is changed first, and then the log is written to the "log page" on the disk "), in addition, when I read the PPT, I compared "yellow paper" (I only saw the previous PPT, but I did not flip it twice to see the next PPT ). therefore, I think the author of the PPT has a problem with the log writing sequence. I simply checked the information and thought deeply about the log writing sequence. At the same time, I corrected some of my previous mistakes.

This article mainly includes the following content:
1. General work and Principles of SQL SERRVER log manager.
2. The instance explores the generation and write of SQL SERVER transaction logs to the disk.
3. Some other related ideas.

Part 1: the general working principle of the SQL SERVER log manager.

The log manager orchestrates and writes transaction logs. It maintains one or more consecutive dedicated memory areas called "log cache. Because SQL SERVER transaction logs must be written to log files in a certain format, one of the functions in the log cache is to orchestrate the log format. When a log cache region is full, one or more log cache regions can be used to save New log records.
The log manager maintains two log cache Queues: flushQueue and freeQueue. Among them, flushQueue includes the log cache waiting to be refreshed to the log file (physical disk); freeQueue contains the log cache that has been refreshed and can be used again.
The refresh of logs is mainly handled by a thread called "log writer". it traverses flushQueue in sequence and writes only the content in the current log cache to the disk at a time.
What triggers the log writer refresh? When a transaction is committed or the log cache is full, the current log cache is put into flushQueue, and the log writer must start to work. After the log writer is completed, the log manager receives a signal indicating that the log is successfully written, and then activates all processes waiting for the log cache to refresh.

Part 2: The instance explores the generation and writing of SQL SERVER transaction logs to disks.

When an update statement is issued and the related lock is obtained, SQL SERVER first changes the data page in the high-speed buffer. When the pages in the high-speed buffer are changed, A log record is generated and stored in the log cache. When this update statement is submitted (COMMIT, this log record in the current log cache will be first successfully refreshed to the log file on the disk, and then return the "updated successfully" Confirmation information to the client. These are the logs written when transactions are relatively small. When a transaction is large, logs are written to the disk even though the transaction is not committed.
Here I will use an instance to prove the following situations:
A. When A transaction is "small", logs will be written to the log files on the disk only when the transaction is committed.
B. When a transaction is large, logs are written to the log files on the disk even though the transaction is not committed.

Instance 1: To prove that case A is troublesome, because it is necessary to check whether there are related log records in the log files on the disk when the transaction is started but not committed. Although SQL SERVER provides an undisclosed command DBCC LOG (Database Name) to view LOG records ), however, this command will list the log records that are not actually written to the disk in the log buffer. Therefore, I have to use a well-known third-party tool Log Explorer.

Test procedure:
1. Create a test database, and the recovery mode is complete. And perform a complete backup (so that SQL SERVER can save the log, otherwise it will be regularly truncated), run the CHECKPOINT command, then, a transaction log backup is performed to cut off all inactive logs. Run the dbcc log (northwind) command to check the situation.

In the result, you can only see two logs of Operation: LOP_BEGIN_CKPT and LOP_END_CKPT. These two logs are generated after the transaction log backup, and other logs have been truncated.

2. Create a table TEST (ORDER_ID INT, zdesc varchar (100) in the TEST database, and insert a TEST data.

CREATE TABLE TEST
(
ORDER_ID INT,
Zdesc varchar (100)
)

GO

Insert into test (ORDER_ID, ZDESC)
VALUES (1, 'A ')
GO

After the execution is successful, run dbcc log (northwind) to check the LOG:

3. Run the following command:

BEGIN TRAN
Update test set zdesc = 'B'
WHERE ORDER_ID = 1

The command starts a transaction and changes the value of the ZDESC column to 'B', but the transaction is not COMMIT.
Run dbcc log (northwind) to view logs.

Check 54th logs. Starting from 54th logs, we run all the logs generated by the UPDATE Service. Because the transaction is not committed, we must find a way to check whether all logs starting with log 54 have been saved to the log file on the disk. At this time, we first change the SQL server service to manual start, and then forcibly restart the computer. After the computer is started, before SQL SERVER is started, copy the northwind LOG file to another directory (although we can run dbcc log (northwind) again after the SQL SERVER service is started on the computer to view the LOG information, however, I am worried that SQLSERRVER will perform restoration at startup to process unsubmitted logs.) We still use Log Explorer to view the Log records in the copied Log files.
Let's take a look at dbcc log (northwind:

As you can see, the previous log No. 64 and No. 65 can be seen, and the two logs are the real logs generated by the UPDATE statement, while 54-63 is a transaction in the modification work system. Then we use Log Explorer to check the logs in the previously copied Log files.

Is the details of the Log file backed up by Log Explorer for northwind. We can select a Log in the table column above, and pay attention to the LSN in the red box. LSN: 36: 87: 1 this LOG is the LOG in the order shown by the dbcc log (northiwind) command 64, that is, the CurrentLSN is: 00000036: 00000087: 0001. Obviously, the Log displayed by LOG Explorer is different from the Log displayed by the previous dbcc log (northiwind). Therefore, we can conclude that a "small" transaction has been generated before it was committed, and stored in the log buffer, but not written to the log file on the disk. Once the transaction is committed, the log will be written to the disk. In this case, you can verify it yourself.

Example 2: B is relatively simple. We only need to let SQL SERVER run a large transaction, and then observe whether the log files on the disk have been automatically increased. If yes, logs must have been written to the disk.

Tutorial steps:
1. Observe the current log file size of northiwind. Because my northwind is a newly created database, the physical size of the log file is 1 MB.
2. Run the following script and observe the physical size of the log file.

BEGIN TRAN
DECLARE @ I INT
SET @ I = 1
WHILE (@ I <99999)
BEGIN
Update test set zdesc = LEFT (NEWID (), 10)
SET @ I = @ I + 1
END

The script is encapsulated into a transaction and is not committed. After running, the physical size of the log file I observed is 38.3 MB, for example:

Obviously, although the transaction is not committed, logs in the log cache will be written to the physical disk as long as the log buffer is filled up. We rolled back the transaction in time, and we can still use dbcc log (northwind) to see these rollback logs.

Part 3: Thoughts on other related issues
1. When SQL SERVER modifies the data page in the high-speed buffer, logs are generated and placed in the log cache. Then, the log is generated by the buffer manager, sent to the log manager, or the log Manager detects the modification to the buffer, and then generates the log by itself.
2. What format should I "orchestrate" logs in the log buffer? Whether it is the format we see through dbcc log (northwind.
3. When writing logs to a disk, the log writer knows which VLF is written to the log file, that is, how to know that a VLF is the last VLF in logic.