Every time the SQL data file or log file expands, it fills the newly allocated (expanded) space with zero. There are few good and bad having this feature (Zeroing the allocated space). One downside of this is, this process will block all the sessions which are writing to these files (data and log files), during this initialization period. One might debate this time period will be very small, but it could be an extremely critical one for some process.
However enabling the Instant File Initialization behavior will make sure that the aforementioned issue will not have any effects when SQL Data file is expanded. But there will be a security risk enabling this feature. When this is enabled, it could be a possibility that unallocated part of the SQL data file could contain information related to previously deleted files (OS related information). There are tools which can examine this data and people who will be having access to the data file (most probably DB Administrators) can easily see the underlying data of these unallocated areas.
Before enabling this we will check how SQL will behave with this option disabled:
DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE Sample_Database
GO
EXEC sys.sp_readerrorlog
GO
DROP DATABASE Sample_Database
GO
DBCC TRACEOFF(3004,3605,-1)
GO
You can clearly see that SQL had an operation to zero out both data and the log file. (I have only highlighted the data file [.mdf])
Enabling Instant File Initialization can be done by adding a ‘SA_MANAGE_VOLUME_NAME’ permission (also know as ‘Perform Volume Maintenance Task’) to the SQL Server Startup account. This can be done as follows:
Open the Local Security Policy Management Application. (execute secpol.msc from the run command or from command line). Double click on the ‘Perform Volume Maintenance Tasks’ which can be found In Security Settings –> Local Policies –> User Rights Assignment.
And add the SQL Server start up account to the list.
SQL Server startup account can be found in ‘Log On’ Tab in ‘SQL Server <Instance>’ in the Services.
*** Please note: SQL will check whether this feature is enabled or disable during start up. Therefore we need to restart the service once it’s enabled or disabled. Also we can only enable this for SQL data files only. We cannot enable this for log files.
After restarting the SQL Service we will execute the code snippet which we executed earlier. You will be able to see that SQL is only zeroing the log file now.
