Here is a support article that should help: DPA repository: Data retention policy - SolarWinds Worldwide, LLC. Help and Support . You will save the most space in a DPA repo by tweaking the CLEAN_DAYS_OF_DETAIL parameter.
Do you also have DPA integrated with VMware and capturing metrics as well? If so, this support article should help: Reduce the size of the CONV_METRIC_DETAILS tables in the DPA repository - SolarWinds Worldwide, LLC. Help and Support .
From the end of the support article:
"*You may need to shrink your database datafiles in order to reclaim this space after a successful run of the cleaner process with the settings changed. The default run of this process is 10PM."
DBCC SHRINKFILE(file, size);
Yes, that is an option to actually free up the space, but often not a great one because it can cause fragmentation. The best option is to configure DPA as you want, i.e. alter the CLEAN_DAYS_OF_DETAIL setting above and let it run for a couple days so it purges to where you want. A very clean option to then shrink the space is:
- Shutdown DPA
- Take a full backup and make sure it's a good backup, i.e. restore it somewhere else to check it.
- Drop the current repository database
- Recreate it and restore the backup (use the same database name)
- Restart DPA
This is something you should test thoroughly before committing to this, i.e. make sure this process works for you before trying it as SolarWinds does not support this process.