Network Management

Database Maintenance Failed with the message of "Failed to execute "Finalize Maintenance [CustomPollerStatistics_CS]" or "Finalize Maintenance [VIM_DatastoreStatistics]"

This article discussed about database maintenance failing with errors found Failed to execute 'Finalize Maintenance [xxx]'

First published date

3/2/2023 5:48 AM

Last published date

1/9/2025 7:12 PM

Overview

This article provides details around database maintenance fails with the following errors are found in the swDebugMaintenance.log:

ERROR SolarWinds.Data.DatabaseMaintenance.MaintenanceEngineHelper - Failed to execute 'Finalize Maintenance [CustomPollerStatistics_CS]' System.Data.SqlClient.SqlException (0x80131904): dbm_ExecuteMaintenance: dbm_AggregateData_part: dbm_AggregateTimeSerie_part: XACT_STATE=-1ALTER TABLE SWITCH statement failed. The specified partition 1 of target table 'SolarWindsOrionDB.dbo.CustomPollerStatistics_CS_Daily_hist' must be empty., ALTER TABLE CustomPollerStatistics_CS_Daily_stg SWITCH TO CustomPollerStatistics_CS_Daily_hist PARTITION 1 Warning: Null value is eliminated by an aggregate or other SET operation. Warning: Null value is eliminated by an aggregate or other SET operation. Warning: Null value is eliminated by an aggregate or other SET operation. Warning: Null value is eliminated by an aggregate or other SET operation.

OR

ERROR SolarWinds.Data.DatabaseMaintenance.MaintenanceEngineHelper - Failed to execute 'Finalize Maintenance [VIM_DatastoreStatistics]' System.Data.SqlClient.SqlException (0x80131904): dbm_ExecuteMaintenance: dbm_AggregateData_part: dbm_AggregateTimeSerie_part: XACT_STATE=-1ALTER TABLE SWITCH statement failed. The specified partition 1 of target table 'SolarWindsOrion.dbo.VIM_DatastoreStatistics_CS_Hourly_hist' must be empty., ALTER TABLE VIM_DatastoreStatistics_CS_Hourly_stg SWITCH

Product section

Network Performance Monitor

Cause

This is a known bug on version 2022.4.1. This is a common maintenance error, which seems to relate to any TimeSeries table, mainly the ones with many columns and many rows and it can be seen mainly in large customer environments (many nodes and engines).

Resolution

To resolve the issue, please upgrade to SolarWinds Platform 2023.1 .

As a workaround, you may proceed with below steps:

Execute query

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


TRUNCATE TABLE CustomPollerStatistics_CS_Daily_hist

Run the following queries to recreate these two stored procedures with fixed code (they will be a part of the next release 2023.1)

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.


IF EXISTS (SELECT [name] FROM sys.procedures WHERE [name] = N'dbm_CreateEmptyPartitions')
DROP PROCEDURE dbm_CreateEmptyPartitions;
GO

CREATE PROCEDURE dbm_CreateEmptyPartitions
/************************/
/*                      */
/* version 2023-02-15/1 */
/*                      */
/************************/
(
 @table_name NVARCHAR(96),
 @start_date DATETIME2,
 @end_date DATETIME2
)
AS
DECLARE @filegroup NVARCHAR(64);
DECLARE @partition_scheme NVARCHAR(128);
DECLARE @partition_function NVARCHAR(128);
DECLARE @error_message VARCHAR(MAX);
DECLARE @error_severity INT;
DECLARE @error_state INT;
DECLARE @max_part_date DATETIME2;
DECLARE @min_part_date DATETIME2;
DECLARE @processing_date DATETIME2;

