Verifying Instant File Initialization

Ran into a few issues verifying instant file initialization. I was trying to ensure that file initialization was enabled, but found out that running the xp_cmd to execute whoami /priv could be inaccurate when I’m not running it from the account that has the privileges. This means that if my sql service account has different permissions than I do, I could get the incorrect reading on if it is enabled.

Paul Randal covers a second approach using the sysinternals tool Accesschk, which seems promising. However, in my case, I didn’t have permissions to run in the environment was I was trying to check. I found a way to do this by rereading original article in which Paul Randal demonstrates the usage of trace flags 3004,3605. This provided a very simple way to quickly ensure I was getting the correct results back. For even more detail on this, I highly recommend his Logging, Recovery, and Transaction Log course. I adapted pieces of his script for my quick error check on this issue.

Successfully Verifying

Successfully added instant file initialization should mean when you review the log you will not have any MDF showing up in the error log for zeroing. I adapted the sql script for reading the error log in a more filtered manner from this post: SQL Internals Useful Parameters for XP Reader (2014)

Verifying Instant File Initialization By Creation of Dummy DB

GitHub

-- TURN ON TRACE FLAG FOR IDENTIFYING INITIALIZATION OF DATA FILE/LOG FILE
dbcc traceon (3605, 3004, -1);
go
-- CREATE DB TO ALLOW TRACE TO LOG ACTIVITY
create database [DropMe_IsFileInitializationEnabled]
go

--TURN OFF TRACE FLAG 
dbcc traceoff (3605, 3004, -1);
go

declare @InstanceName nvarchar(4000)
       ,@LogType      int
       ,@ArchiveID    int
       ,@Filter1Text  nvarchar(4000)
       ,@Filter2Text  nvarchar(4000)
       ,@FirstEntry   datetime
       ,@LastEntry    datetime

select
    @InstanceName = null                         -- Don't know :)
   ,@LogType = 1                                 -- 1 = ERRORLOG
   ,@ArchiveID = 0                               -- File Extension 0 
   ,@Filter1Text = null            -- First Text Filter
   ,@Filter2Text = null                          -- Second Text Filter
   ,@FirstEntry = dateadd(minute, -5, getdate()) -- Start Date
   ,@LastEntry = dateadd(minute, 5, getdate())   -- End Date

exec master.dbo.xp_readerrorlog @ArchiveID
                               ,@LogType
                               ,@Filter1Text
                               ,@Filter2Text
                               ,@FirstEntry
                               ,@LastEntry
                               ,N'asc'
                               ,@InstanceName

go

if exists (
        select
            *
        from
            sys.databases as d
        where
            name = '[DropMe_IsFileInitializationEnabled]'
    )
begin
    drop database [DropMe_IsFileInitializationEnabled]
end

successfully-verifying_v1khio