Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register 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