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
  • 9
    I highly contest the editing of the original title. Y'll are no fun ...
    – rene
    May 8 at 7:10
  • Not punny enough! ;) May 8 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
10
  • So, status-completed now
    – Luuklag
    May 9 at 14:21
  • 2
    @Luuklag Not until the next refresh completes successfully I think... the Db being stuck open was a symptom.
    – ColleenV
    May 9 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 Staff
    May 9 at 15:23
  • 1
    Well, I guess Catija thinks we're being too nitpicky about what "completed" means :)
    – ColleenV
    May 9 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
    May 9 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 Staff
    May 9 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 Staff
    May 9 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
    May 9 at 18:21
  • 2
    Hey now I’m on vacation, don’t drag me into this.
    – Taryn
    May 13 at 0:47
  • Nothing odds popped-up. The whole restore was complete at 2022-05-15 11:10:14.
    – rene
    May 15 at 12:30

You must log in to answer this question.

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