12 Replies Latest reply on Jun 7, 2013 12:14 PM by Leon Adato

    How I can delete data from my database to free up space?

    jrivera

      Hi everyone!

       

      My database has only 5 GB in size, I need to delete old information, to free up space someone could guide me how to do it, I'm not an expert in SQL.

      Thanks

        • Re: How I can delete data from my database to free up space?
          dave_mcmillan

          Have you tried performing a maintenance options like "Shrink" on the database? These are available from the SQL server management studio on the database server for your solarwinds environment. Once you login and expand the databases section simply right click on the database (SolarWindsOrion is the default name) and choose Tasks->Shrink->Database. Then click on "OK" to accept the default actions and let it cook. Repeat for any other databases you have for other SolarWinds products installed. Best part it can be done with no downtime to the environment.

          • Re: How I can delete data from my database to free up space?
            Leon Adato

            Step 1: Get more space. You are going to need A LOT more than 5Gb, no matter what you are doing. For goodness sake, your operating system needs at least 40Gb. Show a little love and give the little bugger at least 100Gb. Disk is sooo cheap these days.

             

            That said, you can:

            1. Go into Settings, Polling Settings, and change the Database Settings so it retains fewer days of data, and summarizes more often.
            2. Wait 24 hours so all that summarization and deletions happen
            3. THEN if you need more space, go into MS SQL Management studio
              1. Right-click the database name (ie: SolarWindsOrion)
              2. Choose Tasks
              3. Choose Shrink
              4. Choose Database
              5. Check the "Reorganize files.." option and click OK
              6. Repeat teps 1-3
              7. Choose Files
              8. Do this for both the transaction logs and the regular files (I'm working from memory, but you can either work it from here or if it makes no sense at this point, you shouldn't be doing this on your own anyway  - go find a DBA to help).

             

            Hope that helps a bit.

            - Leon

            1 of 1 people found this helpful
              • Re: How I can delete data from my database to free up space?
                jrivera

                Hi Leon,

                 

                Thanks for your help, the problem with my limited capacity of the database, is caused by the type of license for my SQL, not for lack of hardware.

                  • Re: How I can delete data from my database to free up space?
                    Aforsythe

                    You really shouldn't use SQL Express for something like this unless you understand the limitations and how to deal with them. I use the express (free) versions all the time for testing, temp dbs, data migration, automating data import/export tasks from one format or another to one format or another, but I never use them to actually store any permanent or even semi-permanent/expirable data.

                     

                    I'm going to agree with dba-one below as well. Shrinking is bad. Planning is good. And if I remember correctly, you can still keep your DB at 5GB as long as it has free space in it so you should definitely purge some data as opposed to shrinking.

                • Re: How I can delete data from my database to free up space?
                  matt.matheus

                  Are you saying it's only 5GB or you only have 5GB space?  I can't imagine a situation where you would only have 5GB available on disk. 

                   

                  If you only have 5GB total space... you really need to free some up.

                   

                  That said, you can do the steps Adatole mentioned to limit how much data SW is collecting each day.  You also could manually purge some of your historical data, but it's not something that I would recommend doing if you don't already know how.

                  • Re: How I can delete data from my database to free up space?
                    dba-one

                    Some tables in SW have foreign keys so simply deleting from the table can be problematic.The statement to purge data from that table is very easy but as a newbie, I can't post it here. Lowering your retention settings is the best way to handle this issue because deleting from a given table can cause big issues. I can give you a hint:

                     

                    Look through the stored procedures in the database as SW uses them to do the purge work. Review the code to get a better understanding of the complete purge process.

                     

                    There are suggestions about shrinking a database and/or related files. I'm a DBA and I despise the fact that SW wants to shrink/grow things like log files. Do not do these things unless you know why you are doing it and HAVE to do it.

                    • Re: How I can delete data from my database to free up space?
                      mr.e

                      We had similar issues, with our database getting too big.  We tackled this in several ways...

                       

                      1. Change Data Retention settings within the SolarWinds/Orion UI. -- For example, reducing the Syslog retention to 14 days has helped a lot.
                      2. Database Shrink -- This was mentioned by a fellow Thwack-er, and this helped but only after we had made changes to our retention settings.
                      3. Truncating some tables (i.e. Wireless tables).  Below is an example of truncate commands I received from SW tech support:
                        1. Truncate WL tables

                          truncate table [dbo].[NPM_NV_WL_APS_DAILY]
                          truncate table [dbo].[NPM_NV_WL_APS_DETAIL]
                          truncate table [dbo].[NPM_NV_WL_APS_HOURLY]
                          truncate table [dbo].[NPM_NV_WL_CLIENTS_DAILY]
                          truncate table [dbo].[NPM_NV_WL_CLIENTS_DETAIL]
                          truncate table [dbo].[NPM_NV_WL_CLIENTS_HOURLY]
                          truncate table [dbo].[NPM_NV_WL_CONTROLLERS_DAILY]
                          truncate table [dbo].[NPM_NV_WL_CONTROLLERS_DETAIL]
                          truncate table [dbo].[NPM_NV_WL_CONTROLLERS_HOURLY]
                          truncate table [dbo].[NPM_NV_WL_INTERFACES_DAILY]
                          truncate table [dbo].[NPM_NV_WL_INTERFACES_DETAIL]
                          truncate table [dbo].[NPM_NV_WL_INTERFACES_HOURLY]

                       

                      Of course, before truncating any tables... Please, (1) verify that you are truncating the correct table -- lest you end up wiping out tables critical to the application.  Also, (2) ensure that you have a good SQL backup of the SolarWinds database.  Anyway,I hope this helps in your aim to trim the size of your database.  Lastly, I would concur with my fellow Thwakers' words of caution when dealing with the SQL database.  If you are a newbie on SQL issues, it is best for you to contact SW tech support.  They will provide you with the guidance you need, and will significantly reduce chances of something going very badly.

                       

                      Best wishes.

                      1 of 1 people found this helpful
                      • Re: How I can delete data from my database to free up space?
                        jrivera

                        Hi everyone!!

                        I Did It! It is enough to modify the number of Polling settings Detailed days that the default is 7 days, it is changed to 2 days and run the maintenance procedure for my database, and finish with the procedure described by Leon (Adatole) and ready. The used space of my database change 4.5 Gb to 2.8 GB of space used, this is my solution until I change my version of SQL and this is unlimited. Thank you all.

                         

                         

                         

                        Polling Settings_07-06-2013 11-38-41 a.m..jpg

                        Details_2_days_07-06-2013 11-38-16 a.m..jpgDetails_7_days_07-06-2013 11-44-22 a.m..jpg

                        • Re: How I can delete data from my database to free up space?
                          Leon Adato

                          Nice work, and even better that you documented with pictures for future generations.