Database Maintenance errors - 'dbm_VolumeUsage_DetailToStatistics'

Hi,

I am seeing error under db maintenance log file as below. Can anyone please assist here?

ERROR SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL - Failed to execute procedure: dbm_VolumeUsage_DetailToStatistics
System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error for type smallint, value = 99840.312500.
Arithmetic overflow error for type smallint, value = 79745.148438.
Arithmetic overflow error for type smallint, value = 66142.804688.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at SolarWinds.Data.DatabaseMaintenance.StandardTableHandlerDAL.InternalExecuteStoredProc(String procedureName, SqlParameter[] parameters, CommandType commandType)
ClientConnectionId:b9f4fd23-b24b-4081-a854-d0b8472a1ef5
Error Number:232,State:2,Class:16

However, db maintenance is getting completed. It seems like the VolumeUsage_DetailToStatistics table is bottleneck here.

Thanks in advance.

@KMSigma.SWI @aLTeReGo @AlexSoul

Find more posts tagged with

npm

database_maintenance

Accepted answers

aLTeReGo

This error is typically caused when a monitored volume unusual values resulting in absurd allocation percentages well over 16M%.

VolumeSpaceUsed	VolumeSpaceAvailable
4.39801E+12	-4.39799E+12

You should be able to locate the volume with the following query.

SELECT [N].[NodeID], [N].[Caption], [V].[VolumeID], [V].[Caption], [V].[VolumePercentUsed]
FROM [Volumes] [V]
JOIN [Nodes] [N] ON [N].[NodeID] = [V].[NodeID]
WHERE [VolumePercentUsed] > 100

I would recommend to drop/remove the volume(s) returned as a result of that query from monitoring and verify that resolves your database maintenance issue. At a minimum, I would suggest looking at the devices returned by the above query to determine if there is anything that would explain why they are returning such unusual values.

All comments

aLTeReGo

This error is typically caused when a monitored volume unusual values resulting in absurd allocation percentages well over 16M%.

VolumeSpaceUsed	VolumeSpaceAvailable
4.39801E+12	-4.39799E+12

You should be able to locate the volume with the following query.

SELECT [N].[NodeID], [N].[Caption], [V].[VolumeID], [V].[Caption], [V].[VolumePercentUsed]
FROM [Volumes] [V]
JOIN [Nodes] [N] ON [N].[NodeID] = [V].[NodeID]
WHERE [VolumePercentUsed] > 100

WinterSoldier

Hi @aLTeReGo,

Thank you for helping with the query. It worked out.

One more help needed. I do see an error in the same log file while running index defragmentation. The error is mentioned below,

ERROR SolarWinds.Data.IndexDefragmentation.IndexDefragmentationExecutor - Defragmentation for Table: CustomPollerStatistics_Hourly Index: PK_CustomPollerStatistics_Hourly Timeout: 394 Fragmentation: 24.7990414504114 ForceRebuild: False failed: System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SolarWinds.Orion.Common.SqlHelper.ExecuteNonQuery(SqlCommand command, SqlConnection connection, SqlTransaction transaction)
at SolarWinds.Data.IndexDefragmentation.IndexDefragmentationExecutor.DefragmentIndex(IndexDefragOperationParameters operationParameters)
ClientConnectionId:4149e925-eee0-4224-96ba-98376586505f
Error Number:-2,State:0,Class:11
2021-03-16 13:09:33,234 [1] INFO SolarWinds.Data.IndexDefragmentation.IndexDefragmenter - Executing defragmentation for index Index: PK_CustomPollerStatistics_Hourly Table:CustomPollerStatistics_Hourly Rows:31287200 Fragmentation:24.7990414504114 Type:Clustered AllowPageLocks:True has finished in 394 seconds.
2021-03-16 13:09:33,234 [1] INFO SolarWinds.Data.IndexDefragmentation.IndexDefragmenter - Defragmentation interrupted because of timeout.

Can you help me in this please?

Thank you.

aLTeReGo

The error is simply stating that index defragmentation took longer to complete than the timeout value. By default, this is 3600 seconds or 1 hour. Depending upon the number of things monitored and your retention settings, one hour is not very long. To adjust the timeout value for index defragmentation, see the following KB article.

support.solarwinds.com/.../Enable-Automatic-Index-Defragmentation-in-Orion-Platform