18

The weekly SEDE refresh is stuck, this query shows the problem occurred while copying the Gaming Stack Exchange database:

screenshot of the query result

Note: a similar thing happened before.

3
  • 1
    Worth noting that there isn't a database in SINGLE_USER mode now. So the failure condition might have a different root cause this time.
    – rene
    Jul 31 at 18:57
  • @rene hopefully something that can be fixed... Aug 1 at 7:34
  • @ShadowTheKidWizard I hear NoSql fixes a lot of SQL Server issues ...
    – rene
    Aug 1 at 17:09

1 Answer 1

15
+50

You are right that the process got stuck as it was processing the Gaming site.

Resolution will come in three parts:

  1. Fixing the problem (✔️ DONE)

    I kicked off refreshes for the missing databases, and as of 20:00 UTC all have been restored.

  2. Analyzing what went wrong (✔️ DONE)

    The SQL Server Agent job failed, and the current process does not skip databases that fail. The job itself is verbose, so any error messages written to Agent job history are drowned by success. And there is nothing in SQL Server's own errorlog. There is this error message in Agent's text file output that doesn't quite line up with the failure with Gaming:

    Msg 3948, Severity 16, State 2
    The transaction was terminated because of the availability replica config/state change or because ghost records are being deleted on the primary and the secondary availability replica that might be needed by queries running under snapshot isolation. Retry the transaction. [SQLSTATE 42000]

    We also see this in the error log but long before the failure happened (and the database referenced here is currently online and available with no issues):

    Msg 913, Severity 16, State 1
    Could not find database ID 126. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

    There was some flapping within the AG on the source that prevented communication with the Gaming database either while or shortly after its AG flipped from secondary to resolving and back to secondary again. This only took a few seconds but it would have affected whichever database was being read at the time (while the AG is in resolving state, the replica is not considered to be primary or secondary).

  3. Preventing it from happening again (⏳ IN PROGRESS)

    Part of the problem is that notification about the failed job simply got lost in the noise of other events that happened over the weekend and took our attention (most notably keeping the network up due to site instability). We're certainly already talking about the following steps:

    1. Make the job failure much more highly visible (e.g. page on-call for awareness).
    2. Add basic retry logic. (✔️ DONE)
    3. Look into why the Colorado secondary went into resolving (not directly related to SEDE at all, but unexpected).
6
  • Will you mark this as status-completed only when all three parts are done? Aug 2 at 8:45
  • 3
    @ShadowTheKidWizard No. Like any outage, this question was about restoring service or data to normal, not about preventing future incidents. The additional information is just transparency; restoring the data is not the only thing we’re going to do as a result.
    – Aaron Bertrand Staff
    Aug 2 at 12:18
  • 3
    I just wanted to mention that I think it's really awesome that you're exposing to us what went wrong and thoroughly analyzing your steps to future-proof this. Thanks so much for the breakdown!
    – Spevacus
    Aug 2 at 17:48
  • @Spevacus yeah, that's very rare, and we should cherish it. Reminds me of Nick replies, when he was in "the mood". ;-) Aug 2 at 18:07
  • 1
    @ShadowTheKidWizard To be fair, when can we ever say "preventing it from happening again" is done? At the end of time? We can take steps toward it but we can't ever be 100% certain we've succeeded.
    – Aaron Bertrand Staff
    Aug 2 at 18:07
  • @AaronBertrand lol, no, when the three sub-steps will be done. :) Aug 2 at 18:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .