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.'