BEGIN
	/* first and last existing partition must be empty otherwise this fails */
	-- todo / switch data, split and put back...
	IF @end_date IS NULL
		BEGIN
			SET @end_date = DATEADD(DAY, 1, @start_date);
		END;

	/* assuming we would not implement schemes with more filegroups, getting info from the first partition only */
	SELECT TOP 1 
	       @partition_scheme = ps.name,
	       @partition_function = pf.name,
		   @filegroup = fg.name
	  FROM sys.partition_schemes ps WITH (NOLOCK)
	  INNER JOIN sys.partition_functions pf WITH (NOLOCK)
			  ON pf.function_id = ps.function_id
	  INNER JOIN sys.data_spaces ds WITH (NOLOCK)
			  ON ds.data_space_id = ps.data_space_id
	  INNER JOIN sys.indexes i WITH (NOLOCK)
			  ON i.data_space_id = ps.data_space_id
	  INNER JOIN sys.tables t WITH (NOLOCK)
			  ON t.object_id = i.object_id
	  INNER JOIN sys.partitions p WITH(NOLOCK)
			  ON i.object_id = p.object_id
			 AND i.index_id = p.index_id
	  INNER JOIN sys.allocation_units au WITH(NOLOCK)
			  ON au.container_id = p.hobt_id			 
	  INNER JOIN sys.filegroups fg WITH(NOLOCK)
			  ON fg.data_space_id = au.data_space_id
	 WHERE t.name = @table_name
	   AND i.type IN (0,1,5)
	   AND p.partition_number = 1	   
	   AND au.type = 1
	;
	
	IF @partition_scheme IS NULL OR @partition_function IS NULL RETURN;	

	SELECT @max_part_date = CAST(MAX(value) AS DATETIME2),
		   @min_part_date = CAST(MIN(value) AS DATETIME2)
	  FROM sys.partition_range_values prv WITH (NOLOCK)
	 WHERE prv.function_id = (
							  SELECT pf.function_id 
								FROM sys.partition_functions pf WITH (NOLOCK)
							   WHERE pf.name = @partition_function
							 );

    /* range is already partitioned */
	IF @start_date >= @min_part_date AND @end_date <= @max_part_date RETURN;

	/* there is just one opened partition in a table opened from both sides */
	/* @max_part_date IS NULL, @min_part_date IS NULL !!! */	
	IF @max_part_date IS NULL and @min_part_date IS NULL
	BEGIN
		SET @max_part_date = DATEADD(DAY, -1, @start_date);
		SET @min_part_date = DATEADD(DAY, -1, @start_date);
		/*
		PRINT '@max_part_date, ' + CAST(@max_part_date AS NVARCHAR);
		PRINT '@min_part_date, ' + CAST(@min_part_date AS NVARCHAR);
		PRINT '@start_date, ' + CAST(@start_date AS NVARCHAR);
		PRINT '@end_date, ' + CAST(@end_date AS NVARCHAR);
		*/
	END;

	/* new starting date */
	IF @start_date < @min_part_date
		BEGIN
			--@start_date -- @min_part_date
			SET @processing_date = @start_date;
			WHILE @processing_date <> @min_part_date
				BEGIN
					BEGIN TRY
						EXEC dbm_AddPartition @partition_scheme, @partition_function, @filegroup, @processing_date;
					END TRY
					BEGIN CATCH
						SELECT @error_message = OBJECT_NAME(@@PROCID) + ': ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
						INSERT INTO PartitionErrors ([Message]) VALUES (@error_message);
						RAISERROR (@error_message, @error_severity, @error_state) WITH NOWAIT;
						RETURN;
					END CATCH;					
					SET @processing_date = DATEADD(DAY, 1, @processing_date);
				END;
		END;

	/* new ending date */
	IF @end_date > @max_part_date
		BEGIN
			SET @processing_date = DATEADD(DAY, 1, @max_part_date);
			WHILE @processing_date <> DATEADD(DAY, 1, @end_date)
				BEGIN
					BEGIN TRY
						EXEC dbm_AddPartition @partition_scheme, @partition_function, @filegroup, @processing_date;
					END TRY
					BEGIN CATCH
						SELECT @error_message = OBJECT_NAME(@@PROCID) + ': ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
						INSERT INTO PartitionErrors ([Message]) VALUES (@error_message);
						RAISERROR (@error_message, @error_severity, @error_state) WITH NOWAIT;
						RETURN;
					END CATCH;	
					SET @processing_date = DATEADD(DAY, 1, @processing_date);
				END;
		END;
END;

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

IF EXISTS (SELECT [name] FROM sys.procedures WHERE [name] = N'dbm_ExecuteMaintenance')
DROP PROCEDURE dbm_ExecuteMaintenance;
GO

CREATE PROCEDURE dbm_ExecuteMaintenance
/************************/
/* version 2023-02-16/1 */
/*                      */
/************************/
(
 @tab_name NVARCHAR(80),
 @date_from DATE = NULL
)
AS
DECLARE @error_message NVARCHAR(MAX);
DECLARE @error_severity INT;
DECLARE @error_state INT;

DECLARE @sql NVARCHAR(MAX);
DECLARE @target_tab_name NVARCHAR(96);
DECLARE @target_retention SMALLINT;
DECLARE @target_date_to DATETIME2;
DECLARE @target_date_from DATETIME2;
DECLARE @ancient_date_from DATETIME2;

DECLARE @is_detail_only_metric BIT;

