SQL Server Instant File Initialization: SetFileValidData (Windows) vs fallocate (Linux

Moved from: bobsql.com


SQL Server Books Online documents Instant File Initialization and the associated security considerations.   This blog highlights the underlying file system implementations and differences in behavior between Windows and Linux.


SQL Server performs the following API calls when creating or extending (growing) data and log files.


CreateFile – Create or open a file SetEndOfFile– Establish the file size and acquire space from the I/O device SetFileValidData – Establish the valid data size If the file is a log file (LDF) SQL Server always writes a known pattern (other than zeros) to the allocated space. If the file is a