We are seeing restore taking more than 25 minutes for a simple 4 mb file .Restore progresses to 100%,but it wont update any status from there
When checked in error log ,we could see below error…
Process 0:0:0 (0x12c8) Worker 0x000000CB5481A160 appears to be non-yielding on Scheduler 0. Thread creation time: 13223894899341. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 2%. System Idle 92%. Interval: 70083 ms.
We are able to match the worker address with session id of restore as shown in below screenshot
further we could see wait type of the spid for restore is 'PREEMPTIVE_OS_CREATEFILE'
Below are some more details
Version:
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) – 12.0.6329.1 (X64)
Jul 20 2019 21:42:29
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )
windows details :
windows serrver 2012 R2 standard
actual file sizes of this mdf,ndf ,ldf are 0.5,0.4 and 1 GB respectively.backup file is 4 MB
File speeds during restore are below 50 MS.
Any ideas on what may cause this behaviour ..?
Best Answer
The size of the backup file is only partly relevant. The restore process first have to create the database files (the "containers" as I like to think of them) and they need to have the same size as when the backup was produced.
So you have have a database with only small amount of information, but sitting in large database files.
Instant File Initialization can help to some extent, but only for database files. I.e., not for transaction log files. See for instant this for more info: https://www.brentozar.com/blitz/instant-file-initialization/
If you want to know the size of the file to be created, you can use RESTORE FILELISTONLY:
You might need a FILE option if you have more than one backup in the file (just like in the RESTORE command you are running).