Veeam ONE: SQL Express Maximum Database Size Limitation

KB ID: 2357
Product: Veeam ONE
Version: All
Published: 2017-10-12
Last Modified: 2022-03-15
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

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.

 

Cause

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.

 

Solution

SQL Server upgrade or database migration (permanent solution)

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

Purging old data (temporary workaround)

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:

  1. Before you begin:
    • Make sure to properly backup Veeam ONE database.
    • Be aware that data that is purged is purged from all Veeam ONE components. For example, in Veeam ONE Reporter you will not be able to build reports that rely on the data that you purge with the script presented below.
    • Keep in mind that all graphs in Veeam ONE Monitor that rely on the deleted data that 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 workaround does not help to reduce the database size, please contact Veeam Support for further assistance.
  2. Stop Veeam ONE Monitoring Service and Veeam ONE Reporting Service on the Veeam ONE server.
    Change the startup type to "Disabled" mode to prevent autostart.
  3. Execute the following query against the database:
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.

 

  1. Shrink the database to reduce its size.
    Note that SQL script above only purges records, it does not reduce the database file size-on-disk.
  2. The script provided above purges records of the oldest month stored in the database. If you need to reduce the database further or clean more records (2, 3, or more months), you will need to repeat steps 3 & 4 several times.
  3. Change the Startup Type of the Veeam ONE Monitoring Service and Veeam ONE Reporting Service back to Automatic, and start them.

 

NOTE: If you decide to continue using SQL Server Express Edition, it is recommended that you adjust the Retention policy settings to store less data.

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.
Click here to send feedback regarding this KB, or suggest content for a new KB.
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.