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

    Removing old data from DPA database

    antonio.nula

      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

        • Re: Removing old data from DPA database
          mandevil

          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
              antonio.nula

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

               

              TableRowsReserved MBData MBIndex MBUnused MB
              CONSPT_299421939453921211
              CONSPT_368133026982684140
              CONST_247328116521634180
              CONST_310935733032540
              CON_IO_DETAIL_3209160926726510
              CON_IO_DETAIL_2216882026526310
              CONSS_2249969017917710
              CON_SQL_SUM_262618910161372
              CONSS_31297557939200
              CONSW_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

                • Re: Removing old data from DPA database
                  jaminsql

                  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.