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.
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
1 of 1 people found this helpful
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:
- Go into Settings, Polling Settings, and change the Database Settings so it retains fewer days of data, and summarizes more often.
- Wait 24 hours so all that summarization and deletions happen
- THEN if you need more space, go into MS SQL Management studio
- Right-click the database name (ie: SolarWindsOrion)
- Choose Tasks
- Choose Shrink
- Choose Database
- Check the "Reorganize files.." option and click OK
- Repeat teps 1-3
- Choose Files
- 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.
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.
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.
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.
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.
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.
1 of 1 people found this helpful
We had similar issues, with our database getting too big. We tackled this in several ways...
- Change Data Retention settings within the SolarWinds/Orion UI. -- For example, reducing the Syslog retention to 14 days has helped a lot.
- Database Shrink -- This was mentioned by a fellow Thwack-er, and this helped but only after we had made changes to our retention settings.
- Truncating some tables (i.e. Wireless tables). Below is an example of truncate commands I received from SW tech support:
- 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]
- Truncate WL tables
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.
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.
Nice work, and even better that you documented with pictures for future generations.