SQL Server The Truth - Instant file initialisation

This is the first in a series of posts that are aimed at dispelling myths and beliefs as to how SQL Server operates. I have interviewed too many people and have been in too many sessions where statements are made as though they are fact when in reality they are not. Often the reason is due to a change in behaviour between versions or a mis-interpretation of some information.

The first is a very new one and relates to a feature in SQL Server 2005, Instant File Initialization.

What this feature does is allow SQL Server to create a file with the right size but not to make it zero it out, i.e. zero all the bytes in the file. The OS just allocates the disk space but the contents of the file is actually what is still on the disk, its not changed.

Firstly it is available in ALL editions including express. This is easily demonstrated by creating a database with a very small log and a huge data file. It should happen within seconds indicating that your service account has the correct permissions.

Secondly it DOES NOT apply to log files, these have to be zeroed. This is covered by most people, but some forget to explicitly state that Log files are excluded from instant file initialization.

This is one of the best discussions on instant file initialisation http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx



-
Published Friday, July 20, 2007 9:26 AM by simonsabin

Comments

Saturday, October 23, 2010 10:35 PM by Simons SQL Blog

# Shrinking data files - Things you shouldn’t do with SQL

This is the first in a follow up from my SQLBits and DDD sessions. Why is shrinking files bad? Shrinking