Hey guys,
Good afternoon!

In this post I will demonstrate to you how to prevent a type of problem in SQL Server that can cause a lot of disruption in the life of a DBA, and takes a long time to resolve and can be easily avoided and monitored, which is when an IDENTITY column ends up reaching the limit value of its 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 must always monitor whether 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 if the DBA tries to act quickly, a type change operation, especially when the table overflows the INT, takes a long time to be carried out and this in the middle of business hours, is really a very serious problem.

The most used data types in columns with IDENTITY and the 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 I only listed the types above, you can also use NUMERIC and DECIMAL to form your IDENTITY, although it is 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 starting with SQL Server 2012, and which can also end up reaching the limit and generating serious problems in production. If you are not familiar 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 commands below:

CREATE TABLE #TesteTinyInt (
    Id TINYINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)

DECLARE @Contador INT = 1, @Total INT = 255

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO #TesteTinyInt ( Nome )
    VALUES ( ' Teste ' + CAST(@Contador AS VARCHAR(10)) )

    SET @Contador += 1
    
END

-- Aqui vai dar erro
INSERT INTO #TesteTinyInt ( Nome )
VALUES ( ' Teste Overflow ' )

If you want to create monitoring for this type of situation or just view the current situation of your instance's databases, simply run the query below (it also monitors the maximum value of SEQUENCES):
View source code

IF (OBJECT_ID('tempdb..#Valor_Maximo_Tipo_Dado') IS NOT NULL) DROP TABLE #Valor_Maximo_Tipo_Dado
CREATE TABLE #Valor_Maximo_Tipo_Dado (
    Ds_Tipo_Dado VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, 
    Vl_Maximo BIGINT
)

INSERT INTO #Valor_Maximo_Tipo_Dado 
VALUES 
   ('tinyint' , 255),
   ('smallint' , 32767),
   ('int' , 2147483647),
   ('bigint' , 9223372036854775807)



---------------------------------------------------------------------------------------------------
-- TAMANHO MÁXIMO DE IDENTITY
---------------------------------------------------------------------------------------------------
   
IF (OBJECT_ID('tempdb..#Tamanho_Maximo_Identity') IS NOT NULL) DROP TABLE #Tamanho_Maximo_Identity
CREATE TABLE #Tamanho_Maximo_Identity (
    Ds_Database sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tabela sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Coluna sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tipo_Dado sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Vl_Maximo bigint NULL,
    Vl_Inicial bigint NULL,
    Vl_Incremento bigint NULL,
    Vl_Ultimo_Valor bigint NULL,
    Qt_Linhas bigint NULL,
    Pr_Atingimento decimal (18, 2) NULL
)


EXEC master.dbo.sp_MSforeachdb '
IF (''?'' NOT IN (''msdb'', ''master'', ''model'', ''tempdb''))
BEGIN

    INSERT INTO #Tamanho_Maximo_Identity
    SELECT DISTINCT
        ''?'' AS Ds_Database,
        B.name AS Ds_Tabela,
        A.name AS Ds_Coluna,
        C.name AS Ds_Tipo_Dado,
        D.Vl_Maximo,
        CONVERT(VARCHAR(20), A.seed_value) AS Vl_Inicial,
        CONVERT(VARCHAR(20), A.increment_value) AS Vl_Incremento, 
        CONVERT(VARCHAR(20), A.last_value) AS Vl_Ultimo_Valor,
        E.row_count AS Qt_Linhas,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.last_value)) * 100 / D.Vl_Maximo) AS Pr_Atingimento
    FROM 
        [?].sys.identity_columns                A   WITH(NOLOCK)
        JOIN [?].sys.tables                     B   WITH(NOLOCK)    ON  A.[object_id] = B.[object_id]
        JOIN [?].sys.types                      C   WITH(NOLOCK)    ON  A.system_type_id = C.system_type_id
        JOIN #Valor_Maximo_Tipo_Dado            D   WITH(NOLOCK)    ON  C.name COLLATE SQL_Latin1_General_CP1_CI_AI = D.Ds_Tipo_Dado
        JOIN [?].sys.dm_db_partition_stats      E   WITH(NOLOCK)    ON  E.[object_id] = A.[object_id]
        JOIN [?].sys.indexes                    F   WITH(NOLOCK)    ON  E.index_id = F.index_id
    WHERE 
        E.row_count > 0


END'


SELECT * 
FROM #Tamanho_Maximo_Identity
WHERE Pr_Atingimento > 50
ORDER BY Pr_Atingimento DESC



