Veeam ONE: SQL Express Maximum Database Size Limitation

KB ID: 2357
Product: Veeam ONE
Version: All
Published: 2017-10-12
Last Modified: 2022-09-23
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 autostart.
  3. Copy the following SQL Script and modify the 'RetentionPolicy_New' value:
    • Retention value is measured in months, and the default is 12.
    • Value specified must 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 and Veeam ONE Reporting Service back to Automatic, and start them.

Reducing future database growth rate

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 utilityRetention Policy Period section.

More information

If the provided procedure does not help, please contact Veeam Support.
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 reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

Oops! Something went wrong.

Please try again later.

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 reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
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.