You may not have given this a thought, but every Dynamics GP database table has a DEX_ROW_ID column, and there is a maximum value for that field. It is 2,147,483,647. Just over 2 billion.
Every time you add a record to a GP table the DEX_ROW_ID increments by one. It does not decrement when you remove a record. If you delete a record, then add another one, it will have DEX_ROW_ID of two.
Some tables, particularly with GP Manufacturing, see a very high volume of data, although this is not just a Manufacturing related issue. At some point, when the DEX_ROW_ID becomes too large for the datatype (SQL int), you will receive an error like the one shown below.
Arithmetric overflow error converting IDENTITY to data type int.
When this occurs data can no longer be added to the table. If this were to happen on a GL table, it would render GP non-functional until the issue was resolved.
We are not going to cover how to address this because there can be unique issues with each table that require a different approach, addressing it requires SQL scripting, and it has the potential to result in data loss and damage to the database if not done correctly.
However, it is worth checking now to see if you might have a problem brewing so you can take steps now to prevent an issue.
Download a ZIP of a SQL script to find GP tables with the largest DEX_ROW_ID (note: this is not the same as tables with the most records). The ZIP file contains a text file with the SQL script. Below is also an image of the script. If tables are getting close to the 2 billion number shown above, contact a GP & SQL expert for assistance.
