MQ tables - Avoiding Arithmetic Overflow Error
Article ID: 201871
Updated On:
Products
Issue/Introduction
When current MQ table (MQ1WP or MQ1OWP for example) identities are getting close to MSSQL server INT data type limit 2,147,483,647, what is the correct course of action?
What is a way to avoid arithmetic overflow error?
The error could look something like:
U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: c5aca0 DB-HDBC: c5ad80
U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
UCUDB - Status: '22003' Native error: '8115' Msg: 'Arithmetic overflow error converting IDENTITY to data type int.'
UCUDB - Status: '01000' Native error: '3606' Msg: 'Arithmetic overflow occurred.'
UCUDB Ret: '3590' opcode: 'INPK' SQL Stmnt: 'INSERT INTO MQ[tablename]
U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '01000', 'Arithmetic overflow occurred.'
Environment
Release : All
Component : AUTOMATION ENGINE
Database: SQL Server
Resolution
It should be possible to reseed the identity of the MQ tables with a command like:
DBCC CHECKIDENT (MQ1WP, RESEED, 1)
Before doing this, a DBA will need to confirm that there are no low identity MQ1WP records that have been hanging there for a long time.
The IDENTITY field cannot detect existing PKs with a value it is about to use next, if there is one it will lead to an error message. There shouldn't be any that low, but again, that needs to be confirmed first.
A DBA will need to be consulted to help with this and should also be able to determine specifics, including if the reseed can be done while Automic is running.
Feedback
