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
-