Cranky Series: Use Instant File Initialization

2

August 20, 2012 by Mike Hillwig

I hate waiting for anything.  It must be true that I’ve become an impatient New Englander. Oh, who am I kidding? I’ve always been impatient.

Instant file initialization is such an easy win, and it’s pretty easy to do.  Within your security policy settings, make sure your SQL Server service account has the “Perform Volume Management Tasks” permission. Yeah, it’s that easy.

When SQL Server tells the operating system that it’s going to create a 10 GB file (or whatever the size), instead of growing it byte by byte, it just plunks down that big file at once. That’s pretty awesome.

Keep in mind that this doesn’t work for transaction log files. But it does for data files, meaning database creation and restores run much faster.