Top is not allowed in an UPDATE or DELETE statement against a partitioned view

[dspjones]

Veeam Support Case 04560681

The KB article https://www.veeam.com/kb2357 shows a SQL query to purge old data from the VeeamOne database.
Apparently, that query no longer works for the latest versions of VeeamOne. Any chance of an updated query? Current query gives the following error: Top is not allowed in an UPDATE or DELETE statement against a partitioned view. I don't want to move the database to a full blown SQL instance. Yes, I know how to change the retention of VeeamOne - I still want to purge the data.

[Vitaliy S.]

Hi,

Try executing the following query to delete monthly data and let me know if that helps! If it does, I will ask our support team to update the KB.

Don't forget to back up your DB first (just in case).

Code: Select all

TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow12]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow11]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow10]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow9]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow8]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow7]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow6]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow5]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow4]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow3]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow1]
TRUNCATE TABLE [%DB_NAME%].[monitor].[PerfSampleLow0]
DBCC shrinkfile (N'VeeamOne', 1)

Thanks!

[dspjones]

Thanks, that query worked great. Took DB from 9.8GB to 4.9GB. Had to plug in the DB name in place of %DB_Name% but that was pretty clear. The query plus changing the retention settings should keep things running smoothly for quite some time. Sure wish MS would increase that limit for SQL Express.

[Vitaliy S.]

Yep, forgot to say that %DB_NAME% must be supplied with the variable value Good to know that you're up & running!