Tuesday, October 1, 2013

Enable Instant File Initialization to accelerate database restore

Today my colleague come to me and ask me why her database restore query was hang. She was going to restore a database with 200GB data file and 11GB log file.

She had run the restore command for about 15 minutes, but the restore process is still in 0%, it is not the normal situation she knows. I connected to the server and checked the running query status, the "restore database" command was waiting for the "ASYNC_IO_COMPLETION"

then I checked the disk performance, the database file is still in writing, 110MB/sec

before the database restore start writing data back to the data file, the data file needs to be initialized first. if instant file initialization is enabled, this step will be skipped for data file(only data file), looks like instant file initialization is not enabled on this server.

In order to prove it, I run the script from http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

DBCC TRACEON(3004,3605,-1)
EXEC sp_readerrorlog
DBCC TRACEOFF(3004,3605,-1)

in the error log file, I do see the
2013-10-01 18:31:05.56 spid104     Zeroing E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestFileZero.mdf from page 0 to 131232 (0x0 to 0x40140000)

so the server is not enabled  instant file initialization. the data file is 200GB, disk write speed is 100-110MB/sec, we get : the file initialization will take about 33 minutes.

I told her just waited for half hour, then you would see the progress. and finally, she told me the restore estimate time started changing after 35 minutes :)

This case proves that enabling Instant File Initialization is really important for sql performance, including database restore.


  1. Good Blog, well descrided, Thanks for sharing this information.
    Big Data and Hadoop Online Training

  2. I really appreciate this post and I like this very much. I am waiting for new post here and Please keep it up in future.. Web Design Sydney

  3. Bolavita - Situs Permainan Online Aman Dan Terpercaya

    Tersedia Bola | Live Casino | Bola Tangkas | Sabung Ayam Online | Slot Games |Togel Online Aman Dan Terpercaya

    Melayani Deposit Via Pulsa Telkomsel | XL | Gopay | OVO | LinkAja | DANA Serta Semua Bank Indonesia

    Kontak Bolavita https://linktr.ee/bolavita

  4. This comment has been removed by the author.

  5. Here is the best AWS DevOps Training in Chennai from Infycle Technologies, the best software training institute in Chennai. And we circulate the topmost demanding courses like Graphic Design and Animation, Power BI, Combo of Python + Oracle with Java, Blockchain, Artificial Intelligence, Big data, Azure Certifications, Python, Selenium Automation Testing, Machine Learning, Medical Coding, etc., with 100+ Live Practical Sessions. Reach us on call at +91-7504633633, +91-7502633633 for best offers.