SQL fails due to Invalid object name 'pass_instance_stat_fact_1m_YYYYMMDDhhmm' (4374609)

Failed to execute SQL:
SELECT TOP 1 SUM(cpu_time) AS sum_cpu_time, SUM(wait_remote_provider) AS sum_wait_remote_provider, SUM(wait_memory) AS sum_wait_memory, SUM(wait_latch) AS sum_wait_latch, SUM(wait_other) AS sum_wait_other, SUM(wait_network) AS sum_wait_network, SUM(num_executions) AS sum_num_executions, SUM(wait_for_cpu) AS sum_wait_for_cpu, SUM(wait_xtp) AS sum_wait_xtp, SUM(active_time) AS sum_active_time, SUM(elapsed_time) AS sum_elapsed_time, SUM(wait_io) AS sum_wait_io, SUM(wait_lock) AS sum_wait_lock, SUM(wait_clr) AS sum_wait_clr, SUM(wait_log) AS sum_wait_log, MIN(sample_starttime) AS min_sample_starttime, MAX(sample_endtime) AS max_sample_endtime FROM pass_instance_stat_fact_1m_YYYYMMDDhhmm WHERE sample_endtime >= ? AND sample_endtime <= ? AND instance_key = ?
Param: [YYYY-MM-DD hh:mm:ss.uuu-0800, YYYY-MM-DD hh:mm:ss.uuu-0800, 327]
Connection Details: MSSQLProfile{host='serverhostname', instance= 'DEFAULT_INSTANCE', username='USERNAME', authType= 'WINDOWS_CUSTOM', port= '1433' useNTLMv2= 'false', socketTimeout= '0', database= 'REPOSITORY_DBNAME', secureConnection= 'OFF', packetSize= '0', ApplicationIntent= 'NONE', enforceSSLVersion= 'NONE', lockTimeout= '10000', codePageOverride = '' }
Cause: Invalid object name 'pass_instance_stat_fact_1m_YYYYMMDDhhmm'.
Error Code: 208 SQL State: S0002

The 1m tables are usually created every six hours UTC... 12am, 6am, 12pm, 6pm. The oldest table is then rolled up and deleted. If there was anything happening at those times preventing the DBSS-SPI repository agent from connecting to the repository database, such as a patch or reboot, then it's possible that the 1m table could not be created during the scheduled time.

By design SQL PI should begin recreating the 1m if it is detected they are missing, however this has found sometimes is not the case. 

WORKAROUND

Recreate the missing 1m tables by removing and adding the PI extension back to the agents

1. Disable the PI extension the agents connecting to that PI repository
2. Then deleting the DBSS-SPI agents from FglAMs connecting to the PI repository database
3. Add the PI extension back to one agent reconnecting using the old PI database name. This will recreate or add any of the necessary the next four 1m tables.
4. Once PI has been added the user should be able to find at least one DBSS-SPI from each used FglAM in the Agent Status panel and PI history should be available in the old agent again. After a few moments, new PI SQL data should also be collected and visible for the agent.
5. Then add the PI extension back to other database agents.

STATUS

Enhancement ID FOGGENERAL-I-305 has been logged to recreate the 1m tables when the PI repository is broken. R&D is presently investigating why this is not consistent. Please notify R&D when the 1m tables are unavailable in the logs for more than a few minutes for a root cause analysis.