Post reply

SQL 40 hour recovery

  • March 21, 2014 at 6:01 pm

    #284908

    Hi,

    Last weekend some inadvertently restarted SQL and something was running that caused a database to have an estimated recovery time of 40 hours. We had to restore the database and logs from an earlier time. Our log backups went from 100mb every 20 minutes to 96gb in the 20 minutes before the restore. We have the full backup and log backup including the final 96gb backup. Is it possible to find out what was running that caused the long database recovery?

    Thanks

    Paul

  • March 22, 2014 at 4:52 am

    #1699301

    You'd need to run the restore... which is probably going to take quite a while. 96gb is a reasonably large number of open transactions.

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

  • March 22, 2014 at 8:13 am

    #1699310

    Hard to say without investigation, but I'd guess that someone opened a transaction sometime between the log backup that was small and the one that was large and didn't commit it. Or started some huge data modification operation.

    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

  • March 22, 2014 at 8:16 am

    #1699312

    Hi Thanks for your reply, what investigation can i perform?I'm quite stumped on this trying to look at it in the past.

    Thanks

    Paul

  • March 22, 2014 at 8:22 am

    #1699314

    Read the raw log backups. Not trivial, not easy, no documentation available and probably several hours at best. To be honest, not something I'd even do unless someone was insisting on knowing the exact cause.

    First check jobs, see what was running, check default trace, check error log. There may be hints in there.

    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

  • March 22, 2014 at 8:29 am

    #1699315

    AH How can I check the default trace? That sounds like my plan 🙂

    Thanks

    Paul

  • March 22, 2014 at 9:43 am

    #1699317

    Default trace won't show much. Objects created and dropped, little else.

    Search this site, there are a few articles on the subject.

    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

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

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