Instant File Initialization in SQL Server

Hi Friends,

I was reading something interesting. Wanted to share this with you all.

What is the background process on the disk when you create a new database? The data and log files that have to be initialized for your database are initialized by overwriting any data left on the disk from previously delete files. What does this mean?

Suppose you have a 10 GB database. And you delete the database. The database certainly gets deleted and the operation cannot be undone. The space is also released to the OS. But the data still resides on the disk unless it is overwritten by some other file. Yes, you can see that OS has 10 GB free space because that space is available to be used.

Now, suppose after deleting that 10 GB database, you immediately create another database of 5 GB, SQL Server will initialize the data files for the new database by filling the file with zeros. And this may take some time. This is called zero initialization.

   

What if you want to create the files instantly? You can enable instant initialization. When you enable instant initialization, the database is created instantly but the files are not filled with zeros, instead the zeroing out operation happens later when you start writing data to the files for the first time.

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

So this is ‘a kind of’ availability feature where you can make the data files quickly available to client applications.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.