We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a known state.

Every Monday, we refresh all 4 environments to the same known state. All restore jobs start at 1am.

The backup file we restore from is a regular SQL backup taken from a SQL 2014 Server using a standard BACKUP DATABASE command. The restore command is issued by SQL Server Agent using a standard RESTORE DATABASE command.

When the daily restore runs, it takes between 2-2.5 hours to complete. On a Monday morning, that server completes in around 3 hours. This makes some sense as there are 4 servers trying to read the same backup file instead of one.

When the other 3 servers restore on Monday morning, the restore command is issued as scheduled at 1am, but they restore command then appears to do nothing until about 11am (< 10% CPU, disk and network usage).

When selecting from sys.dm_exec_requests on all 3 servers during this time, I can see that the percentage complete is 0% until about 11am, when it starts increasing fairly evenly on all 3 servers. At the same time, network and disk usage both increase to around 100Mbps - which is an expected number for our dev environment.

I'm trying to work out what is happening for the approx. 10 hours between the restore command being issued and the data being written to disk.

The file doesn't appear to be locked by any one server, as at 11am all 3 servers are able to restore at roughly the same speed.

We will look to stagger the restore, or even move it to the weekend, but I'd really like to try and work out why the server is not restoring for 10 hours.

Is there something in the restore command that would cause it to wait?

  • 2
    Check your dev servers' ERRORLOG files include a string like this: "Database Instant File Initialization: enabled". Is it there? – Denis Rubashkin Nov 5 '18 at 8:11
  • Adding to @DenisRubashkin comment, if instant file initialization shows disabled, grant perform volume maintenance tasks to the SQL Server service account or group as described in the documentation. – Dan Guzman Nov 5 '18 at 11:36
  • Hadn't thought of that. It's disabled on all 4 servers. I'll enable it and see what impact that has. – Greg Nov 6 '18 at 1:36
  • I've enabled it on all 4 servers. The server that restores nightly took 7 hours this morning (up from 2-2.5 hours). Not sure if that's just a one off, but seems unusual. The real test will be Monday – Greg Nov 8 '18 at 22:22
  • Did you get this resolved? – Max Vernon Jan 18 '19 at 1:59

Your Answer

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

Browse other questions tagged or ask your own question.