28

I was running a query this morning similar to:

create table #validaccounts(accountid int primary key)

declare @sql nvarchar(max)
select @sql = concat(N'insert into #validaccounts
select distinct accountid
from (',
STRING_AGG(concat(N'
select u.accountid
from [', convert(nvarchar(max),name), N'].dbo.users u
inner join users h on h.accountid = u.accountid
where u.reputation > 1' ) ,' union all ')
,' ) data')
from sys.databases
where database_id > 5
and ((name not like '%.Meta' and name not like '%_Temp' ) or name = 'Stackexchange.Meta')

exec(@sql)

but I was met with this error message, consistently:

Database 'StackExchange.Arduino' is already open and can only have one user at a time.

This happened both in my logged in and incognito session, so I assume the error is not tied to my account.

I do notice the restore/refresh was still running as the sys.databases showed the StackExchange.Arduino_Temp which indicates it is the database currently being restored. But it did start that restore at 3:27:21. It is now 7:13. I know Arduino is popular but this is taking way too long.

Enter image description here

Can you investigate why the Arduino db didn't complete and verify why the databases ended up in a state my example query doesn't run any more? I'm pretty sure I've run that query before while a restore is in progress, without any error messages. Strangely enough, running a query for the Arduino database by means of selecting it from the drop down in SEDE and then run a single query does work.

For those users looking for a temporary work-around: Add an extra where clause to exclude StackExchange.Arduino, for example like so:

SELECT name
FROM sys.databases
WHERE database_id > 5
  AND (name = 'StackExchange.Meta' OR name NOT LIKE 'StackExchange.Meta%')
  -- temporary exclude borked database and its Temp counter part
  AND (name not in ('StackExchange.Arduino','StackExchange.Arduino_Temp'))

2
  • 10
    I highly contest the editing of the original title. Y'll are no fun ...
    – rene
    Commented May 8, 2022 at 7:10
  • Not punny enough! ;) Commented May 8, 2022 at 9:54

1 Answer 1

17

What happened?

The weekly refresh process got stuck & failed part-way through on Sunday, and left the StackExchange.Arduino database in a wonky state. (Specifically, it was stuck in Single User Mode.)

During the refresh process, we stage the updated copy of the database, get everything updated & into a consistent state, then "swap & drop" to drop the old copy & put the new copy into play, then move on to the next database. During this "swap & drop" step for the StackExchange.Arduino database, the job failed.

About 1/3 of the databases were updated prior to the failure, and 2/3 are remaining. The full list of databases is below, listed by status.

What did I do?

I've completed the "swap & drop" for the StackExchange.Arduino database, so it is now fully refreshed. I am not going to restart the refresh job, because it's somewhat disruptive to users because of this "swap & drop" step. (That's why we normally run it on Sunday.)

I will do an out-of-band refresh for just the StackOverflow database this evening (that's evening for me, in the US... just before midnight UTC), when usage is low & disruption can be minimized. The remaining databases that were not updated will be updated next Sunday during the normal update time.

Update 5/10 14:00 UTC The StackOverflow database was successfully updated overnight, and is now updated. I've also moved it on the "Status" list below to reflect the current status.

Status

These databases were successfully updated prior to the job failure:

  • StackExchange.3dprinting
  • StackExchange.3dprinting.Meta
  • StackExchange.Ai
  • StackExchange.Ai.Meta
  • StackExchange.Arduino
  • StackExchange.Arduino.Meta
  • StackExchange.Bioinformatics
  • StackExchange.Bioinformatics.Meta
  • StackExchange.Buddhism
  • StackExchange.Buddhism.Meta
  • StackExchange.Cardano
  • StackExchange.Cardano.Meta
  • StackExchange.Civicrm
  • StackExchange.Civicrm.Meta
  • StackExchange.Coffee
  • StackExchange.Coffee.Meta
  • StackExchange.Computergraphics
  • StackExchange.Computergraphics.Meta
  • StackExchange.Conlang
  • StackExchange.Conlang.Meta
  • StackExchange.Craftcms
  • StackExchange.Craftcms.Meta
  • StackExchange.Crafts
  • StackExchange.Crafts.Meta
  • StackExchange.Cseducators
  • StackExchange.Cseducators.Meta
  • StackExchange.Datascience
  • StackExchange.Datascience.Meta
  • StackExchange.Devops
  • StackExchange.Devops.Meta
  • StackExchange.Drones
  • StackExchange.Drones.Meta
  • StackExchange.Earthscience
  • StackExchange.Earthscience.Meta
  • StackExchange.Economics
  • StackExchange.Economics.Meta
  • StackExchange.Elementaryos
  • StackExchange.Elementaryos.Meta
  • StackExchange.Emacs
  • StackExchange.Emacs.Meta
  • StackExchange.Engineering
  • StackExchange.Engineering.Meta
  • StackExchange.Eosio
  • StackExchange.Eosio.Meta
  • StackExchange.Esperanto
  • StackExchange.Esperanto.Meta
  • StackExchange.Ethereum
  • StackExchange.Ethereum.Meta
  • StackExchange.Expatriates
  • StackExchange.Expatriates.Meta
  • StackExchange.Hardwarerecs
  • StackExchange.Hardwarerecs.Meta
  • StackExchange.Health
  • StackExchange.Health.Meta
  • StackExchange.Hinduism
  • StackExchange.Hinduism.Meta
  • StackExchange.Hsm
  • StackExchange.Hsm.Meta
  • StackExchange.Interpersonal
  • StackExchange.Interpersonal.Meta
  • StackExchange.Iot
  • StackExchange.Iot.Meta
  • StackExchange.Iota
  • StackExchange.Iota.Meta
  • StackExchange.Joomla
  • StackExchange.Joomla.Meta
  • StackExchange.Korean
  • StackExchange.Korean.Meta
  • StackExchange.Languagelearning
  • StackExchange.Languagelearning.Meta
  • StackExchange.Latin
  • StackExchange.Latin.Meta
  • StackExchange.Law
  • StackExchange.Law.Meta
  • StackExchange.Lifehacks
  • StackExchange.Lifehacks.Meta
  • StackExchange.Literature
  • StackExchange.Literature.Meta
  • StackExchange.Materials
  • StackExchange.Materials.Meta
  • StackExchange.Matheducators
  • StackExchange.Matheducators.Meta
  • StackExchange.Moderators
  • StackExchange.Moderators.Meta
  • StackExchange.Monero
  • StackExchange.Monero.Meta
  • StackExchange.Musicfans
  • StackExchange.Musicfans.Meta
  • StackExchange.Mythology
  • StackExchange.Mythology.Meta
  • StackExchange.Opensource
  • StackExchange.Opensource.Meta
  • StackExchange.Or
  • StackExchange.Or.Meta
  • StackExchange.Portuguese
  • StackExchange.Portuguese.Meta
  • StackExchange.Proofassistants
  • StackExchange.Proofassistants.Meta
  • StackExchange.Puzzling
  • StackExchange.Puzzling.Meta
  • StackExchange.Quantumcomputing
  • StackExchange.Quantumcomputing.Meta
  • StackExchange.Retrocomputing
  • StackExchange.Retrocomputing.Meta
  • StackExchange.Rus
  • StackExchange.Rus.Meta
  • StackExchange.Sitecore
  • StackExchange.Sitecore.Meta
  • StackExchange.Stellar
  • StackExchange.Stellar.Meta
  • StackExchange.Substrate
  • StackExchange.Substrate.Meta
  • StackExchange.Tezos
  • StackExchange.Tezos.Meta
  • StackExchange.Ukrainian
  • StackExchange.Ukrainian.Meta
  • StackExchange.Vegetarian
  • StackExchange.Vegetarian.Meta
  • StackExchange.Vi
  • StackExchange.Vi.Meta
  • StackExchange.Woodworking
  • StackExchange.Woodworking.Meta
  • StackExchange.Worldbuilding
  • StackExchange.Worldbuilding.Meta
  • StackOverflow.Es
  • StackOverflow.Es.Meta
  • StackOverflow.Ja
  • StackOverflow.Ja.Meta
  • StackOverflow.Meta
  • StackOverflow.Ru
  • StackOverflow.Ru.Meta
  • StackOverflow

The following databases were not updated this past weekend. StackOverflow will be updated tonight. The remaining databases will be updated next weekend during the normal scheduled updates:

  • ServerFault
  • ServerFault.Meta
  • StackApps
  • StackExchange.Academia
  • StackExchange.Academia.Meta
  • StackExchange.Android
  • StackExchange.Android.Meta
  • StackExchange.Anime
  • StackExchange.Anime.Meta
  • StackExchange.Apple
  • StackExchange.Apple.Meta
  • StackExchange.Astronomy
  • StackExchange.Astronomy.Meta
  • StackExchange.Audio
  • StackExchange.Audio.Meta
  • StackExchange.Aviation
  • StackExchange.Aviation.Meta
  • StackExchange.Beer
  • StackExchange.Beer.Meta
  • StackExchange.Bicycles
  • StackExchange.Bicycles.Meta
  • StackExchange.Biology
  • StackExchange.Biology.Meta
  • StackExchange.Bitcoin
  • StackExchange.Bitcoin.Meta
  • StackExchange.Blender
  • StackExchange.Blender.Meta
  • StackExchange.Boardgames
  • StackExchange.Boardgames.Meta
  • StackExchange.Bricks
  • StackExchange.Bricks.Meta
  • StackExchange.Chemistry
  • StackExchange.Chemistry.Meta
  • StackExchange.Chess
  • StackExchange.Chess.Meta
  • StackExchange.Chinese
  • StackExchange.Chinese.Meta
  • StackExchange.Christianity
  • StackExchange.Christianity.Meta
  • StackExchange.Codegolf
  • StackExchange.Codegolf.Meta
  • StackExchange.Codereview
  • StackExchange.Codereview.Meta
  • StackExchange.Cogsci
  • StackExchange.Cogsci.Meta
  • StackExchange.Cooking
  • StackExchange.Cooking.Meta
  • StackExchange.Crypto
  • StackExchange.Crypto.Meta
  • StackExchange.Cs
  • StackExchange.Cs.Meta
  • StackExchange.Cstheory
  • StackExchange.Cstheory.Meta
  • StackExchange.Dba
  • StackExchange.Dba.Meta
  • StackExchange.Diy
  • StackExchange.Diy.Meta
  • StackExchange.Drupal
  • StackExchange.Drupal.Meta
  • StackExchange.Dsp
  • StackExchange.Dsp.Meta
  • StackExchange.Ebooks
  • StackExchange.Ebooks.Meta
  • StackExchange.Electronics
  • StackExchange.Electronics.Meta
  • StackExchange.Ell
  • StackExchange.Ell.Meta
  • StackExchange.English
  • StackExchange.English.Meta
  • StackExchange.Expressionengine
  • StackExchange.Expressionengine.Meta
  • StackExchange.Fitness
  • StackExchange.Fitness.Meta
  • StackExchange.Freelancing
  • StackExchange.Freelancing.Meta
  • StackExchange.French
  • StackExchange.French.Meta
  • StackExchange.GameDev
  • StackExchange.GameDev.Meta
  • StackExchange.Gaming
  • StackExchange.Gaming.Meta
  • StackExchange.Garage
  • StackExchange.Garage.Meta
  • StackExchange.Gardening
  • StackExchange.Gardening.Meta
  • StackExchange.Genealogy
  • StackExchange.Genealogy.Meta
  • StackExchange.German
  • StackExchange.German.Meta
  • StackExchange.Gis
  • StackExchange.Gis.Meta
  • StackExchange.Graphicdesign
  • StackExchange.Graphicdesign.Meta
  • StackExchange.Ham
  • StackExchange.Ham.Meta
  • StackExchange.Hermeneutics
  • StackExchange.Hermeneutics.Meta
  • StackExchange.History
  • StackExchange.History.Meta
  • StackExchange.Homebrew
  • StackExchange.Homebrew.Meta
  • StackExchange.Islam
  • StackExchange.Islam.Meta
  • StackExchange.Italian
  • StackExchange.Italian.Meta
  • StackExchange.Japanese
  • StackExchange.Japanese.Meta
  • StackExchange.Judaism
  • StackExchange.Judaism.Meta
  • StackExchange.Linguistics
  • StackExchange.Linguistics.Meta
  • StackExchange.Magento
  • StackExchange.Magento.Meta
  • StackExchange.Martialarts
  • StackExchange.Martialarts.Meta
  • StackExchange.Math
  • StackExchange.Math.Meta
  • StackExchange.Mathematica
  • StackExchange.Mathematica.Meta
  • StackExchange.Mathoverflow
  • StackExchange.Mathoverflow.Meta
  • StackExchange.Meta
  • StackExchange.Money
  • StackExchange.Money.Meta
  • StackExchange.Movies
  • StackExchange.Movies.Meta
  • StackExchange.Music
  • StackExchange.Music.Meta
  • StackExchange.Networkengineering
  • StackExchange.Networkengineering.Meta
  • StackExchange.Opendata
  • StackExchange.Opendata.Meta
  • StackExchange.Outdoors
  • StackExchange.Outdoors.Meta
  • StackExchange.Parenting
  • StackExchange.Parenting.Meta
  • StackExchange.Patents
  • StackExchange.Patents.Meta
  • StackExchange.Pets
  • StackExchange.Pets.Meta
  • StackExchange.Philosophy
  • StackExchange.Philosophy.Meta
  • StackExchange.Photography
  • StackExchange.Photography.Meta
  • StackExchange.Physics
  • StackExchange.Physics.Meta
  • StackExchange.Pm
  • StackExchange.Pm.Meta
  • StackExchange.Poker
  • StackExchange.Poker.Meta
  • StackExchange.Politics
  • StackExchange.Politics.Meta
  • StackExchange.Programmers
  • StackExchange.Programmers.Meta
  • StackExchange.Quant
  • StackExchange.Quant.Meta
  • StackExchange.Raspberrypi
  • StackExchange.Raspberrypi.Meta
  • StackExchange.Reverseengineering
  • StackExchange.Reverseengineering.Meta
  • StackExchange.Robotics
  • StackExchange.Robotics.Meta
  • StackExchange.Rpg
  • StackExchange.Rpg.Meta
  • StackExchange.Russian
  • StackExchange.Russian.Meta
  • StackExchange.Salesforce
  • StackExchange.Salesforce.Meta
  • StackExchange.Scicomp
  • StackExchange.Scicomp.Meta
  • StackExchange.Scifi
  • StackExchange.Scifi.Meta
  • StackExchange.Security
  • StackExchange.Security.Meta
  • StackExchange.Sharepoint
  • StackExchange.Sharepoint.Meta
  • StackExchange.Skeptics
  • StackExchange.Skeptics.Meta
  • StackExchange.Softwarerecs
  • StackExchange.Softwarerecs.Meta
  • StackExchange.Sound
  • StackExchange.Sound.Meta
  • StackExchange.Space
  • StackExchange.Space.Meta
  • StackExchange.Spanish
  • StackExchange.Spanish.Meta
  • StackExchange.Sports
  • StackExchange.Sports.Meta
  • StackExchange.Sqa
  • StackExchange.Sqa.Meta
  • StackExchange.Stats
  • StackExchange.Stats.Meta
  • StackExchange.Sustainability
  • StackExchange.Sustainability.Meta
  • StackExchange.Tex
  • StackExchange.Tex.Meta
  • StackExchange.Tor
  • StackExchange.Tor.Meta
  • StackExchange.Travel
  • StackExchange.Travel.Meta
  • StackExchange.Tridion
  • StackExchange.Tridion.Meta
  • StackExchange.Ubuntu
  • StackExchange.Ubuntu.Meta
  • StackExchange.Unix
  • StackExchange.Unix.Meta
  • StackExchange.Ux
  • StackExchange.Ux.Meta
  • StackExchange.WebApps
  • StackExchange.WebApps.Meta
  • StackExchange.Webmasters
  • StackExchange.Webmasters.Meta
  • StackExchange.Windowsphone
  • StackExchange.Windowsphone.Meta
  • StackExchange.Wordpress
  • StackExchange.Wordpress.Meta
  • StackExchange.Workplace
  • StackExchange.Workplace.Meta
  • StackExchange.Writers
  • StackExchange.Writers.Meta
  • StackOverflow.Br
  • StackOverflow.Br.Meta
  • SuperUser
  • SuperUser.Meta
11
  • So, status-completed now
    – Luuklag
    Commented May 9, 2022 at 14:21
  • 2
    @Luuklag Not until the next refresh completes successfully I think... the Db being stuck open was a symptom.
    – ColleenV
    Commented May 9, 2022 at 14:31
  • 4
    The immediate/specific issue of the Arduino site's database being inaccessible is fully resolved, but as @ColleenV said, there's a slightly larger issue that will be fully resolved with next week's refresh.
    – AMtwo
    Commented May 9, 2022 at 15:23
  • 1
    Well, I guess Catija thinks we're being too nitpicky about what "completed" means :)
    – ColleenV
    Commented May 9, 2022 at 16:42
  • In former cases the remainder of the refresh was kicked off manually. What has changed that makes it not possible this time? On another note: If I'm still informed correctly the Arduino database should have been dropped immediately after being made single user. What specific condition made that the single user mode succeeded but the drop didn't? That only makes sense to me if there are other statements run there or SQL Server has pulled a trick from its sleeve.
    – rene
    Commented May 9, 2022 at 16:45
  • 3
    @rene It looks like we encountered a race condition where in the fraction of a millisecond between commands, a different process "stole" the single user connection to that database.
    – AMtwo
    Commented May 9, 2022 at 17:45
  • 3
    @rene re: not re-running the sync fully, we weighed "stale data for the week" against the interrupt of the 8+ hour refresh job running mid-day/mid-week, and decided to wait (but to still do the main StackOverflow refresh). I'm the main person who made that decision this time--so the short answer to "what has changed this time?" might be "AMtwo evaluated the risks & action plan, rather than Taryn."
    – AMtwo
    Commented May 9, 2022 at 17:52
  • So Taryn didn't leave her blue feet then? ;) ... Anyway, that is a fair call. I'll keep an eye on things next Sunday and report back if something odd pops-up.
    – rene
    Commented May 9, 2022 at 18:21
  • 2
    Hey now I’m on vacation, don’t drag me into this.
    – Taryn
    Commented May 13, 2022 at 0:47
  • Nothing odds popped-up. The whole restore was complete at 2022-05-15 11:10:14.
    – rene
    Commented May 15, 2022 at 12:30
  • Happens again, assume same root cause is the issue? Commented Sep 19, 2022 at 13:58

You must log in to answer this question.

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