Script to verify if "Instant File Initialization" is Enabled

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

--  Once you have added the SQL Server security account to this security policy 
--  you need to then execute gpupdate /force from the command prompt and restart 
--  the SQL Service for this change to take effect.

USE master
SET NOCOUNT ON

-- If a database named DummyTestDB_VerifyIFI already exists drop it. 
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
    DROP DATABASE DummyTestDB_VerifyIFI

-- Temp table to hold output from sp_readerrorlog. 
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL 
    DROP TABLE #SqlLogs

GO

CREATE TABLE #SqlLogs(LogDate datetime, 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_VerifyIFI 
GO

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

-- Remove the dummy database. 
DROP DATABASE DummyTestDB_VerifyIFI

-- Now go check the output in the current SQL Server errorlog 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_VerifyIFI.mdf%'
              AND LogDate > DATEADD(HOUR, -1, LogDate))
    PRINT 'We do NOT have instant file initialization.'
ELSE
    PRINT 'We have instant file initialization.'

Setting the Number of ERRORLOG files

For some reason, the default number of ERRORLOG files maintained by SQL Server defaults to six. This has always seemed like a poor choice to me, as it makes it impossible to investigate events that may have occurred many months ago. Fortunately, this is a simple Registry setting, and we can use the undocumented xp_instance_regwrite system stored procedure to set it to the value we want.

-------------------------------------------------------------------------------
-- Set the number of ERRORLOG files to the maximum value of 99 instead of the  
-- default of 6. This is especially useful if you also set up a Job to cycle   
-- the ERRORLOG files every week or so, to keep them small enough to load      
-- comfortably.                                                                
-------------------------------------------------------------------------------

EXEC xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', 
 N'NumErrorLogs', 
 REG_DWORD, 
 99

GO

See this SQLAdmin post at xp_instance_regwrite syntax for details on how to use that procedure.