Thursday, August 21, 2008
zeroing for log files
Instant file initialization is an excellent feature that came with Windows 2003 which is made use by SQL Server 2005. When instant file initialization is enabled, the zeroing process for the SQL Server data files is disabled which reduces substantially the time required for file write and growth processes. However, instant file initialization cannot be done for SQL Server log files. The reason is that there are parity bits required by log file sectors to help in crash recovery processes, and if zeroing is disabled, the already existing data in the sectors can be misinterpreted as parity bits and can cause issues. There is a post from Paul Randal explaining the architectural reason. Find the post at http://www.sqlskills.com/blogs/paul/2008/08/20/SearchEngineQA24WhyCantTheTransactionLogUseInstantInitialization.aspx.