Weekly SEDE refresh stuck at Gaming.SE

Question

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

Note: a similar thing happened before.

Answer 1

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