Instant File Initialization

Getting rid of Instant File Initialization (or enabling it if that strikes your fancy)

SQL Server 2005 introduced us to Instant File Initialization. This allows SQL Server to create files of any size without sitting there for minutes or hours (depending on the size of the files).

While this is great when creating your database, or extending your database files there is a cost to doing so. Before each data page is written the SQL Server will write all zeros to the page. It also has the potential of a security issue as any data fragments which are in the space which the file took up are going to be included in the backup and could then be read if the backup was lost.

Continued »