Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Network Performance Monitor (NPM) > Configuration wizard error: Arithmetic overflow error converting IDENTITY to data type int - DiscoveredPollers table

Configuration wizard error: Arithmetic overflow error converting IDENTITY to data type int - DiscoveredPollers table

Updated June 26 2017

Overview

The configuration wizard displays the following error when upgrading NPM:

......\Main\Orion\Core\Src\Config\SQL Scripts\Migrate\PopulateNodePollers.sql|
2017-06-22 22:02:55,362 [12] ERROR DBConfigProcessor - Error while executing script - Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred..
Error details: System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.


It is failing on the following SQL query:

INSERT INTO DiscoveredPollers (ProfileID, NetObjectID, NetObjectType, PollerType)
            SELECT D.ProfileID, D.NodeID as NetObjectID, 'N' AS NetObjectType, P.PollerType   
  FROM DiscoveredNodes D CROSS JOIN 
            (
              SELECT 'N.Status.ICMP.Native' AS PollerType 
              UNION ALL
              SELECT 'N.ResponseTime.ICMP.Native' AS PollerType
            ) P

Environment

All Orion Platform versions

Cause 

This issue occurs because the DiscoveredPollers.ID column in the table is extremely large in count. When the configuration wizard or elsewhere attempts to convert that number, it fails because it is incapable of handling a large number.

 

For example, if DiscoveredPollers.ID is in the high billions or trillions:

int = -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) = 4 Bytes

 

SQL query:

DBCC CHECKIDENT ([DiscoveredPollers], NORESEED)

 

This returns the following:

Checking identity information: current identity value '2,147,483,647', current column value '668,158,927'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


A normal machine with no issues would return a low number:

Msg 7998, Level 0, State 3, Line 1
Checking identity information: current identity value '5040', current column value '5040'.

Resolution

Warning: 
In theory, all previously discovered objects can be affected, which can be fixed by rediscovery. For example, a discovered Cisco node BEFORE it is added to the nodes table and BEFORE you truncate the table, as it contains all the poller node support.


Reseed the DiscoveredPollers table by performing the following:
Note: Loss and corruption of data may occur if not done correctly.

  1. Stop all services.
  2. Run the following queries through SQL Management Studio in the database:
    truncate table DiscoveredPollers 
    dbcc checkident (DiscoveredPollers, reseed, 1)
  3. Restart services.
     

The DiscoveredProfiles table has no retention settings, which is why the steps need to be performed manually. The queries will restart the identity of the table at 1 rather than continuing at the number it is currently at. It is possible to reseed the table at 1 without truncating the table, but the range of IDs already in the database should be taken into consideration and whether the new data will reach that point before the historical data is cleared through retention for certain tables.

 

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.

 

Last modified
03:50, 27 Jun 2017

Tags

Classifications

Public