---------------------------------------------------------------------------------------------------
-- TAMANHO MÁXIMO DE SEQUENCES (Se você estiver antes do SQL 2012, vai dar erro)
---------------------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Tamanho_Maximo_Sequence') IS NOT NULL) DROP TABLE #Tamanho_Maximo_Sequence
CREATE TABLE #Tamanho_Maximo_Sequence (
    Ds_Database sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Sequence sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tipo_Dado sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Vl_Maximo_Tipo_Dado bigint NULL,
    Vl_Inicial bigint NULL,
    Vl_Incremento bigint NULL,
    Vl_Minimo bigint NULL,
    Vl_Maximo bigint NULL,
    Vl_Ultimo_Valor bigint NULL,
    Pr_Atingimento_Tipo decimal (18, 2) NULL,
    Pr_Atingimento decimal (18, 2) NULL
)

EXEC master.dbo.sp_MSforeachdb '
IF (''?'' NOT IN (''msdb'', ''master'', ''model'', ''tempdb''))
BEGIN

    INSERT INTO #Tamanho_Maximo_Sequence
    SELECT DISTINCT
        ''?'' AS Ds_Database,
        A.name AS Ds_Sequence,
        B.name AS Ds_Tipo_Dado,
        C.Vl_Maximo AS Vl_Maximo_Tipo_Dado,
        CONVERT(VARCHAR(20), A.start_value) AS Vl_Inicial,
        CONVERT(VARCHAR(20), A.increment) AS Vl_Incremento, 
        CONVERT(VARCHAR(20), A.minimum_value) AS Vl_Minimo,
        CONVERT(VARCHAR(20), A.maximum_value) AS Vl_Maximo,
        CONVERT(VARCHAR(20), A.current_value) AS Vl_Ultimo_Valor,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.current_value)) * 100 / C.Vl_Maximo) AS Pr_Atingimento_Tipo,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.current_value)) * 100 / CONVERT(BIGINT, CONVERT(VARCHAR(20), A.maximum_value))) AS Pr_Atingimento
    FROM 
        [?].sys.sequences                       A   WITH(NOLOCK)
        JOIN [?].sys.types                      B   WITH(NOLOCK)    ON  A.system_type_id = B.system_type_id
        JOIN #Valor_Maximo_Tipo_Dado            C   WITH(NOLOCK)    ON  B.name COLLATE SQL_Latin1_General_CP1_CI_AI = C.Ds_Tipo_Dado


END'


SELECT * 
FROM #Tamanho_Maximo_Sequence
WHERE Pr_Atingimento > 50
ORDER BY Pr_Atingimento DESC


SELECT * 
FROM #Tamanho_Maximo_Sequence
WHERE Pr_Atingimento_Tipo > 50
ORDER BY Pr_Atingimento_Tipo DESC

If you don't have a basis for carrying out this test and want to create your own just to see what the result looks like, use this query:
View source code

IF (OBJECT_ID('dbo.TesteTinyInt') IS NOT NULL) DROP TABLE dbo.TesteTinyInt
CREATE TABLE dbo.TesteTinyInt (
    Id TINYINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)


DECLARE @Contador INT = 1, @Total INT = 255

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteTinyInt ( Nome )
    VALUES ( ' Teste ' )

    SET @Contador += 1
    
END



IF (OBJECT_ID('dbo.TesteSmallInt') IS NOT NULL) DROP TABLE dbo.TesteSmallInt
CREATE TABLE dbo.TesteSmallInt (
    Id SMALLINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)


SET @Contador = 2
SET @Total = 15

INSERT INTO dbo.TesteSmallInt ( Nome )
VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteSmallInt ( Nome )
    SELECT Nome FROM dbo.TesteSmallInt

    SET @Contador += 1
    
END


-- Cuidado, pois aqui vai demorar um pouco e consumir muita TempDB
IF (OBJECT_ID('dbo.TesteInt') IS NOT NULL) DROP TABLE dbo.TesteInt
CREATE TABLE dbo.TesteInt (
    Id INT IDENTITY(1, 1),
    Nome VARCHAR(50)
)

SET @Contador = 1
SET @Total = 20

INSERT INTO dbo.TesteInt ( Nome )
VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteInt ( Nome )
    SELECT nome FROM dbo.TesteInt

    SET @Contador += 1
    
END


-- As Sequences foram implementadas no SQL Server 2012. 
-- Se você usa uma versão anterior, não rode daqui pra baixo, senão vai dar erro na sua execução.
CREATE SEQUENCE dbo.SEQ01_Teste AS TINYINT
START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 200
GO


SET @Contador = 1
SET @Total = 180

-- Incrementa a Sequence
WHILE(@Contador <= @Total)
BEGIN
    
    SELECT NEXT VALUE FOR dbo.SEQ01_Teste

    SET @Contador += 1
    
END

Query result:

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