SQL Server Performance

How to reduce the restore time

Discussion in 'SQL Server Log Shipping' started by xiebo2010cx, May 3, 2007.

  1. xiebo2010cx Member

    Folks,

    I support a pair of SQL 2K EE log shipping servers. Below are the details.

    Primary server: Backing up transLog every 30 mins from 12:00AM to 11:59PM

    Secondary server:
    Copying transLog file every 30 mins from 12:05AM to 11:59PM
    restoring only twice per day, 3AM and 3PM.

    Here comes the performance problem, when restoring, secondary server has to restore 24 transLog files copied from primary servers one time. Sometimes, one translog files sizes around 2GB, total restoring data nears 45GB. It took 8 hours to restore secondary database last time at 3PM.

    Folks, any idea on how to reduce the restoring time based on my situation.





    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  2. techbabu303 New Member

    I am not expert in this area but fail to understand some things's

    1. If your are copying over the TLOG files every 30 minutes to secondary server, why dont you restore them once it is copied over ? this would reduce time to restore than one time all 24 logs.

    2. The primary server on which TLOG backup runs I assume is OLTP data base with lot of modifiactions but 2GB for 30 minutes seems high but may be Iam wrong ....hope you can elaborate like transactions per second running on primary server ?

    Cheers
    Sat
  3. satya Moderator

    See this KBAhttp://support.microsoft.com/kb/873482 and check whether your system is falling under this issue.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. xiebo2010cx Member

    The reason why only restore twice: we have to do reporting during some time period, when restoring TLog, the database will be in loading/recovering mode.

    The primary server is really busy, with a lot of concurrent connections. Generally the TLog backup is around 1GB/30mins, sometimes it may reach 2.5GB/30mins


    Satya, I will research your KBA, and respond here.

    Thank you, Guys


    quote:Originally posted by techbabu303

    I am not expert in this area but fail to understand some things's

    1. If your are copying over the TLOG files every 30 minutes to secondary server, why dont you restore them once it is copied over ? this would reduce time to restore than one time all 24 logs.

    2. The primary server on which TLOG backup runs I assume is OLTP data base with lot of modifiactions but 2GB for 30 minutes seems high but may be Iam wrong ....hope you can elaborate like transactions per second running on primary server ?

    Cheers
    Sat

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  5. prabhabk New Member

    In my opinion for reporting server transactional replication is best compare to logshipping.I am a Entry level DBA only.You can ask the comment from experts like satya,Mohammedu

    Thanks,
    Prabhakaran
    MS SQL Server DBA
  6. MohammedU New Member

    Replication is one of the option for reporting but it depends on the size of the database and all the tables involved in replication should have the PK...

    Did you try running the differential backup twice a day few minutes before your restore time and then restore the differential backup instead of tlog.

    If I am not mistaken differential backup should be smaller than the 24 tlog backups...

    Try a shot using differential backup/restore...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. prabhabk New Member


    According to my understanding Diffrential backup size will depends on when they have taken the last full backup.Consider If the are taking full backup every sunday then differntial backup size will be more than log backup size only.



    Thanks,
    Prabhakaran
    MS SQL Server DBA
  8. MohammedU New Member

    Yes, you are correct... but better to try differential backup and see how much time it takes...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. MohammedU New Member

    Forgot to mention...
    If you want spend some money you can try third party backup tools like LiteSpeed or SQL safe...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. satya Moderator

    You are correct, in case of the recovery options then you have to decide how you want to recover the database from the available backups. Transaction log backup is easy and similar to differential backups, but the total process depends upon how you have tested your recovery options.

    quote:Originally posted by prabhabk


    According to my understanding Diffrential backup size will depends on when they have taken the last full backup.Consider If the are taking full backup every sunday then differntial backup size will be more than log backup size only.



    Thanks,
    Prabhakaran
    MS SQL Server DBA

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page