Stack Overflow Outage [closed]

Question

As a result of power loss at the PEAK data center as posted on our blog our SQL instance was shut down unexpectedly. SQL Server was unable to recover using dbcc checkdb and we decided to restore from a backup taken at 10/9/2010 00:00 PST. We do have the data and will work quickly to restore as much as we can from the timeframe between the last backup and 4:00 PST when the outage occurred. We will update this post as we have more information.

update

We have finished recovering the missing posts and post history. It was a manual process. We are still working on recovering some of the votes and comments.

update 2

comments are recovered

update 3

I am done (waffles out) ... finished migrating votes as well

Answer 1

Some restored posts have wrong vote decomposition, e.g. in https://stackoverflow.com/questions/3896802/c-programming-language-increment-decrement-operators,

Answer 2

Nice job!

One thing to note: post IDs and user IDs might have changed, probably implying that search engine results may be off for a while too.

(Update, 2010-10-11: searching Google for the titles of the examples below, now gets one the correct posts. But other searches might still yield wrong results.)

Like:

• https://stackoverflow.com/questions/3896272/why-the-image-not-getting-displayed-in-the-background-of-the-div now redirects to https://stackoverflow.com/questions/3896210/python-threads-and-global-vars/3896272#3896272 (while the original now is 3896612)

• https://stackoverflow.com/questions/3896185/need-help-with-css-sprites-plz-closed gets one https://stackoverflow.com/questions/3885247/inverted-colors-text-on-top-of-an-opengl-scene/3896185#3896185 (while the original now is 3896522)

Also: some post may show "show xx more comments" while clicking doesn't reveal them. This will probably be solved automatically by the daily process recalculating the denormalized count.

Answer 3

Looks like some votes are still missing. I lost 35 points.

Also I had upvoted 3 answers on this question Which encryption method would produce this result. I have re-upvoted them after the crash.

This question Large table querying is showing up as accepted and upvoted on my recent activity, but not on the question.

This question WCF: How to diagnose faulted channels? also shows up as an upvote on my recent activity, but has 0 upvotes on the question.

These 2 questions match the missing 35 points.

Answer 4

The metadata for question Using a strategy pattern and a command pattern is incorrect. I noticed answer count and latest activity are under-represented. (I added an answer during the period lost due to the restore.) I don't know if any others are also incorrect.

Answer 5

I'm just asking -- I've drunk the kool-aid that makes me believe that when a SQL Server database is running using the "full recovery model" (with log backups being taken regularly) that the only possible data loss that can occur (other than the situation where the disk holding the current log backup is physically damaged by the power outage) is that any transactions in progress at the time of the outage will be rolled back (but all that were successfully committed will be recovered).

Unless there is physical disk damage, when power is restored, isn't SQL Server supposed to be able to recover automatically? You say that it marked the database as "suspect". What did the SQL Server event log say was the cause of it being so marked? Did you make a copy of the database and log files as they were at that point and open a case with Microsoft to let them figure out why the recovery didn't happen? SQL Server -- I thought -- is very careful about the sequence in which it writes to the database file(s) and log file so that recovery is guaranteed.

Am I wrong that SQL Server is designed to handle exactly this situation 100.00% of the time??? Isn't it a bug -- or a hardware failure -- when it doesn't?

Even if SQL Server can't process the database and log files when it restarts (possibly due to disk damage), if you obtain functioning disk hardware, restore the most recent backup, and apply all subsequent log backups followed by the current live log file (if available), shouldn't that bring you to exactly the point of the last successfully committed transaction? If the current live log file isn't available, then you "only" get to the point of the most recent transaction log backup -- which in a busy site I would think would be maybe 10 minutes old, not hours.

I'm concerned that what happened to SO's database could happen to one of mine, so I'm wanting to know more than I do now about what exactly was wrong and whether MS has had a chance to see the problem "live" and as a result possibly make (some version of) SQL Server more robust in that situation.