Antonio, not sure that will buy you much as we are already summarizing data, so deleting older summarized data won't get back much storage.
Can you run this script against your repo DB and see what objects are consuming the most space?
-- temporary table to hold results of sp_spacedused for a specific table
IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t ([name] NVARCHAR(128),[rows] CHAR(11),reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),unused VARCHAR(18))
-- get the space used for each table in this database
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM (
SELECT [name] as "Table",
CONVERT(int, [rows]) as "Rows",
CONVERT(int, LEFT([reserved],LEN([reserved])-3)) / 1024 as "Reserved MB",
CONVERT(int, LEFT([data],LEN([data])-3)) / 1024 as "Data MB",
CONVERT(int, LEFT([index_size],LEN([index_size])-3)) / 1024 as "Index MB",
CONVERT(int, LEFT([unused],LEN([unused])-3)) / 1024 as "Unused MB"
FROM #t) t
ORDER BY [Reserved MB] DESC
DROP TABLE #t
Thank you for the information. Below is the query results for TOP 10
Table Rows Reserved MB Data MB Index MB Unused MB CONSPT_2 994219 3945 3921 21 1 CONSPT_3 681330 2698 2684 14 0 CONST_2 473281 1652 1634 18 0 CONST_3 109357 330 325 4 0 CON_IO_DETAIL_3 2091609 267 265 1 0 CON_IO_DETAIL_2 2168820 265 263 1 0 CONSS_2 2499690 179 177 1 0 CON_SQL_SUM_2 626189 101 61 37 2 CONSS_3 1297557 93 92 0 0 CONSW_2 440199 85 84 0 0
If removing data can't help with freeing the space, is it possible if I create a new database on a normal SQL Server Standard Edition? I mean in terms of moving the licence. Also, if yes, how can I deploy the new clean database? I saw that the repo.properties holds the connection string, is there anything else that needs to be changed?
You can deploy a new database rather easy. In repo.properties set the value of repo.created=no save the file and restart the service Ignite PI if you are on a windows install. If Linux the same but, run ./shutdown.sh then ./startup.sh
When DPA has the value set to no it will load the wizard for creating a new repo.
From your table set above it looks like your largest tables are the CONSPT and CONST for two of your instances. This would be the SQL Plan Text and SQL text tables. If you want to clear them out some we can send you a script in a support case.