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)
| -- 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 |
