#1 Global Leader in Data Protection & Ransomware Recovery

Veeam ONE: SQL Express Maximum Database Size Limitation

KB ID: 2357
Product: Veeam ONE
Version: All
Published: 2017-10-12
Last Modified: 2024-02-01
mailbox
Get weekly article updates
By subscribing, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Notice.

Cheers for trusting us with the spot in your mailbox!

Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest

error icon

Oops! Something went wrong.

Please, try again later.

Challenge

When the Veeam ONE database is located in a SQL Express instance, if the database reaches the maximum allowed size, Veeam ONE will not be able to continue data collection, thus affecting data accuracy and alarm generation.

 

Cause

If you choose to host the Veeam ONE database on Microsoft SQL Server Express Edition, be informed that this edition has a 10 GB database size limitation. For details, see Editions and Supported Features for SQL Server.

 

Solution

Permanent Solution: Upgrade SQL Server or Migrate Database

The best way to resolve this database size limit is to upgrade the SQL Instance from Express edition to Standard or Enterprise edition.

Alternatively, move the Veeam ONE database to another SQL Instance that uses Standard or Enterprise edition. The procedure for configuring Veeam ONE to use a new SQL Server connection is described in KB1599.

Temporary Workaround: Reduce Data Retained in Veeam ONE Database

If upgrading to a licensed version of SQL is not feasible, the alternative solution is to reduce the amount of retained performance data history, reducing the database size below the maximum limit imposed by SQL Express.


Below are two methods to change the Performance Data Retention:

Method 1: Adjust and Enforce Retention using Veeam ONE Setting Utility

You can delete past performance data by lowering the performance data retention within the Veeam ONE Settings Utility by following the method documented in KB2051.

Note: When retention is enforced, performance data within the database is removed, however, the actual database file on disk will not become smaller. If you want the Veeam ONE database file to become smaller on disk, you must perform a Shrink Database operation against the Veeam ONE database after reducing performance data retention. **The SQL Script in Method 2 performs a shrink operation.

Method 2: Adjust and Enforce Retention using a SQL Script

If the Veeam ONE Setting Utility is not available or cannot be used for other reasons, the retention value can be set and enforced using the following SQL Script. Follow these steps to reduce the database size:

  1. Before you begin:
    • Make sure to properly backup Veeam ONE database.
    • Be aware that data that is removed because it is outside of retention is unavailable to all Veeam ONE components. For example, in Veeam ONE Reporter, you will not be able to build reports that rely on the data that was removed due to retention enforcement.
    • Graphs in Veeam ONE Monitor that would use the data that is removed due to retention will be unavailable.
    • Unless specifically instructed by Veeam Support, do not modify the SQL statement and do not execute it against other database tables. If this script does not help to reduce the database size, please contact Veeam Support for further assistance.
  2. Stop the Veeam ONE Monitoring Service and Veeam ONE Reporting Service on the Veeam ONE server.
    Change their startup type to "Disabled" to prevent autostarting.
  3. Copy the following SQL Script and modify the 'RetentionPolicy_New' value:
    • Retention value is measured in months, and the default is 12.
    • The value specified must be a whole number between 1 and 36.
--Specify the new Retention Policy (how many months to keep)
DECLARE @RetentionPolicy_New int = 12
--
--StrictCleanup value of 0 is recommended for most environments. Set to 1 for a more aggressive cleanup that will take longer.
DECLARE @StrictCleanup int = 0
--
--Do not modify anything below this line!--
IF (@RetentionPolicy_New not between 1 and 36) BEGIN RAISERROR ('ERROR: The Retention Policy value must be within range of 1-36 months',15,15); RETURN; END
DECLARE @RetentionPolicy int = (select top 1 coalesce(RetentionPolicyPeriod,12) from common.ServerSettings)
IF @RetentionPolicy > @RetentionPolicy_New SET @RetentionPolicy = @RetentionPolicy_New
IF @RetentionPolicy < 1 SET @RetentionPolicy = 1
DECLARE @RetentionDate datetime = DATEADD(MONTH, -@RetentionPolicy, GETUTCDATE())
PRINT CONCAT('Current Retention Policy: ',@RetentionPolicy,' months. New Retention Policy: ',@RetentionPolicy_New,' months. 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
EXEC monitor.sp_perf_sample_delete 3, @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%') or (name LIKE 'PerfSampleDay%')
OPEN @cur
DECLARE @partitionName NVARCHAR(32)
FETCH NEXT FROM @cur INTO @partitionName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX)

--Truncate the section
SET @sql = N'IF @RetentionDate >= (SELECT max([timestamp]) from ' + @partitionName + ') TRUNCATE TABLE ' + @partitionName
EXEC sp_executesql @sql, N'@RetentionDate DATETIME', @RetentionDate

--Delete the data from the remaining section (to fully meet the Retention Policy) + shrink the log
IF (@StrictCleanup = 1) BEGIN
SET @sql = N'DELETE from ' + @partitionName + ' where [timestamp] < @RetentionDate'
EXEC sp_executesql @sql, N'@RetentionDate DATETIME', @RetentionDate
IF (SELECT recovery_model FROM sys.databases where database_id = DB_ID()) = 3
DBCC SHRINKDATABASE (0,TRUNCATEONLY) WITH NO_INFOMSGS
END

FETCH NEXT FROM @cur INTO @partitionName
END
CLOSE @cur
END
UPDATE common.ServerSettings SET RetentionPolicyPeriod = @RetentionPolicy_New
DBCC SHRINKDATABASE (0) WITH NO_INFOMSGS
GO
  1. Execute the SQL script against the VeeamONE database.
    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.
  2. The script sets the new Retention Policy value, and purges records older than the specified retention value limit. If you need to reduce the database further or clean more records, set the retention value lower and repeat steps 3 & 4 as needed.

    Optional: To enable advanced filtering, set the “StrictCleanup” parameter to 1. Doing so will cause the purge process to take significantly longer but achieves the smallest possible database size after cleanup.
  3. Change the Startup Type of the Veeam ONE Monitoring Service to 'Automatic' and the Veeam ONE Reporting Service to 'Automatic (Delayed Start)', then start them.

Reducing future database growth rate

Before applying the approaches described below, the database must be in an operational state, either by reducing its size using the methods described above in this article or by migrating the database to an edition of Microsoft SQL that does not have the 10GB size limit.

Changing the Data Collection Mode

The Data Collection Mode determines the frequency of data collection. The Data Collection Mode is configured during initial deployment and can be modified after deployment using the Veeam ONE Settings Utility > Scalability. Within the Scalability options, it is also possible to limit which Performance Metrics are collected.

Modifying the Retention policy

You can modify the historical data retention period by adjusting the settings in Veeam ONE Settings utilityRetention Policy Period section.

More Information

If the provided procedure does not help, please contact Veeam Support.
To submit feedback regarding this article, please click this link: Send Article Feedback
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.

Spelling error in text

This site is protected by hCaptcha and its Privacy Policy and Terms of Service apply except as noted in our Privacy Policy.
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

Oops! Something went wrong.

Please, try again later.

You have selected too large block!

Please try select less.

KB Feedback/Suggestion

This form is only for KB Feedback/Suggestions, if you need help with the software open a support case

By submitting, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Notice.
This site is protected by hCaptcha and its Privacy Policy and Terms of Service apply except as noted in our Privacy Policy.
Verify your email to continue your product download
We've sent a verification code to:
  • Incorrect verification code. Please try again.
An email with a verification code was just sent to
Didn't receive the code? Click to resend in sec
Didn't receive the code? Click to resend
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

error icon

Oops! Something went wrong.

Please, try again later.