Update on 9/3/2013:  Added a continuation of this post here for Instance Database File Initialization.

Welcome to the world of Database File Initialization. It’s a wonderful concept. Its not something you’ll encounter every day, but when you do, you can see it in action (or perceived inaction) every single time .

Database File Initialization: Let’s set the stage

When you create a new database or add a new file(s) to an existing database, for example, using the following command:

[sourcecode language=”sql”]
ALTER DATABASE [SomeDB]
ADD FILE
(
NAME = ‘StopGap_Primary.ndf’
, FILENAME = ‘T:\SQLData\SomeDB\StopGap_Primary.ndf’
, SIZE = 100 GB
, MAXSIZE = 200 GB
, FILEGROWTH= 10 GB
)
TO FILEGROUP [PRIMARY]

[/sourcecode]

Sql Server goes to the disk drive (T:\SQLData\SomeDB\) and tries to create a MDF/NDF/LDF file of specified size, in this case a 100 GB file. Creating a file, of any size, by itself is not time consuming. It’s mostly metadata operation where it communicates with the disk subsystem that the disk address locations (clusters/extents/sectors/stripes) from here to there are assigned to a particular file. But Sql Server does more than that. It actually goes to each disk location and fills it with zeroes. This process of initializing a new file with zeroes is what takes time. This is an important step and there is a reason for it.

Two reasons:

• When you  start writing data to this file, the operation is faster. Sql Server does not need to zero-out the file locations before writing new data.
• Not doing it is not a sound practice (described below – behind the scenes)

Behind the scenes:

When you delete a file, Operating System does not go to the disk location and delete the contents stored in the disk location. It just does a meta data operation that says, from now on the address locations (previously occupied by this file) are now free and available for reuse. But the content still exists on the disk and if the disk were to fall in the wrong hands, one could still recover data on the disk and retrieve contents of the deleted file.

To keep it simple, I am stopping here, but you can dig deeper and find out more by asking ‘why’ at every step. Ex: Why does Sql need to worry about previously deleted files? etc. But if this is a problems for you, you could take advantage of Instant file Initialization.

Instant file Initialization

Instant File Initialization allows you to quickly allocate file space by skipping the step of zeroing the allocated disk space. This is much faster and when you start writing new data it overwrites any existing content on the disk. To do this, assign SE_MANAGE_VOLUME_NAME permission to the user account under which Sql Server Service runs.

Note:

• Please note that Log files cannot be initialized instantaneously.
• When TDE (Transparent Data Encryption) is enabled the data files cannot be initialized instantaneously.

From the MSDN article, the following actions trigger File Initialization.

• Create a database.
• Add files, log or data, to an existing database.
• Increase the size of an existing file (including autogrow operations).
• Restore a database or filegroup.

Hope this helps,
_Sqltimes