5

At the moment of posting this, the SEDE homepage says it has been updated one hour ago:

enter image description here

However, when I try to execute a cross-site database query, it runs into an error:

enter image description here

According to Taryn's comment,

The job is scheduled to start at 3am UTC and runs for about 5 hours, so probably just some contention.

So that means I have to wait for an hour or so; no problem, but the 'Data updated' message is wrong right now (). Ideally (), it should show that a refresh is taking place right now.

  • I would like to hear if the SRE team did get their email ... or if this is caused by another not yet known fail case – rene Sep 2 '18 at 8:10
  • @rene There was no failure, the job ran successfully. As far as the 'updated x' time, I'll see what we can do about fixing it. – Taryn Sep 4 '18 at 13:49
  • @Taryn I don't think you can or you have to move the update of that LastPost field for all sites (databases) to the end of the job, no? Then it still is inaccurate as some sites got their refresh already... Maybe I should add that LastPost field to show in an hover for each site in the SEDE front page. Alternatively/additionally we could add an extra appsetting to indicate if the data is being updated. At the start of the job it has to be set to true, at the end to false. – rene Sep 4 '18 at 14:19
  • It is clear that our multi-db scripts need to handle the existence of these _temp db's nicely. – rene Sep 4 '18 at 14:21
  • @Taryn Is the sp_refreshDatabase.sql updated and if it is would it be possible to share that newer version? I find myself trying to guess what is in the black-box and I realize it is easier if I know which scripts run and what they touch in SEDE so I can make a better judgement call on what is going on and on which end the work needs to be done to have the simplest solution that might work. – rene Sep 4 '18 at 14:42
  • @rene It looks like that's the current version being used. – Taryn Sep 4 '18 at 14:52
3
+50

The relative time after Data updated is obtained from the Sites table found in the DataExplorer Database. The Sites table contains for each row meta data for the databases the Data Explorer offers you access to. Each row contains a DateTime field LastPost which gets filled when on the admin controller RefreshStats is called that then goes on to call UpdateStats of the Sites model. There the Max(Creationdate) for the Posts table is executed and the result stored.
I assume /admin/RefreshStats is called after each database got restored / refreshed. At least your screenshot and timing for raising the question suggests that.

Technically speaking the Max(creationdate) of the Posts table is not when the data was updated, but for now that is close enough.

There is no trigger within SEDE that tells it when the restore starts. At best we can rely on an implementation detail of the sp_refresh_database script. At the very start of that script a new database is created with a temporary name that ends with '_Temp'. As one of the last steps in the refresh script the temporary database gets renamed to its actual name.

If we rely on that implementation detail this query will tell you if a refresh procedure is under way:

select top 1 
       case when name like '%_Temp' then 'restoring' else null end [status]
from sys.databases 
order by create_date desc

The benefit of this query would be that it has the potential to deliver us a more accurate Date updated but I first want SE quality assurance verify that we fancy querying SQL Server specific meta data tables (The Information_schema offered by SQL Server don't have creation timestamps). And I assume for now that the database credentials that are used for DataExplorer does have access to sys.databases. Regular users have that permission so I don't expect problems but you never know.

When implemented, expect this between Sunday 03:00 UTC and 08:00 UTC when a refresh is under way:

enter image description here

Under normal operation no text will be shown (as it is now).

Without further ado, here is the pull request.

Allow for 6 to 8 weeks to get this code reviewed, merged and deployed.

You must log in to answer this question.

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