Olá pessoal,
Bom tarde!

Neste post vou demonstrar a vocês como prevenir um tipo de problema no SQL Server que pode causar muito transtorno na vida de uma DBA, e demora um bom tempo para resolver e pode ser facilmente evitado e monitorado, que é quando uma coluna IDENTITY acaba atingindo o valor limite do seu tipo de dado e ao tentar inserir novos registros na tabela, você verá uma mensagem de erro como essa:

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

Esse erro ocorre porque cada tipo de dado possui um valor máximo em que ele pode atingir. O DBA precisa sempre monitorar se esse valor não está chegando próximo do limite, pois quando isso acontece, novos registros não são gravados.

Num cenário de produção, isso é um desastre para a empresa e mesmo que o DBA tente agir rapidamente, uma operação de alteração de tipo, principalmente quando a tabela estoura o INT, demora muito para ser realizada e isso no meio do horário comercial, é realmente um problema gravíssimo.

Os tipos de dados mais utilizados em colunas com IDENTITY e os valores permitidos são:

Tipo de dadoIntervalo
tinyint0 a 255 (1 byte)
smallint-32.768 a 32.767 (2 bytes)
int-2.147.483.648 a 2.147.483.647 (4 bytes)
bigint-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 (8 bytes)

Apesar de ter listado apenas os tipos acima, você também possa utilizar NUMERIC e DECIMAL para formar seu IDENTITY, embora não seja muito comum. Entretanto, o tamanho máximo do NUMERIC e DECIMAL vai depender da escala e precisão que você declarar na coluna.

Neste post, também vamos monitorar as SEQUENCES, que foram implementadas a partir do SQL Server 2012, e que também podem acabar atingindo o limite e gerar sérios problemas em produção. Caso você não conheça o recurso SEQUENCE do SQL Server, dê uma lida no post Trabalhando com Sequences no SQL Server.

Uma forma prática de simular esse problema, é executando os comandos abaixo:

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 ' )

Caso você queira criar um monitoramento desse tipo de situação ou apenas visualizar como está a situação atual dos databases da sua instância, basta executar a query abaixo (também monitora o valor máximo de SEQUENCES):
Exibir o código-fonte

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

Se você não tem uma base para realizar esse teste e deseja criar a sua apenas para visualizar como é o resultado, utilize essa query:
Exibir o código-fonte

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

Resultado da consulta:

E é isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima.