Removing old data from DPA database

Question

Hi,

We are currently having the DPA db running on a SQL Express, it reached the 10GB limit and DPA can't perform its checks now.

Is there a way to remove old data up to a date? Lets say  until January 2015, older data than 01.01.2015 can it be removed?

Thank you,

Antonio

jaminsql · Answer

Antonio,

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.

antonio.nula · Answer

Thank you for the information. Below is the query results for TOP 10

TableRowsReserved MBData MBIndex MBUnused MBCONSPT_299421939453921211CONSPT_368133026982684140CONST_247328116521634180CONST_310935733032540CON_IO_DETAIL_3209160926726510CON_IO_DETAIL_2216882026526310CONSS_2249969017917710CON_SQL_SUM_262618910161372CONSS_31297557939200CONSW_2440199858400

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?

Thank you,

Antonio

mandevil · Answer

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