Most space in Veeam should be in either one of two different tables. Based on where the space is located will dictate which method is used.
- perfsamplelow – is more than 1 month out history, and can use a script to truncate
- perfsampleMed – Is current history set by Gui. Don’t use script oil this but rather thank gui retention rules.
TO AVOID CONNECTION REFUSALNOTE: YOU NEED TO RUN SQL MANAGER UNDER THE Windows ACCOUNT that runs VeeamOne AS THAT IS THE ACCOUNT THAT RUNS THE SERVICE.
Shrink Veeam One DB Low Table
SQL Manager is installed on VEEAMONE. Use that to run scripts.
CHECKPOINT DECLARE @dt DATETIME SET @dt = CONVERT(DATETIME, '2020-01-25 00:00:00.001' ,120) WHILE EXISTS (SELECT * FROM [monitor].[perfsamplelow] WITH(NOLOCK) WHERE [timestamp] < @dt) BEGIN BEGIN TRAN DELETE TOP (50000) FROM [monitor].[perfsamplelow] WHERE [timestamp] < @dt COMMIT TRAN CHECKPOINT END DBCC shrinkfile (N'VeeamOne', 1)
Analyze table size SQL
select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table], cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb, cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb from sys.tables tab join sys.indexes ind on tab.object_id = ind.object_id join sys.partitions part on ind.object_id = part.object_id and ind.index_id = part.index_id join sys.allocation_units spc on part.partition_id = spc.container_id group by schema_name(tab.schema_id) + '.' + tab.name order by sum(spc.used_pages) desc;
For more information see this post
The retention policy for VEEAM ONE has been changed to hopefully shrink the VEEAM ONE SQL database.
- Run the command C:\Program Files\Common Files\Veeam\Veeam ONE Settings\VeeamOneSettings.exe2. Change the Retention Settings to a lower default setting of 12 and 6 months
- Change Retention Policy Settings. You can go as low as 1 month.
3. We will need to wait to see if the VEEAM ONE SQL database shrinks at all after the changes have been made.
If the database does NOT shrink after about 30 minutes, then you neeed to go into SQL manager, connect to the veeam database as the same windows user that runs veeam, and run the following command
DBCC shrinkfile (N'VeeamOne', 1)
This will take up to 30 minutes. Sometimes 2 minutes sometimes 30 minutes sometimes in between. Depends on size of database, speed of computer that is processing the schrink.