February 16, 2017 at 7:27 am
Hi,
I am running SQL Server 2014 SP1 Std and I have a database that's < 100GB that takes a good hour to restore. Does that seem right?
I often use the GUI to do the restore, so I'll use a combination of sys.dm_exec_requests and sys.dm_exec_sql_text() to monitor the progress and it's almost as if the restore is just hung for 50 of the 60 minutes and then the last ten minutes I see the percentage complete actually start to move from nothing to something to 100% (fairly quickly).
I guess I'm just not sure what takes so long before "progress" starts to register on the restore.
Thoughts?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 16, 2017 at 7:34 am
Mike
Does the database already exist on the target server? Do you have Instant File Initialization enabled? It sounds as if it's spending 50 minutes creating and zeroing the database files.
John
February 16, 2017 at 7:46 am
Also, check the number of VLFs on the source db.
DBCC LOGINFO
If the number of rows is greater than about 200 look at reducing them:
https://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/
February 16, 2017 at 7:57 am
Thanks, guys.
John,
The database does not already exist on the target server and I don't have Instant File Initialization enabled.
Ken,
Thanks for the VLF info. I have fewer than 10 rows.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply