KB ID: | 2357 |
Product: | Veeam ONE |
Version: | All |
Published: | 2017-10-12 |
Last Modified: | 2022-03-15 |
Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest
Once Veeam ONE database reaches the maximum allowed size, Veeam ONE will not be able to continue data collection thus affecting data accuracy and alarms generation.
If you choose to host the Veeam ONE database on Microsoft SQL Server Express, be informed there is a 10 GB database size limitation for this edition. For details, see Editions and Supported Features for SQL Server.
The best way to resolve the issue would be to upgrade your SQL Server to a Standard or Enterprise edition or move Veeam ONE database to another SQL Server with required edition. The procedure for configuring Veeam ONE to use a new SQL Server connection is described in the following KB article: http://www.veeam.com/kb1599
You can also address the issue by purging old performance data as described below.
You can delete past performance data via a custom SQL script that should be run against Veeam ONE database. Follow these steps to reduce the database size:
DECLARE @RetentionDate datetime
SELECT @RetentionDate=DATEADD(MONTH, 1, MIN([timestamp])) FROM monitor.PerfSampleLow
PRINT 'Keeping data newer than ' + CAST(@RetentionDate AS NVARCHAR)
IF OBJECT_ID (N'[monitor].[PerfSampleLow]', N'U') IS NOT NULL
BEGIN
PRINT 'Clearing unpartitioned data'
EXEC monitor.sp_perf_sample_delete 2, @RetentionDate
END
ELSE
BEGIN
PRINT 'Clearing partitioned data'
DECLARE @cur CURSOR
SET @cur = CURSOR LOCAL FOR SELECT 'monitor.' + [name] FROM sys.tables WHERE name LIKE 'PerfSampleLow%'
OPEN @cur
DECLARE @partitionName NVARCHAR(32)
FETCH NEXT FROM @cur INTO @partitionName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'IF @RetentionDate >= (SELECT max([timestamp]) from ' + @partitionName + ') TRUNCATE TABLE ' + @partitionName
EXEC sp_executeSQl @sql, N'@RetentionDate DATETIME', @RetentionDate
FETCH NEXT FROM @cur INTO @partitionName
END
CLOSE @cur
END
NOTE: this operation can cause a significant workload on the database and growth of the database transaction log. Make sure you do not have mission-critical databases on this server.
Before applying any of the approaches described below, you will need to migrate the DB to a standard or enterprise installation of MS SQL Server or purge the old data.
Changing the scalability mode from Typical to Advanced
In the Advanced mode, the data collections are less frequent and include fewer performance metrics, which helps to slow down the database growth. You can learn more about the Advanced scalability mode from our deployment guide.
Modifying the Retention policy
You can modify the historical data retention period by adjusting the settings in Veeam ONE Settings utility > Retention Policy Period section.
Your feedback has been received and will be reviewed.
Please try again later.
This form is only for KB Feedback/Suggestions, if you need help with the software open a support case
Your feedback has been received and will be reviewed.