no data in HN or DN QoS tables for one or a small number of metrics
Article ID: 281868
Updated On:
Products
Issue/Introduction
While trying to create a report using historic data, we find that some QoS metrics which should have data going back a long time appear to have only the raw data (e.g. last 30 days) available.
Investigating further we can see the raw data in the RN_QOS_DATA_#### table, but the associated HN_ and DN_ tables have 0 rows.
This does not appear to impact all QoS's but a very small number.
Environment
MSSQL Backend - UIM 20.x or 23.x
Cause
partition problem with the tables
Resolution
First, run the following SQL Query to check how many partitions the HN and DN tables in question have, substituting the appropriate table name/numbers for the data in question:
SELECT
obj.name AS TableName,
COUNT(DISTINCT p.partition_number) AS NumberOfPartitions
FROM sys.partitions p
JOIN sys.objects obj ON p.object_id = obj.object_id
WHERE p.object_id IN (OBJECT_ID('HN_QOS_DATA_0001'), OBJECT_ID('DN_QOS_DATA_0001'))
GROUP BY obj.name;
The exact values may vary depending on the specific values you have chosen for the historic/daily QoS retention:
The important thing is that neither one of them should return a value of "1" indicating only a single partition.
If the value returned is 1, check the following query and make sure it returns a value of "partition":
select state from tbn_de_DataMaintConfig;
If this query returns "basic" this means that your database is not partitioned and this KB article does not apply. Only proceed further if "partition" is returned. See the "Additional Information" section for details.
If the query above returns "partition" and the number of partitions showing for the HN and/or DN tables is "1" then you should proceed as follows:
1. deactivate the data_engine probe
2. Issue the following query which will temporarily put the data_engine maintenance process into un-partition mode;
update tbn_de_DataMaintConfig set state = 'basic';
3. Now run the following query which will perform the maintenance. The below query has a '99' : this represents the qos_def_id of the problematic table, e.g. HN_QOS_DATA_0099. Change this value as appropriate - so if you are having problems with HN_QOS_DATA_0149, change the 99 below to 149. If you are having problems with RN_QOS_DATA_0001, change the 99 to 1. Do not use leading zeroes. This query will unpartition the tables temporarily:
declare @RCR int, @HCR int, @RSR int, @HSR int, @RC int
exec @RC = spn_de_DataMaint 99, 'shrink-hn' , null, 5, @RCR output, @HCR output, @RSR output, @HSR output
select @RCR as RCR, @HCR as HCR, @RSR as RSR, @HSR as HSR, @RC as RC;
4. Now run the following query to re-enable partitioning mode:
update tbn_de_DataMaintConfig set state = 'partition';
5. Now run the same query from step 3 again which will re-partition the HN and DN tables.
declare @RCR int, @HCR int, @RSR int, @HSR int, @RC int
exec @RC = spn_de_DataMaint 99, 'shrink-hn' , null, 5, @RCR output, @HCR output, @RSR output, @HSR output
select @RCR as RCR, @HCR as HCR, @RSR as RSR, @HSR as HSR, @RC as RC;
6. activate data_engine
After this, you can run the first query to count the partitions and you should see that the HN/DN tables no longer return "1" for the number of partitions. After this, data maintenance should work as expected going forward. (Unfortunately, there is no way to recover the old data that was not being properly maintained; it is permanently lost.)
Additional Information
If while running the query to check the status of partitioning, the result was "basic" instead of "partition": This indicates that your database is not set in partitioning mode and so the return value of "1" for the number of partitions is actually expected. If that is the case, then the cause of maintenance failing must be something else not related to the partitions.
Check the following KB's:
How does data_engine maintenance and retention work?
Verifying data_engine is deleting old raw and historic data properly
Feedback
