So what is it?
It's cool.
Err...More specifically it's a part of Windows that deals with writing files to disk. I
won't go into the file system behaviour, but when dealing with SQL Server it comes
into play when the database grows, or a database is created/has space allocated.
When you allocate space for a database, the file(s) are zeroed out. That means
all the allocated space has 0's written to it. While it works for small database
files, larger files tend to take much longer to allocate. A question then
comes up as: Well why don't you zero out the file as you need it, instead of doing
it at the beginning? Instant Intialization now comes into play.
It allows for data to be zeroed out on the fly. That 10GB space you just allocated
for database growth doesn't have to be scrubbed immediately. It can be dealt
with when it's actually needed. Pretty cool, indeed.
So how do I use it?
It's actually pretty straightforward. You give your SQL Server service account or
the local group SQLServerMSSQLUser$instancename the SE_MANAGE_VOLUME_NAME privilege.
The account or local group has to be allowed the "Perform Volume Maintenance Task"
local security right. The local admin account has this priveledge in the first place,
so if you are using that account as the service account you don't need to do anything.
HOWEVER(!) Don't use the fricken admin account as your SQL Server Service Account.
Bad things happen! With that being said,
onto the next point.
If you made the changes while the Service is running, you have to restart the service.
It's an on startup check that is done. Also, it's only available on SQL Server 2005+.
That's actually all there is to it.
Related posts
This is an auto-generated list