SQL 40 hour recovery

Question

SQL 40 hour recovery

paul.belton

SSC Rookie

Points: 32
More actions
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

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

paul.belton SSC Rookie Points: 32 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

paul.belton SSC Rookie Points: 32 More actions · Answer 5

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

Thanks

Paul

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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