3 Replies Latest reply on Sep 16, 2015 9:48 PM by jaminsql

    Removing old data from DPA database




      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,


        • Re: Removing old data from DPA database

          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

            • Re: Removing old data from DPA database

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


              TableRowsReserved MBData MBIndex MBUnused MB


              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,


                • Re: Removing old data from DPA database



                  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.