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
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 ..?