7 Replies Latest reply on Jan 7, 2015 2:45 AM by wajeeh_r

    shrink database web reporter

    smalldog

      Hi All, my web reporter is full. file scr_fct_exact_access very large size. Could you help me to reduce database? How to download and use my sql client? My web reporter use internal database. Thanks so much!

        • 1. Re: shrink database web reporter
          sroering

          I don't know which DB you're using. Since you don't have a SQL Client, I'm assuming the internal DB?

           

          Also, by shrinking I don't know if you are also asking how to remove old data?  Or if you have already removed data, but the data file is still large and you want to shrink it to reclaim the disk space to your OS.

          • 2. Re: shrink database web reporter
            smalldog

            Yes Sroering, i using internal database, also deleted old logs from database but size in disk still very large. So i want to shrink it  to reclaim the disk space like you say. Have you got document guide for this? how to delete and shrink database? Thanks!

            • 3. Re: shrink database web reporter
              sroering

              Deleting records makes room available in the data file, but does not automatically release the free space back to the file system.  Most databases (Oracle, MS SQL Server, MySql, etc.) behave like this.

               

              The internal DB is MySQL and the command to shrink a file is "OPTIMIZE TABLE <table_name>". The tables to shrink are "scr_fct_web" and "scr_fct_exact_access".

              You'll need to download a SQL Client to connect to the DB to run the command. I recommend HeidiSQL, which is easy to use, free, and has a portable version that does not require install. The SQL client needs to be put on the Web Reporter server since the internal DB only listenes on localhost, port 9121.  The DB name is "reporting" and you can login with dba:dba. Make sure that Web Reporter service is stopped or the DB is manually taken off-line while you shrink the tables.

               

              Once you are connected to the reporting database, you need to make sure that you have enough free disk space for the shrink.  The shrink will copy the data to a new data file, then remove the original file.  So this means you need enough disk space to hold the remaining data (and indexes). From Heidi SQL, select the reporting database from the left column, Then look at the size column on the Database tab.  I've reverse sorted the table names to keep the screenshot small, the scr_fct_* tables will likely be at the bottom for you.  The size column says how many bytes of data are in the table.

               

              heidi.bmp

               

              For the scr_fct_web, you'll need the data size plus 120% for the index.  Do this table first, since it's much smaller than scr_fct_exact_access. For scr_fct_exact_access, you'll need the data size plus 20% for the index.  If you don't have enough space, the optimize will fail, and possibly orphan the temporary files used for the optimize. So make sure you have enough free disk space before you begin. This may mean you need to remove more data from the DB before you begin.

               

               

              run these queries from the query tab.

              OPTIMIZE TABLE scr_fct_web;

              OPTIMIZE TABLE scr_fct_exact_access;

               

              Let me know if you have questions.

              • 4. Re: shrink database web reporter
                smalldog

                Thanks sroering, that very helpfull. I have errors when log on to DB internal with SQL client (see attach). Do i have stop service reporter or internal database before login?

                • 5. Re: shrink database web reporter
                  sroering

                  Sorry, I gave you the wrong port. 9121 is for the log sources.  9129 is for the database.   Also, don't forget username "dba" password "dba"

                   

                  Seems I cannot edit the post above to fix the mistake. 

                   

                  Message was edited by: sroering on 8/2/11 8:07:53 AM CDT
                  1 of 1 people found this helpful
                  • 6. Re: shrink database web reporter
                    smalldog

                    Thanks Sroering, i can connected.

                    • 7. Re: shrink database web reporter
                      wajeeh_r

                      Hello Sroering,

                       

                      I was having my 'reporting' DB of size 18GB. I followed steps outlined in [EOL] Web Reporter: Database Maintenance and Space Usagethen I also followed your response here to claim white space by using optimize client. I also used HeidiSQL portable version. Before the operation on my system drive C:\ I was having free space of 3.5GB but since your post says for copy operation to a new file extra free space is required equal to size of data, my table 'scr_fct_exact_access' was 18.2gb in size before optimize operation as you can see in attached photo.

                      DB_Size.jpg

                       

                      So, My reporter is a virtual machine, in first step I increased the space of my C:\ drive to 60GB from previous 35GB (from which only 3.5GB was free) to accomplish copy operation to new file. After increasing the space 28GB was free on C:\ drive, it took 43 minutes to execute the optimize operation as you can see in attached photo time taken for operation, after the operations again my free space is 28GB on C:\

                      Optimization_Done.jpg

                      After this I again checked the size of DB by connecting again to 'reporting' and got the below screen shot, it is now 17.8GB, so only this small amount is reduced during shrink operation ? I want to verify if I completed the operation in correct way? I was expecting this to be reduced more, in other words how this helps if the shrinking is that little? Can you please help to understand this?

                      ChangeOf_Size.jpg