SQL Server - How to identify, monitor, and prevent overflow in IDENTITY and SEQUENCE columns from tables with many records

Views: 1.246
Reading Time: 4 minutes

Hello people,
Good afternoon!

In this post I will show you how to prevent a type of SQL Server problem that can cause a lot of disruption to a DBA's life, takes a long time to resolve, and can be easily avoided and monitored, which is when an IDENTITY column reaches the limit value of your data type and when trying to insert new records into the table you will see an error message like this:

Msg 8115, Level 16, State 1, Line 18
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.

This error occurs because each data type has a maximum value that it can reach. The DBA always needs to monitor that this value is not approaching the limit, because when this happens, new records are not written.

In a production scenario, this is a disaster for the company, and even though the DBA tries to act quickly, a type change operation, especially when the table overflows the INT, takes a long time to perform, and this in the middle of business hours is really a very serious problem.

The most commonly used data types in IDENTITY columns and allowed values ​​are:

Data TypeInterval
tinyint0 to 255 (1 byte)
smallint-32.768 to 32.767 (2 bytes)
int-2.147.483.648 to 2.147.483.647 (4 bytes)
bigint-9.223.372.036.854.775.808 to 9.223.372.036.854.775.807 (8 bytes)

Although you have listed only the above types, you can also use NUMERIC and DECIMAL to form your IDENTITY, although not very common. However, the maximum size of NUMERIC and DECIMAL will depend on the scale and precision you declare in the column.

In this post, we will also monitor the SEQUENCES, which were implemented from SQL Server 2012, which may also eventually reach the limit and lead to serious production issues. If you are unfamiliar with SQL Server's SEQUENCE feature, read the post Working with Sequences in SQL Server.

A practical way to simulate this problem is by running the following commands:

If you want to create a monitoring of this type of situation or just see how is the current situation of the databases of your instance, just run the query below (also monitors the maximum value of SEQUENCES):
View source

If you don't have a basis for this test and want to create yours just to see what the result is, use this query:
View source

Query Result:

And that's it folks!
I hope you enjoyed this post and see you next time.