SQL Server errors: about 30 days. After clearing logs on the seventh day, the related LSN will be filled in and initialized.

Misunderstanding #14. After clearing the log, the related LSN will be filled with zero Initialization

Error

When the log file grows manually, automatic growth, and creation are performed, it will be filled in with zero initialization. However, do not confuse this process with the regular log clearing process. Log truncation only means marking one or more VLF as inactive for reuse. During log clearing, no logs are cleared or 0 is entered. "Log clearing" and "log truncation" mean the same, but they are all improperly used because the log size does not change during this process.

You can see A blog post about zero initialization of log files in my blog: Search Engine Q & A #24: Why can't the transaction log use instant initialization ?. And my post in TechNet: Understanding Logging and Recovery in SQL Server.

You can trace 3004 to view the SQL Server's process of entering and initializing log files. When the log file grows, you can view the relevant information in the SQL Server log. The following is the test code:

Copy codeThe Code is as follows: dbcc traceon (3004,360 5 );
GO
-- Create database and put in SIMPLE recovery model so the log will 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 = 20 MB );
GO
-- Error log mark 1
Alter database LogClearTest set recovery simple;
GO
USE LogClearTest;
GO
-- Create table and fill with 10 MB-so 10 MB in the log
Create table t1 (c1 int identity, c2 CHAR (8000) DEFAULT 'A ');
GO
Insert into t1 default values;
GO 1, 1280
-- Clear the log
CHECKPOINT;
GO
-- Error log mark 2
Alter database LogClearTest set recovery simple;
GO

Correspondingly, you can see in the log:Copy codeThe Code is as follows: 13:20:27. 55 spid53 dbcc traceon 3004, server process ID (SPID) 53. This is an informational message only; no user action is required.
13:20:27. 55 spid53 dbcc traceon 3605, server process ID (SPID) 53. This is an informational message only; no user action is required.
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 '.
13:20:28. 12 spid53 FixupLogTail () zeroing D: \ SQLskills \ LogClearTest_log.ldf from 0x5000 to 0x6000.
13:20:28. 12 spid53 Zeroing D: \ SQLskills \ LogClearTest_log.ldf from page 3 to 63 (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 serves as the start and end mark for this part of the log. The CheckPoint in the two Alter Database commands does not cause the zero filling operation. If you need to further verify this, use dbcc sqlperf (LOGSPACE) before and after the Checkpoint to view the log file size, you will find that although the log file size has not changed, however, the percentage of space used by logs is greatly reduced.

(Result of the translator's test ):