3 responses

  1. Tim
    2023-04-19

    In the past, I was told IFI can’t be done on log files as if the log file was created using the same blocks on disk as an older, deleted, log file, it could potentially cause issues and therefore every block had to be zeroed out forcing writes to the whole log file at creation and every growth.
    In your example, the create database statement will use the model database which has a very tiny 3 or 8 MB log file which may take less than 1 second to write to. I wonder if you created a database with 10 GB data file and 3 MB log file and compare the time to a 3 MB data file and 10 GB log file.
    If IFI was true for both data and log files, the db will be create in the same amount of time.

    See the Redgate post for other ways to check if IFI is turned on
    //www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/instant-file-initialization/

    SELECT servicename, instant_file_initialization_enabled
    FROM sys.dm_server_services
    WHERE servicename like ‘SQL Server (%’;

    Reply

    • Wayne Sheffield
      2023-05-02

      Hi Tim,
      Thanks for your comment.
      First, IFI is not used at all when creating log files.
      However, starting with SQL Server 2022, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization. Prior to SQL 2022, growth events would not be able to use IFI on the log files either.

      Note that your query for identifying if IFI is enabled works for starting with SQL 2012. There’s always older, non-supported versions out there, and examining the SQL Server log is how it needs to be done for those versions.

      Reply

Leave a Reply to let me know how you liked this post

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top
mobile desktop