January 5, 2017 at 12:24 am
Hi all.
In my office we are using MS sql server 2008r2.
We have a backup files but one of the files takes too long to be restored. Approximately it should not take more than 5 minutes and sometimes took 1hr which is very headache.
I need to know what is the exactly problem and how to fix it. The size of backup file is 3gb.
Your help guyz
Note:
Am junior DBA
January 5, 2017 at 1:30 am
sosbgenius20 (1/5/2017)
Hi all.In my office we are using MS sql server 2008r2.
We have a backup files but one of the files takes too long to be restored. Approximately it should not take more than 5 minutes and sometimes took 1hr which is very headache.
I need to know what is the exactly problem and how to fix it. The size of backup file is 3gb.
Your help guyz
Note:
Am junior DBA
Do you have Instant File Initialization enabled?
😎
January 5, 2017 at 1:52 am
Does the restore spend a lot of time at 99-100% complete, or a long time at the beginning (0%), or is it slow throughout?
If it's taking a long time to finish off, then check the VLFs (run DBCC LOGINFO) on the source / original database - you might have a lot (more than 1000), which will cause any restore / restart operation to take a long time. Then fix this by shrinking the TLog, resetting the autogrowth to something more sensible for the size of your database, and regrowing the log. (You *are* doing regular transaction log backups?)
If it's slow at the beginning, then check for Instant File Initialisation / "Perform Volume Maintenance Tasks" - ensure that this permission has been granted to the SQL Server service account
If it's slow throughout, then your storage system could be overloaded...
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 5, 2017 at 4:42 am
Everything everyone else has already said, plus, to help troubleshoot, you can look at sys.dm_os_wait_stats before and after running the restore to understand where the slow downs are occurring. You can see waits in real time by querying sys.dm_exec_requests, but that's not going to be the most effective way to see all the waits. You can also capture wait events using extended events (although this is more of a pain in 2008R2 than in more modern versions of SQL Server).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2017 at 11:20 am
Also, look at the size of the log file in the original db. When you restore, the entire log file must be pre-formatted (even if IFI is on -- IFI affects only data files).
If necessary, shrink the log file on the original db prior to backup, then re-expand it after the backup. You may need to re-expand in stages, such as by 4GB or 6GB at a time, if it's a (very) large log file.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply