Restore failure on Log Shipping Secondary

  • I had a problem with a tran log filling up on a Prod database. I added second log to the DB to increase free space. Now the restore is failing in DR with an error stating "transaction log is full". I am unable to grow the current log file as the db is not in a Standby state. In sys.databases it shows a state of recovery_pending. Any attempts to alter the database return an error that it is in the middle of a restore. If I restart SQL Server will it recover the DB back to the point of the last restore and leave it in a Standby state again, or do I need to start over with a full backup at this point?? Any help is appreciated!

    Thanks,

    Josh

  • A restart is unlikely to fix that. I suspect a reinit of the log shipping (from a fresh backup) will be needed.

    Try a restart first, it's quick, but I doubt it'll help.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Gail! I was afraid that starting from scratch would be my only option at this point. Appreciate the help on a holiday!

    Josh

  • Hi Josh,

    What is the fix for the issue?. Does the restart helped you or you reinitiated the log shipping from scratch?

    I am curious to knew about the result.

    --- Babu

  • Hi Josh,

    If you just increased the database file size on the source end then it should be propogated automatically to the destination end of log shipping and there is no need of re establish log shipping.

    Please do check if you have enough drive space to expand the log file at destination by the amount you have increased at the source, if the destination doesn't have enough space you may need to shrink the source file or add extra space to destination.

  • One solution is (not pretty and certainly doesn't adhere to best practices!!!) to shrink the logs of other databases (if there actually are any others!!!) on the primary server to claim back space, once these have log shipped (and ultimately shrank the logs on the secondary server then hopefully the DB you're having problems with will have enough space to restore.

    Like i said, use this with caution as shrinking logs on production is not best practice 😉

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • We ended up starting over with a new full backup. There was plenty of space on the drive on the target server, so a lack of disk space wasn't an issue.

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

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