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

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.

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

Hi Dave,

I've done this, however the size of use of my database was not significantly reduced. I can see that the table "CustomPollerStatistics_Detail" his Data size is quite high, you know how I could delete the old data in this table. thanks

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.

Matt Hi!

Yes Matt, my total space is 5 GB, you could guide me as manually purge some data? Thank you.

I honestly don't feel like it would be a good idea for me to do that on a forum like Thwack, too much chance to lose data.  I would open up a support case with SW and have them take a look for you.

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.

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.

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.

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.