Misunderstanding #14. The associated LSN is populated with 0 when the log is cleared.
Error
The log files are manually grown, automatically grown and created with a 0 initialization operation. However, do not confuse this process with the process of periodically clearing logs. Log truncation simply means that one or more VLF are marked as inactive for reuse. In the course of the log cleanup, no log is cleared or filled in 0. The "Purge log" and "Truncate log" mean the same thing, but they all fall into the wrong word, because there is no change in the size of the log during this process.
You can see in my blog about log file 0 Initialization blog: Search Engine q&a #24: Why can ' t the transaction log use instant initialization? And the article I posted in TechNet Magazine: Understanding Logging and Recovery in SQL Server.
You can view SQL Server's process of filling 0 initialization of log files by tracking tag 3004来. When you open the tracking tag to grow the log file, you can see the relevant information in the SQL Server logs, and the following is the test code:
Copy Code code as follows:
DBCC traceon (3004, 3605);
Go
--Create database and put in simple recovery model so the log would clear on checkpoint
CREATE DATABASE logcleartest on PRIMARY (
NAME = ' Logcleartest_data ',
FILENAME = N ' D:\SQLskills\LogClearTest_data.mdf ')
LOG on (
NAME = ' Logcleartest_log ',
FILENAME = N ' D:\SQLskills\LogClearTest_log.ldf ',
SIZE = 20MB);
Go
--Error Log Mark 1
ALTER DATABASE logcleartest SET RECOVERY simple;
Go
Use Logcleartest;
Go
--Create table and fill with 10mb-so 10MB in the log
CREATE TABLE T1 (C1 INT IDENTITY, C2 CHAR (8000) DEFAULT ' a ');
Go
INSERT into T1 DEFAULT VALUES;
Go 1280
--Clear the log
CHECKPOINT;
Go
--Error Log Mark 2
ALTER DATABASE logcleartest SET RECOVERY simple;
Go
Accordingly, in the log you can see:
Copy Code code as follows:
2010-04-13 13:20:27.55 spid53 DBCC traceon 3004, server process ID (SPID) 53. This is a informational message only; No user action is required.
2010-04-13 13:20:27.55 spid53 DBCC traceon 3605, server process ID (SPID) 53. This is a informational message only; No user action is required.
2010-04-13 13:20:27.63 spid53 zeroing D:\SQLskills\LogClearTest_log.ldf from page 0 to 2560 (0x0 to 0x1400000)
2010-04-13 13:20:28.01 spid53 zeroing completed on D:\SQLskills\LogClearTest_log.ldf
2010-04-13 13:20:28.11 spid53 starting up database ' Logcleartest '.
2010-04-13 13:20:28.12 spid53 fixuplogtail () zeroing D:\SQLskills\LogClearTest_log.ldf from 0x5000 to 0x6000.
2010-04-13 13:20:28.12 spid53 zeroing D:\SQLskills\LogClearTest_log.ldf from Page 3 to [0x6000 to 0x7e000)
2010-04-13 13:20:28.14 spid53 zeroing completed on D:\SQLskills\LogClearTest_log.ldf
2010-04-13 13:20:28.16 spid53 Setting database option RECOVERY to simple for database logcleartest.
2010-04-13 13:20:29.49 spid53 Setting database option RECOVERY to simple for database logcleartest.
In the above test code, ALTER DATABASE is used as the opening and closing tag for this part of the log. The checkpoint in the two ALTER DATABASE commands does not cause a 0 operation to be completed. If you need to further verify this, use DBCC SQLPERF (LOGSPACE) to view the size of the log file before and after checkpoint, you will find that the log file size has not changed, but the percentage of usage space in the log is significantly reduced.
(The following figure is the result of a translator's test):
