Home Forums SQL Server 2005 Backups extremely slow log shipping restore in standby mode RE: extremely slow log shipping restore in standby mode

  • Ray Mond (5/6/2011)


    6 hours does look like an extremely long time to create the undo file, even if the entire 5.5 GB contains uncommitted transactions.

    I agree it does seem an extremely long time, but... my experience of restoring with standby has been similar, and I resorted to using standby only for the last log file of a bunch.

    If the whole 5.5 GB contains uncommitted transactions, some of which were left over from the previous log file, then SQL Server has to re-apply the partial transactions from the previous undo file, then restore the log and apply the 5.5 GB of transactions, then roll back those same 5.5 GB worth of transactions and write them to a new undo file.

    As we've seen from some other threads on this site, rolling back a large transaction takes an order of magnitude longer than making the changes in the first place.