Veeam ONE Database size becomming large

[bgalante]

Hi,

Is there a way to trim the size of the database? Our DB is growing and we have to keep adding space to accommodate it.
Any help is appreciated, thank you!

[Vitaliy S.]

How large is your database right now and how many hosts/VMs/datastores you have in the infrastructure?

[bgalante]

The DB is 60GB as of now.

There are 41 Hosts, 936 VM's, and about 72 datastores.

[Vitaliy S.]

Is it all Hyper-v or VMware infrastructure? What is the Veeam ONE version and build number you're running?

[bgalante]

It's all VMWare, and version is - Version: 8.0.0.1669

[Vitaliy S.]

How long have you been running Veeam ONE? The most growth of the database happens during first 2 weeks.

[bgalante]

It's been running for about 6 months, it did grow a lot at the beginning but recently we seem to have had another surge.
I can't say exactly how much, but the DBA said it was significant.

Really just looking for a way to reduce / prune / compress it. I'm hoping that's possible. Or at least to limit it to where it is now.

[Vitaliy S.]

You can limit the size of the database by adjusting the retention policy settings. Here is a related topic for more info > Purging Old Data.

In addition to this, you can switch to advanced scalability mode that will instruct our engine to store less data in the database. Finally, can you please execute the script below and tell us the output?

This script will show what type of data (performance, events etc.) is taking most space in your database:

Code: Select all

create table #tbl ([schema_name] nvarchar(max), table_name nvarchar(max), index_name  nvarchar(max),
used_in_kb  bigint, reserved_in_kb bigint, rows bigint)
go
 
with table_space_usage 
([schema_name], table_name, index_name, used, reserved, ind_rows, tbl_rows)
as (
select s.Name, o.Name, coalesce(i.Name, 'heap'), p.used_page_count*8,
p.reserved_page_count*8, p.[row_count], case when i.index_id in (0,1) then p.[row_count] else 0 end
from sys.dm_db_partition_stats p 
join sys.objects as o on o.object_id = p.object_id
join sys.schemas as s on s.schema_id = o.schema_id
left join sys.indexes as i on o.object_id = p.object_id and i.index_id = p.index_id
where o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
)
insert #tbl
select t.[schema_name], t.table_name, t.index_name, SUM(t.used) as used_in_kb,
SUM(t.reserved) as reserved_in_kb, case GROUPING(t.index_name) when 0 then SUM(t.ind_rows)
else SUM(t.tbl_rows) end as rows
from table_space_usage as t
group by t.[schema_name], t.table_name, t.index_name
with rollup 
order by GROUPING(t.[schema_name]), t.[schema_name],
 GROUPING(t.table_name), t.table_name,
 GROUPING(t.index_name), t.index_name
go
select * from #tbl as t
order by t.used_in_kb desc 


drop table #tbl

[bgalante]

Here is the first few lines from the file:

Code: Select all

NULL	NULL	NULL	9509261528	9514791456	112461430992
monitor	NULL	NULL	9211994328	9215276992	110702948311
monitor	PerfSampleMed	NULL	5349331008	5351438592	66187338392
monitor	PerfSampleLow	NULL	3613348800	3614029696	43129507142
monitor	PerfSampleMed	clst	666639776	666742224	11752891864
monitor	PerfSampleLow	clst	434420256	434483792	7658510614
monitor	PerfSampleMed	cl	350863040	350916960	6185732560
monitor	PerfSampleMed	nc1	346105840	346437960	7113592444
reporter	NULL	NULL	296257216	297097312	1754186143
monitor	PerfSampleLow	nc1	253190256	253260360	4635414319
reporter	ObjectProperty	NULL	235402296	235888376	1557604778

The link you pointed me to, which said run "veeamONEsettings.exe, when I try to run that, I get an error of "invalid license data - reinstall required.

[Vitaliy S.]

Are you using free or paid version of Veeam ONE? Do you try to run it via Veeam Monitor Client or directly from the folder?

[bgalante]

I'm using the paid version, and I'm trying to open it from right inside the folder.

[Vitaliy S.]

While I'm trying to find out the reason for this error message, can you please try to open this tool via Veeam ONE Monitor Client? Navigate to server settings menu to locate it.

[bgalante]

I went in the Veeam ONE monitor. I went to settings, on the "other settings" tab, there is a "Support Utility" which says "Use this utility for advanced Veeam ONE configuration" However when I launch it, it comes back and says "Login failed for user (Me)" Is this where you wanted me to navigate?

[Vitaliy S.]

Yes, that's the place. Are you able to open via any other Veeam ONE admin account? If not, then please let our support team take a look at the issue, so that we could adjust the retention policy for your database.

[bgalante]

I was able to get in via a different user.

I'm looking at the general section, and the "Retention Policy Period" tab Which is currently set at 12 months.

If I change this to 3 months, will that automatically purge the old data or is there an additional step? Specifically to reduce the size of the DB on disk.

[Shestakov]

Not at once. Task to clean database by retention executes once a day in Veeam One Monitor, once a week in Veeam One Reporter.

[risingflight]

Hi Everyone,

I am using SQL 2008 R2, When i look at the db file, the size is 250 GB, please guide me how to reduce the size.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\mydb.MDF

MYDB.MDF file size is 250 GB and i am running out of disk size. experts help me.

In the veeam application it is 6 months logs but i dont think it is 6 months may be for more than 2 years.

[Shestakov]

Hi RisingFlight,
How many hosts, vms, datastores do you have?

In the veeam application it is 6 months logs but i dont think it is 6 months may be for more than 2 years.

Do you mean historical performance, events data?
Thanks!

[Vitaliy S.]

What platform are you at? VMware or Hyper-V?

[bgalante]

Hi,

I'm back with the results of my original issue.

I reduced the retention period in Veeam ONE from 12 months to 3 On Tuesday.
I waited until today (2 days) to check if the daily "clean up" job had reduced the size of the DB. Is has not, it was actually 2 GB larger that when i started this thread last week.
I want to clarify, the DB is on a different host from the Veeam ONE application.

My DBA ran a "shrink" on the DB and was able to reduce it from 62GB to 60. I thought I'd see a greater reduction seeing I reduced the retention period by 75%.

Does something need to be run on the SQL Server hosting the DB?

Is there a log, or something that shows if the cleanup job ran, and the details of what it cleaned up? I wasn't able to locate it.

[Shestakov]

Hi Brian, yes the data retention changes should be tracked in the Veeam ONE log. As was stated before Veeam One Reporter renews retention once a week so I would wait for several more days.
However if you decide to review the logs, you can contact Veeam technical support team for an assistance.
Thanks!

[Vitaliy S.]

To the best of my knowledge most data is consumed by performance data, so I would definitely open a support case to confirm that monitoring data has been cleaned up.

[Reklov]

..."will that automatically purge the old data or is there an additional step? "
well, that would also be interesting for me

Thanks.

[Shestakov]

Hi Reklov,
as was answered previously: "Not at once. Task to clean database by retention executes once a day in Veeam One Monitor, once a week in Veeam One Reporter."