BEGIN
	BEGIN TRY
		/* add default value when there is no value passed */
		IF @date_from IS NULL SET @date_from = CAST(GETDATE() AS DATE);

		/* check type of a metric */
		SELECT @is_detail_only_metric = tspc.IsDetailOnly
		  FROM dbm_TimeSerieProcessingConfig tspc WITH (NOLOCK)
		 WHERE tspc.TableType = 'Detail'
		   AND tspc.TableName = @tab_name;
	
		/* process current data and move them to _detail_hist */
		EXEC dbm_Move2Partitioned @tab_name = @tab_name, @date_from = @date_from, @check_retentions = 1;

		/* only for metric with more granularities */
		IF @is_detail_only_metric = 0
		BEGIN
			/* create partitions for _hourly_hist */				
			/* get oldest date from _detal_hist to create partitions for this data */
			SET @target_tab_name = @tab_name + '_Detail_hist'; 
			SELECT @target_date_from = DATEADD(DAY, -1, CAST(MIN(prv.value) AS DATE))
			  FROM sys.indexes ind WITH(NOLOCK)
			  LEFT JOIN sys.partitions p WITH(NOLOCK)
					 ON ind.object_id = p.object_id
					AND ind.index_id = p.index_id
			  LEFT JOIN sys.data_spaces ds WITH(NOLOCK)
					 ON ind.data_space_id = ds.data_space_id
			  LEFT JOIN sys.partition_schemes sch WITH(NOLOCK)
					 ON sch.data_space_id = ds.data_space_id
			  LEFT JOIN sys.partition_range_values prv WITH(NOLOCK)
					 ON prv.function_id = sch.function_id
					AND prv.boundary_id = p.partition_number
			  LEFT JOIN sys.index_columns ic WITH(NOLOCK)
					 ON (ic.partition_ordinal > 0
					AND ic.index_id = ind.index_id
					AND ic.object_id = ind.object_id)
			 WHERE p.object_id = OBJECT_ID(@target_tab_name)
			   AND ind.type IN (0,1,5)
			   AND p.rows > 0
			;

			SET @target_tab_name = @tab_name + '_Hourly_hist';
			SET @sql = 'exec dbm_CreateEmptyPartitions ''' + @target_tab_name + ''', @date_from, @date_to';
			SELECT @target_retention = tspc.RetentionDays
			  FROM dbm_TimeSerieProcessingConfig tspc
			 WHERE tspc.TableName = @tab_name
			   AND tspc.Granularity = 1;

			SET @target_date_to = @date_from;	
			EXEC sp_executesql @sql, N'@date_from DATETIME2, @date_to DATETIME2', @date_from = @target_date_from, @date_to = @target_date_to;	

			/* create partitions for _daily_hist */

			/* we have to check for possible very old data in hourly... */	
			/* @target_tab_name still points to hourly! */
			SELECT @ancient_date_from = DATEADD(DAY, -1, CAST(MIN(prv.value) AS DATE))
			  FROM sys.indexes ind WITH(NOLOCK)
			  LEFT JOIN sys.partitions p WITH(NOLOCK)
					 ON ind.object_id = p.object_id
					AND ind.index_id = p.index_id
			  LEFT JOIN sys.data_spaces ds WITH(NOLOCK)
					 ON ind.data_space_id = ds.data_space_id
			  LEFT JOIN sys.partition_schemes sch WITH(NOLOCK)
					 ON sch.data_space_id = ds.data_space_id
			  LEFT JOIN sys.partition_range_values prv WITH(NOLOCK)
					 ON prv.function_id = sch.function_id
					AND prv.boundary_id = p.partition_number
			  LEFT JOIN sys.index_columns ic WITH(NOLOCK)
					 ON (ic.partition_ordinal > 0
					AND ic.index_id = ind.index_id
					AND ic.object_id = ind.object_id)
			 WHERE p.object_id = OBJECT_ID(@target_tab_name)
			   AND ind.type IN (0,1,5)
			   AND p.rows > 0
			;

			/* if oldest date in hourly is older than rention period of daily data, create space for those old data */
			IF @ancient_date_from IS NOT NULL AND @ancient_date_from < @target_date_from SET @target_date_from = @ancient_date_from;

			SET @target_tab_name = @tab_name + '_Daily_hist';
			SET @sql = 'exec dbm_CreateEmptyPartitions ''' + @target_tab_name + ''', @date_from, @date_to';
			SELECT @target_retention = tspc.RetentionDays
			  FROM dbm_TimeSerieProcessingConfig tspc
			 WHERE tspc.TableName = @tab_name
			   AND tspc.Granularity = 2;

			SET @target_date_to = @date_from;
			EXEC sp_executesql @sql, N'@date_from DATETIME2, @date_to DATETIME2', @date_from = @target_date_from, @date_to = @target_date_to;
		END;

		/* process data aggregation - rollups */
		EXEC dbm_AggregateData_part @tab_name = @tab_name, @date = @date_from;
	END TRY
	BEGIN CATCH
		SELECT @error_message = OBJECT_NAME(@@PROCID) + ': ' + ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE();
		INSERT INTO PartitionErrors ([Message]) VALUES (@error_message);
		RAISERROR (@error_message, @error_severity, @error_state) WITH NOWAIT;
		RETURN;		
	END CATCH;
END;

Run Database maintenance manually - How to run the database maintenance manually in Orion environment

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment. You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same if any.