SDU Tools: Check Instant File Initialization (IFI) State for SQL Server

When SQL Server requests storage space from the operating system, the default action is for the OS to write zeroes over all the space before it’s presented to SQL Server to use. This is to ensure that the SQL Server process cannot read the data that was previously on that section of the storage (drive).

This can cause significant delays in at least two common situations:

• A file needs to grow
• Space needs to be preallocated during a database restore.

If this security issue isn’t a concern for you (if you have a dedicated SQL Server system, or you have a corporate SAN then it’s unlikely to be), then SQL Server has an option called Instant File Initialization that avoids all the writing of zeroes.

In recent SQL Server installers, there is an option to enable this during installation.

But people often wonder if they do have it enabled. So in our free SDU Tools for developers and DBAs, we added a procedure to make that easy: CheckInstantFileInitializationState.

It works by creating a database called [SDU_Temp_04421BA2-6229-4182-AA1D-2155A6710551] and looking in the SQL Server logs to see if there are messages about zeroing the related files. So, to run this procedure, you need permission to be able to create a database in the default locations.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

2019-11-27