As part of a recurrent discussion on “How to set up a stabil and performing SQL Server?” I stumble on a regular basis about the (perhaps often underestimated) possibility of “Instant File Initialization”. I didn’t know this parameter until 1-2 years, but now it is part of our standard, how we install SQL Server.
I’ve got still a conversation with one of my DB2 colleagues in mind when their were just planning/testing a DB2 server migration…
He wanted to create a new database and had to wait until the respective data files were created… that needs a long time for a 100GB large datafiles.
MS SQL makes here no difference to IBM DB2, both database systems overwrite the newly occupying disk space the new data files or extensions of the data files in the file system first with zeroes, so as to ensure that old fragments were actually overwritten by already deleted files.
The data areas are thus adjusted, initialized in this case.
Within a restore there are now “growing”-Events which has to be filled up with zeros, cause the file were created as big as needed. So the restore does not require a previous filling / overwrite the data area.
How to check if Instant File Initialization is activated:
- Activate Trace Flags 3004 (DBCC TRACEON(3004,-1);)
- Activate Trace Flags 3605 (DBCC TRACEON(3605,-1);)
- Create Test-database
- have a look into SQL Server Error Log
Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.
