SQL Server誤區30日談 第14天 消除日記後會將相干的LSN填零初始化。本站提示廣大學習愛好者：（SQL Server誤區30日談 第14天 消除日記後會將相干的LSN填零初始化）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server誤區30日談 第14天 消除日記後會將相干的LSN填零初始化正文
誤區 #14.消除日記後會將相干的LSN填零初始化
毛病
當日記文件在手動增加，主動增加和創立時都邑停止填零初始化操作。然則請不要把這個進程和按期消除日記的進程弄混。日記截斷僅僅意味著將一個或多個VLF標志為不運動以便被反復應用。在日記消除的進程中，並沒有任何日記被消除或是填0。“消除日記”和”截斷日記”意思是一樣的，但都屬於用詞欠妥，由於在這個進程中日記的年夜小不會有任何轉變。
你可以在我的博客中看到有關日記文件填零初始化的博文：Search Engine Q&A #24: Why can't the transaction log use instant initialization?。和我宣布在TechNet雜志的文章:Understanding Logging and Recovery in SQL Server。
你可以經由過程跟蹤標志3004來檢查SQL Server對日記文件停止填零初始化的進程。將這個追蹤標志翻開當日記文件增加時，你便可以在SQL Server日記中看到相干信息，上面是測試代碼:
DBCC TRACEON (3004, 3605);
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 = 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
響應的，在日記中你可以看到:
2010-04-13 13:20:27.55 spid53 DBCC TRACEON 3004, server process ID (SPID) 53. This is an 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 an 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 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.
(下圖是譯者測試的成果):
