SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by John Sansom / SQL Server Instant File Initialization (PQW) / Latest Posts

RE: SQL Server Instant File Initialization (PQW)

mohammed moinudheen — Sat, 07 Apr 2012 04:56:56 GMT

[quote][b]John.Sansom (4/7/2012)[/b][hr]Mohammed, good man. Thanks for stepping in and helping heb1014 in my absence, I appreciate it.[/quote]You are welcome, :-)

RE: SQL Server Instant File Initialization (PQW)

John.Sansom — Sat, 07 Apr 2012 04:33:17 GMT

Mohammed, good man. Thanks for stepping in and helping heb1014 in my absence, I appreciate it.

RE: SQL Server Instant File Initialization (PQW)

heb1014 — Wed, 04 Apr 2012 09:22:35 GMT

Thanks Mohammed! I must have read too fast. Both flags have to be enabled to see the zeroing in the log and data files. Then, the service account must be added to the volume maintenance policy (followed by a restart).Works now!

RE: SQL Server Instant File Initialization (PQW)

mohammed moinudheen — Tue, 03 Apr 2012 23:28:21 GMT

[quote][b]heb1014 (4/3/2012)[/b][hr]I just created a test database on my Dev machine and I don't see anything in the log about zeroing the data file or log file. What am I missing?Thanks![/quote]Did you enable those traces like how John had described.

RE: SQL Server Instant File Initialization (PQW)

Nitya — Tue, 03 Apr 2012 23:10:14 GMT

Try Creating a database with a large filesize. e.g. 8gb or more and see how long it takes with instant initialization and with out

RE: SQL Server Instant File Initialization (PQW)

heb1014 — Tue, 03 Apr 2012 11:56:01 GMT

I just created a test database on my Dev machine and I don't see anything in the log about zeroing the data file or log file. What am I missing?Thanks!

RE: SQL Server Instant File Initialization (PQW)

John.Sansom — Tue, 03 Apr 2012 03:48:24 GMT

Thanks Mike, glad you found it useful. Always test those changes in DEV first ;-)

RE: SQL Server Instant File Initialization (PQW)

SQLDBA360 — Tue, 03 Apr 2012 03:43:06 GMT

Great artical John, i'm probably not alone in thinking why haven't I done that already :-) (implementing it in DEV as I type), looking forward to your next post.Mike

RE: SQL Server Instant File Initialization (PQW)

Edward Sun — Mon, 02 Apr 2012 11:34:01 GMT

Thanks much for this article. It implies that it's more important to pre-size the tlog files than the data files because the tlog files cannot take the advantage of Instant File Intialization.

RE: SQL Server Instant File Initialization (PQW)

John.Sansom — Mon, 02 Apr 2012 08:43:32 GMT

Thanks Mohammed! You can never have too many PQWs :-)

RE: SQL Server Instant File Initialization (PQW)

mohammed moinudheen — Mon, 02 Apr 2012 08:01:46 GMT

Good job John in explaining this concept. Looking forward for your future PQWs. :-)

RE: SQL Server Instant File Initialization (PQW)

gmmastros — Mon, 02 Apr 2012 06:51:46 GMT

I wrote SQLCop a couple years ago. SQLCop can also detect whether Instant File Initialization is enabled.Download SQLCop from [url]http://sqlcop.lessthandot.com[/url]Run SQLCopLog in to any database on the server.Expand "Configuration"Click on "Instant File Initialization"SQLCop will also show you a blog written by Brad M. McGehee that shows you how to enable Instant File Initialization.

RE: SQL Server Instant File Initialization (PQW)

John.Sansom — Mon, 02 Apr 2012 04:35:50 GMT

Thanks NCS.That's a good suggestion.

RE: SQL Server Instant File Initialization (PQW)

Nitya — Mon, 02 Apr 2012 04:32:35 GMT

Awesome post. Been using the feature ever since I learnt from Kim Tripp (> 4years) A little bit of explanation around why log files are not zero initialized would have made it even more complete.ThxNCS

RE: SQL Server Instant File Initialization (PQW)

John.Sansom — Mon, 02 Apr 2012 02:50:32 GMT

Thanks Johan!

RE: SQL Server Instant File Initialization (PQW)

ALZDBA — Mon, 02 Apr 2012 02:43:12 GMT

great summary, John !It's good you mention "DB restores" as well, as it is a "non trivial" usage area in many cases. :exclamationmark:

SQL Server Instant File Initialization (PQW)

John.Sansom — Mon, 02 Apr 2012 00:03:39 GMT

Comments posted to this topic are about the item [B]SQL Server Instant File Initialization (PQW)[/B]