Restore is not progressing at all . Please kindly help

Question

Restore is not progressing at all . Please kindly help

WhiteLotus

SSChampion

Points: 12122
More actions
January 24, 2022 at 7:22 am

#3976394

Hi All ,
I need to restore database 1.2 TB .. bak files are on network share drive .
the time i run restore .. it shows no progress . Status is suspended ,, it has been running for 1 hour now and no progress
when i restored small database less than 1 Gb . It worked ..
any feedback is much appreciated
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

Wait.

A terrabyte restore could take several hours depending on your disk, the amount of recovery needed, contention on resources with running processes...

Wait.

Or, look to the wait statistics to see what things are waiting on, especially what the RESTORE process is waiting on. If there are blocking processes, you could try killing them (with all the implications that brings).

Probably waiting is the best bet.

"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

Jeffrey Williams SSC Guru Points: 90309 More actions · Answer 2

Here is a script you can use to monitor the restore process

 Select r.command
      , s.text
      , r.start_time
      , r.percent_complete
      , running_time = concat(cast(datediff(second, r.start_time, getdate()) / 3600 As varchar(3)), ' hour(s), '
                            , cast(datediff(second, r.start_time, getdate()) % 3600 / 60 As varchar(2)), 'min, '
                            , cast(datediff(second, r.start_time, getdate()) % 60 As varchar(2)), 'sec')
      , est_time_remaining = concat(cast((r.estimated_completion_time / 3600000) As varchar(3)),  ' hour(s), '
                                  , cast((r.estimated_completion_time % 3600000) / 60000 As varchar(2)), 'min, '
                                  , cast((r.estimated_completion_time % 60000) / 1000 As varchar(2)), 'sec')
      , dateadd(second, r.estimated_completion_time / 1000, getdate()) As est_completion_time
   From sys.dm_exec_requests                     r
  Cross Apply sys.dm_exec_sql_text(r.sql_handle) s
  Where r.command Like 'DBCC%'
     Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');

If you can copy the backup file to a local drive - it would probably perform much better, depends on your network. One thing you can try is setting MAXTRANSFERSIZE on the restore (and backups for that matter). The default setting is 64K and increasing up to the maximum of 4MB could have a dramatic effect on how long it takes. Some testing may be needed - to determine if 1MB, 2MB or 4MB works better on your systems.

Just for an example - switching from the default to 4MB allowed a restore of a 6TB+ database to be completed in about 34 minutes vs the 2.5 hours it was previously taking. You can also look into setting BUFFERCOUNT - but I normally let SQL Server determine that itself.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

WhiteLotus wrote:

Hi All ,
I need to restore database 1.2 TB .. bak files are on network share drive .
the time i run restore .. it shows no progress . Status is suspended ,, it has been running for 1 hour now and no progress
when i restored small database less than 1 Gb . It worked ..
any feedback is much appreciated
Thank you

Do you have "Instant File Initialization" enabled?

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

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 4

Also keep in mind by default it will only report progress every 10 % !

Use Jeffrey's script to have the prognosis.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

So, enquiring minds want to know... Is it done yet? 😀

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

WhiteLotus SSChampion Points: 12122 More actions · Answer 6

Guys .. it took 8 hours to restore and i found out the memory is only 12 GB hahaha

thats why it is very very slow !!!

thanks for the feedback