What's the Codegolf_Temp database (and why does it mess up my query)?

Question

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?

Answer 1

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.

Answer 2

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 synced from the source 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 (or running) import
and name not like '%_temp' collate sql_latin1_general_cp1_ci_ai