Finding historic data files by largest size

[sbeadle]

Today I was asked where the largest historic files are. I came up with this query which finds and names the top 10 largest:

select top (10) p.fullname, f.RecordCount, f.StartTime, 
'History\Historic\HdI' || formatvalue( p.id/256 using '00000') || '\' || formatvalue( p.id using '00000') || '\WK' || formatvalue( f.granule using '000000') || '.HRD' as filename
from cdbpoint p inner join cdbhistoricfile f on (p.id = f.objectid)
order by f.RecordCount desc

Results below.

Reading the historic file table is much more efficient than ploughing through individual historic records. Take care though if your system has an 'Index After' setting because the server will need to enumerate these older files, taking time, cache and CPU to do it.

You can build similar queries for Events, Alarm Summary and Configuration Change records. You could add your queries in reply to this post!

Steve

Row | FullName | RecordCount | StartTime | FILENAME 
==========================================================================================================================================================
0 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 10.A 10.Analog | 519030 | 24/04/2023 00:00:00.000 | History\Historic\HdI01639\419777\WK022035.HRD
1 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 07.A 09.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01645\421211\WK022035.HRD
2 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 08.A 01.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01645\421213\WK022035.HRD
3 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 07.A 07.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01645\421214\WK022035.HRD
4 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 07.A 10.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01645\421212\WK022035.HRD
5 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 08.A 04.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01645\421190\WK022035.HRD
6 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 08.A 08.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01639\419778\WK022035.HRD
7 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 09.A 01.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01639\419775\WK022035.HRD
8 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 08.A 05.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01639\419788\WK022035.HRD
9 | Demo Items.Test Bulk Data.d.1000 1.T 10.T 08.A 06.Analog | 519028 | 24/04/2023 00:00:00.000 | History\Historic\HdI01639\419774\WK022035.HRD

[AdamWoodlandToo]

https://community.se.com/t5/Geo-SCADA-Knowledge-Base/Finding-large-historic-granules/ba-p/278729 might need an update, especially regarding the index after info

[sbeadle · Accepted solution]

Thanks Adam. Page has been updated.