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)
GO
CREATE DATABASE TestFileZero
GO
EXEC sp_readerrorlog
GO
DROP DATABASE TestFileZero
GO
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.
Thank you so much for these kinds of informative blogs.We are also a digital marketing company in gurgaon and we provide the website design services,
ReplyDeleteweb development services, e-commerce development services.
website designer in noida
website designers delhi
website designers in delhi
website designing agency in delhi
website designing and development
website designing companies in delhi
website designing company delhi
website designing company in delhi ncr
website designing company in gurgaon
website designing company in new delhi
website designing company in noida
website designing company list
website designing company noida
website designing cost in delhi
website designing cost in india
website designing delhi
website designing firms in delhi
website designing in delhi
website designing in delhi ncr
website designing in gurgaon
website designing in noida
website designing services
website designing services delhi
website designing services in delhi
web design development company
web design development services
web design in delhi
web design service
web design services company
web design services in delhi
web designer company
web designer delhi
web designer in delhi
web designers delhi
web designers in delhi
web designing & development
web designing advertisement
web designing and development
web designing and development company
web designing and development services
Good Blog, well descrided, Thanks for sharing this information.
ReplyDeleteBig Data and Hadoop Online Training
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
ReplyDeleteBolavita - Situs Permainan Online Aman Dan Terpercaya
ReplyDeleteTersedia 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
This comment has been removed by the author.
ReplyDeleteHere 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.
ReplyDelete