dba-dash: Arithmetic overflow error converting numeric to data type numeric

I’m having an issue with the IdentityColumns collection.

This is on a SQL Server 2017 Standard RTM CU17. And is only seen on this specific server.

ErrorContext: Collect

Source: IdentityColumn

Microsoft.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting numeric to data type numeric. at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at DBADash.DBCollector.GetDT(String tableName, String SQL, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1331 at DBADash.DBCollector.AddDT(String tableName, String sql, Int32 commandTimeout, SqlParameter[] param) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1340 at DBADash.DBCollector.ExecuteCollection(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1039 at DBADash.DBCollector.<>c__DisplayClass73_0.<Collect>b__0(Context _) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 593 at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token) at Polly.Retry.RetryEngine.Implementation[TResult](Func3 action, Context context, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates1 shouldRetryResultPredicates, Action4 onRetry, CancellationToken cancellationToken, Int32 permittedRetryCount, IEnumerable1 sleepDurationsEnumerable, Func4 sleepDurationProvider) at Polly.Retry.RetryPolicy.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken) at Polly.Policy.Implementation(Action2 action, Context context, CancellationToken cancellationToken) at Polly.Policy.Execute(Action`2 action, Context context, CancellationToken cancellationToken) at DBADash.DBCollector.Collect(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 589 ClientConnectionId:9d275931-d791-4a16-b159-99097a915d2c Error Number:8115,State:1,Class:16

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 15

Commits related to this issue

Most upvoted comments

That fix, solves it for me. Thank you. And thank you again for this awesome tool.

I can also confirm all is well, except for one server’s set of historical databases that have the setting turned on. Here is my little test case:

declare @IdentityCollectionThreshold int = 5;

--select SESSIONPROPERTY('NUMERIC_ROUNDABORT')
SET NUMERIC_ROUNDABORT ON;

with cteDataTypes as (
	SELECT name [sys_name], system_type_id, max_length, precision, scale
	FROM sys.types
	WHERE max_length < 9 --equates to the 3rd Test previously added to prevent arithmatic overflow
	AND max_length > 0 --filtering out strings types
), cteTest1Parts as (
	select *, 0 as [calc_last_value_big]
		,CASE max_length 
				WHEN 1 THEN POWER(2.,max_length*8) 
				ELSE POWER(2.,max_length*8-1)-1 
		END [calc_max_ident] 
	from cteDatatypes
), cteTest1Calc as (
	/*calc.last_value_big / calc.max_ident > @IdentityCollectionThreshold*/
	select *, IIF(calc_last_value_big / calc_max_ident > @IdentityCollectionThreshold, 'True', 'False') [LastValueOverMaxIdent]  
	from cteTest1Parts
), cteTest2Parts as (
	select *, 1 [RC_row_count] -- in my case this value is 1
		, POWER(2E0,max_length*8) [calc_max_rows]
	from cteDataTypes
), cteTest2Calc as (
	/*OR RC.row_count  / calc.max_rows * 100 > @IdentityCollectionThreshold*/
	select *, iif(RC_row_count / calc_max_rows * 100 > @IdentityCollectionThreshold, 'True', 'False') [RowCountOverMaxRowsTest]
	from cteTest2Parts
)
select * 
from cteTest1Calc