Restore takes forever
-
Hi all!
I have som pretty smal databases - the backup is app. 250 MB.
Its a developement enviroment, so i restore very often.
It takes app. a minute or so before the restore reaches 100%, and then 15 to 30 minutes before i get a "Restore completed" message.
The pc is an I7 based PC, and the backups etc is placed on a 2 TB harddisk - not the primary, but "Onboard". I have only 16 GB RAM - perhaps the explanation?
I have upgraded the SQL server over the years from 2005, but it has allways been the case.
Backups comes from various sql servers - most usual is 2012.
comments?
Med venlig hilsen/Best Regards/Mit Freundlichen Grüßen/ Kveðja/ Distinti saluti/ Met beste groete
Edvard
-
Quick question, what is the recovery model of the database?
😎
-
I suspect Eirikur is looking at recovery as the culprit. Sounds right too. How big is the log?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
and.... how many VLFs - higher number does delay restore.
and... if restoring from a lower SQL version system objects will also be upgraded - this adds to restore time.
-
Thanks!
I am pretty sure that the log is the problem. Theese DB's were created in 2011, and the logs are pretty big.
And i do not need them anyway.
Which restore method is the fastest?
Med venlig hilsen/Best Regards/Mit Freundlichen Grüßen/ Kveðja/ Distinti saluti/ Met beste groete
Edvard
- Edvard Korsbæk wrote:
Thanks!
I am pretty sure that the log is the problem. Theese DB's were created in 2011, and the logs are pretty big.
And i do not need them anyway.
Which restore method is the fastest?
A good place to start would be sharing some information 😉
😎
- What is the recovery model of the database?
- What are the backup details? (Full, differential, compression etc.)
- Log file(s) details including size, growth size, VLF count etc.
- Any other information that might be relevant, the more the merrier!
-
You may not need the T-Logs in DEV, but they can be very important in PROD. So the place to start is where the databases originate and see if your databases and backup/restore plans are adequate.
-
on those db's that case issues I would start by looking to see how many VLF's they have. and if too many get the source (production DB) fixed in the first place.
see http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/ for info
-
This is my usual settings.
The backup comes from an 2012 DB as I understand.
The primary table is 3.288 MB, and the log is 177.674 MB.
My settings as pr. attached.
Wonder about I should ask for another setting in the backup? I.E, that it is the backup thats the real problem.
Med venlig hilsen/Best Regards/Mit Freundlichen Grüßen/ Kveðja/ Distinti saluti/ Met beste groete
Edvard
-
Maybe your transaction logs are not being backed up on the source database, so they keep growing.
Assuming the source database is production, a typical backup plan is full backup weekly, differential daily, and transaction logs every 15 minutes. That's a generic example. Then the backup file should not be much larger than the database, about 4 meg.
-
As a bit of a sidebar, having only 16GB of RAM on a Developer Server where production code is developed and unit tested and has restores done so frequently seems a bit chintzy. Even some smart phones have that much memory, nowadays. I'd fix that even if I weren't having backup issues. 😉 If the system is still on spinning rust, I'd also consider an upgrade to Nvme SSDs.
Developers already have a hard enough job... at least upgrade their machine so that it's better than a decade old laptop.
It might also fix the restores (for the roll forward/back stuff, especially ) but you need to check for the number of VLFs, etc, in the log files.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is the first part of the information we need
😎
and then also this part
-
can you also provide the output of
DBCC loginfo ('TEEST')
or
sys.dm_db_log_info ( database_id )
replace database_id with database_id value from sys.databases
if it returns more than 300 rows just state how many
-
This was removed by the editor as SPAM
-
Really sounds like you may have the database in FULL recovery, but you're not running log backups. Just guessing, but from what you've posted, seems likely. However, if you answer Eirikur's question, we'd get a lot farther down the track and out of the weeds where we're all guessing at the solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply