Question about the KB2357

[dcampregher]

Hello guys!

In the KB2357 about the "SQL Express Maximum Database Size Limitation" there is a SQL script to force the Veeam One to purge the oldest data. After execute this script configured to keep only the last month, the database size decreased just 1,5GB. Investigating the SQL i see that the script purge only data from one table and there is other table much more big that the specified in the script:

HECKPOINT
DECLARE @dt DATETIME
SET @dt = CONVERT(DATETIME, '2019-12-08 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)?

Output from the top>down table size:

monitor.PerfSampleMed 5338.88 5342.28
monitor.PerfSampleLow 1307.03 1361.28
monitor.Event 252.27 276.72
reporter.ObjectProperty 175.79 249.88
reporter.rs_ReportDefinition 136.29 136.33
reporter.LatestObjectProperty 70.44 70.90
monitor.PerfSampleDay 67.98 73.09
monitor.EventTag 65.65 114.53
monitor.BpSessionTaskEvent 52.95 53.09
reporter.Object 34.47 50.30.

Note that there is a table named "monitor.PerfSampleMed" with 5.3GB and isn't touched by the script. So the question is there a way to purge these table to free space in the database file.

[Shestakov]

Hello Diogo,
monitor.PerfSampleMed table contains most current performance data which is not older than 1 month thus expected to be untouched by the script. You can configure data retention in UI and even choose performance metrics you want and don't want to collect to free up some space in database.
Thanks

[dcampregher]

Hello!

The table monitor.PerfSampleMed at this time have 7646.80 MB. In one week this table increase 2.3GB, what is the reason ? The retention was configured for 01 month in th last wednesday.

What can i do for release the space consumed by this table ?

[Shestakov]

Retention doesn't touch that table as it contains the most current information, not old historical.
Veeam ONE keeps 20sec performance metrics, then aggregates them into 5min metrics and then to 2 hours averages.
The way to shrink it is to change the aggregation rules. Please contact Veeam support team for the assistance.
Thanks