0

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

enter image description here

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

0
2

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:

RESTORE FILELISTONLY 
FROM DISK = 'R:\mybackup.bak'

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

7
  • Actually,IFI is enabled Feb 11 '20 at 12:08
  • Administrators are granted IFI privelage and sql service account is part of administrators group,should we add sql service account seperately Feb 11 '20 at 12:14
  • I never recommend running SQL Server service with high privilege. But to answer the question, no need to add IFI to the service acct if the current is part of admin group. However, there could still be a large log file, and you never get IFI for log files. Did you check the output from RESTORE FILELISTONLY? (Btw, you can see in the errorlog file is SQL Server has IFI.) Feb 11 '20 at 12:59
  • total max file size is less than 2 GB from restore filelist only Feb 11 '20 at 13:33
  • log file is 1 GB,mdf and ndf is 1 gb Feb 11 '20 at 13:34
0

I had the same problem it was due to the size of the DB. Although the backup shows 9 MB, when I right click the DB in SQL server management and select properties, the size shown was 25 GB! What I did is that I changed the DB to "Simple Recovery", shrinked the log file,backup again, and I could restore now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.