Database File Initialization Disabled

When SQL Server data and log files are initialized, they overwrite any existing data left on the disk from previously deleted files. This is done by zeroing out the files (filling with zeros) during the following operations:

• Creating a database.
• Adding an extra data or log file to an existing database.
• Increasing the size of an existing file, either manually or via autogrow operations.
• Restoring a database or filegroup.

The zeroing out operation causes these tasks to take longer, which could potentially impact the performance of the SQL Server. Enable instant file initialization to bypass this operation, but be mindful of the possible security implications that are detailed in the further reading section.

Note that some SAN devices may address this through features like zero-detection, where the data isn't written to disk if a zeroing operation is detected. Therefore, the gains from implementing Database File Initialization will vary depending on the underlying hardware.

Suggested Action

Consider granting the SQL Server service account permission to perform volume maintenance tasks, which will enable database file initialization.

How to Check If Database File Initialization Is Enabled

--sys.dm_server_services may not be available on old or unpatched systems
SELECT servicename,
       instant_file_initialization_enabled,
       startup_type_desc,
       status_desc,
       service_account
FROM sys.dm_server_services;
GO

exec xp_readerrorlog 0, 1, N'Database Instant File Initialization'
GO

Further Reading

​Database File Initialization | Microsoft Docs

sys.dm_server_services (Transact-SQL) | Microsoft Docs