6

Today, I was running some queries on SEDE and found out that this one fails to run because of the following error:

Invalid object name 'StackExchange.Codegolf_Temp.dbo.badges'.

Apparently, there's a third database for the Programming Puzzles & Code Golf site, next to the one for the main site and the meta site (see this query).

Other databases are all related to either a main or a meta site (except for the first four, which are system databases; this is normal on a SQL Server instance). What is this specific database doing there?

And as a corollary: if it really has no badges table, why does it fail to run the query when it runs for Excavator, but not for other badges like Fanatic?

  • 1
    we don't even have access to that db The server principal "STACKEXCHANGE\svc_sede" is not able to access the database "StackExchange.Codegolf_Temp" under the current security context. – rene May 6 '18 at 11:04
  • May have been a botched site launch in the past. – Sonic the Bracketed Hedgehog May 6 '18 at 11:09
  • 1
    @SonictheInclusiveHedgehog meh, this has worked before. Glorfindel is using my multidb query template. I'm pretty sure that database is only here since this morning, after the SEDE refresh – rene May 6 '18 at 11:16
  • I tried to repro that fail for Excavator but not for Fanatic and could only repro once. Now that I've forked the query it fails consistently, as expected. – rene May 6 '18 at 11:18
  • 1
    Looks like the job failed. Let me do some digging. – Taryn May 6 '18 at 12:02
  • I'm going to mark this as a bug because it was the result of the failed job. Things should be resolved now. – Taryn May 6 '18 at 17:26
8

As rene mentioned, it is created as part of the weekly refresh of SEDE. The job failed last night part way through leaving that _Temp database in place.

I've gone ahead and kicked off the refresh again and will monitor to make sure nothing goes wrong, but it will take a few hours for it to completely finish. Once it's done there should be no more _temp versions to cause problems.

Update: 2018-05-03 15:19 UTC the job is done. There were no additional errors and there should no longer be any _temp DBs to cause problems.

  • Today, it's happening again: Invalid object name 'StackOverflow_Temp.dbo.badges'. – Glorfindel Jun 24 '18 at 7:01
  • @Glorfindel I'm going to take a guess that maybe you tried to run a query while the refresh was still executing. I'm not seeing any failures and that _temp DB doesn't exist. – Taryn Jun 24 '18 at 11:54
  • Could be (it was 7am UTC, a few minutes before posting that comment). Now it's working again :) – Glorfindel Jun 24 '18 at 11:57
  • @Glorfindel The job is scheduled to start at 3am UTC and runs for about 5 hours, so probably just some contention. – Taryn Jun 24 '18 at 11:58
  • Probably I'm the only one running cross-site queries that early on Sunday morning (and I don't do it often enough to notice) :P – Glorfindel Jun 24 '18 at 11:59
6

The [name of db]_Temp is an unintended leftover of the import process.

In this answer Nick links to the stored procedure that takes care of the import.

Here are the most relevant parts:

CREATE PROCEDURE [dbo].[sp_Refresh_Database] @DBName nvarchar(100)
AS
BEGIN
    Declare @TempDBName varchar(100) = @DBName + '_Temp';

    // initialization code omitted

    Exec('Create Database [' + @TempDBName + ']
              ON  PRIMARY (NAME = N''' + @DBName + ''', FILENAME = N''' + @DataPath + @BaseFileName + '.mdf'' , SIZE = 4096KB , FILEGROWTH = 102400KB)
              LOG ON (NAME = N''' + @LogFileName + ''', FILENAME = N''' + @LogPath + @BaseFileName + '_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)');

    // import of tables code omitted

    Exec('Alter Database [' + @TempDBName + '] Modify Name = [' + @DBName + ']');
END

For unclear reasons the procedure did start and created the Temp db but it never ran to completion. This hypothesis is further confirmed by the fact that the max(creationdate) of some of the major tables in the [stackexchange.codegolf] database are on April 29th, 2018. That is Sunday a week a go.

It messes up your query because the permissions are not set, so the svc_sede user doesn't have any access to that schema. But the database shouldn't really exist at this point.

An SE developer need to look into the logging to determine why this particular database couldn't be restored from the backup and when they fixed the root cause, the procedure should be re-run for [stackexchange.codegolf].

I'm not sure if you want to workaround this issue as it clearly indicate something is off with the SEDE data but in case you do:

from sys.databases
where database_id > 5
-- ignore _Temp databases that might be a left over of a broken import
and name not like '%_temp' collate sql_latin1_general_cp1_ci_ai 

You must log in to answer this question.

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