Post reply

Restore is not progressing at all . Please kindly help

  • 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

     

  • January 24, 2022 at 2:48 pm

    #3976539

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 24, 2022 at 8:26 pm

    #3976688

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • January 24, 2022 at 11:16 pm

    #3976744

    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)
    Intro to Tally Tables and Functions

  • January 25, 2022 at 7:51 am

    #3976859

    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

  • January 26, 2022 at 9:59 pm

    #3977665

    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)
    Intro to Tally Tables and Functions

  • January 28, 2022 at 5:27 am

    #3978170

    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

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

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