Tip:
Highlight text to annotate it
X
In this demo, I'm going to show you how to turn on Instant Data File Initialization,
and validate that it's been turned on successfully. The first thing I'm going to do is run some
code, and show that it's currently turned off, and this is the way it comes out-of-the-box
by default. So what I'm going to do is I'm going to turn on a couple of trace flags,
and create a database in the same connection, and we should see some messages in the Error
Log about what happened when the database files were initialized. Let me open up the
Error Logs here. We can see that the .mdf -- or primary data file -- was zeroed out,
and the log file was also zeroed out. Instant Data File Initialization skips the zeroing
on data files, so after we turn it on, we're going to run this test again, and we shouldn't
see any messages related to zeroing out of the data files. Let's close this, and now
we'll go and turn on the permission. To do this, we go to Start, Administrative Tools,
Local Security Policy. And then Local Policies, scroll down and find Perform Volume Maintenance
Tasks. What I'm going to do is I'm going to add the local service account group to this
permission here -- I guess it's a user right. There is my local group. And now it's added
to that policy. What we have to do now is restart the instance in order for it to take
effect. Okay, so my instance should be back up now, and what I'm going to do is I'm going
to drop the database that I created, so I can create it again. And then I'll just run
this code again, refresh the Error Log node, and open this up again. You can see where
we set the trace flag and now you can see there's no message about zeroing of data files.
So we've successfully turned it on. Thanks for watching!