THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Check for Instant File Initialization

Instant File initialization, IFI, is generally a good thing to have. Check out this earlier blog post of mine f you don't know what IFI is and why it is a good thing: blog. The purpose of this blog post is to provide a simple script you can use to check if you have IFI turned on.

Note that the script below uses undocumented commands, and might take a while if you have a large errorlog file...

 
USE MASTER;
SET NOCOUNT ON

-- *** WARNING: Undocumented commands used in this script !!! *** --

--Exit if a database named DummyTestDB exists
IF DB_ID('DummyTestDB') IS NOT NULL
BEGIN
  RAISERROR
('A database named DummyTestDB already exists, exiting script', 20, 1) WITH LOG
END

--Temptable to hold output from sp_readerrorlog
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL DROP TABLE #SqlLogs
GO
CREATE TABLE #SqlLogs(LogDate datetime2(0), ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

--Turn on trace flags 3004 and 3605
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

--Create a dummy database to see the output in the SQL Server Errorlog
CREATE DATABASE DummyTestDB
GO

--Turn off trace flags 3004 and 3605
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

--Remove the DummyDB
DROP DATABASE DummyTestDB;

--Now go check the output in the SQL Server Error Log File
--This can take a while if you have a large errorlog file
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS(
          
SELECT * FROM #SqlLogs
          
WHERE TEXT LIKE 'Zeroing completed%'
            
AND TEXT LIKE '%DummyTestDB.mdf%'
            
AND LogDate > DATEADD(HOUR, -1, LogDate)
        )
  
BEGIN
    PRINT
'We do NOT have instant file initialization.'
    
PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
  
END
ELSE
   BEGIN
    PRINT
'We have instant file initialization.'
  
END

Published Wednesday, October 30, 2013 6:12 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alvaro Costa said:

Great script!!

Thanks,

Alvaro Costa

alvaro.galdino@gmail.com

October 30, 2013 1:39 PM
 

LondonDBA said:

Nice script. Another way is to temporarily enable xp_cmdshell and then run:

exec xp_cmdshell 'whoami /priv'

You'll see all the permissions for the sql service account.

October 30, 2013 7:45 PM
 

Tibor Karaszi said:

Read this if you want to read more about instant file initialization (IFI). In an earlier blog post ,

March 12, 2014 7:32 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement