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.
