SQL Server - Como identificar, monitorar e prevenir overflow em colunas com IDENTITY e SEQUENCE de tabelas com muitos registros

Post Views 3,420 views

Reading time 4 minutes

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 dado	Intervalo
tinyint	0 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 ' )

CREATE TABLE #TesteTinyInt (

Id TINYINT IDENTITY(1, 1),

Name VARCHAR(50)

)

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

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO #TesteTinyInt ( Name )

VALUES ( ' Teste ' + CAST(@Contador AS VARCHAR(10)) )

SET @Contador += 1

END

-- Aqui vai dar erro

INSERT INTO #TesteTinyInt ( Name )

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

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

IF (OBJECT_ID('dbo.TesteTinyInt') IS NOT NULL) DROP TABLE dbo.TesteTinyInt

CREATE TABLE dbo.TesteTinyInt (

Id TINYINT IDENTITY(1, 1),

Name VARCHAR(50)

)

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

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO dbo.TesteTinyInt ( Name )

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

Name VARCHAR(50)

)

SET @Contador = 2

SET @Total = 15

INSERT INTO dbo.TesteSmallInt ( Name )

VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO dbo.TesteSmallInt ( Name )

SELECT Name 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),

Name VARCHAR(50)

)

SET @Contador = 1

SET @Total = 20

INSERT INTO dbo.TesteInt ( Name )

VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO dbo.TesteInt ( Name )

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

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:

And that's it, folks!
Espero que tenham gostado desse post e até a próxima.

Michel disse:

November 23, 2022 às 18:58

Como corrigir?

Responder
csamante disse:

February 8, 2017 às 20:03

Parabéns Dirceu, mais um excelente artigo para sua coleção!

Responder
- Dirceu Resende disse:
  
  February 24, 2017 às 06:54
  
  Obrigado pelo feedback, Caio. Abraço.
  
  Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como identificar, monitorar e prevenir overflow em colunas com IDENTITY e SEQUENCE de tabelas com muitos registros

You may also like...

3 Responses

Deixe uma respostaCancelar resposta

You may also like...

Azure SQL Database – Como criar e gerenciar usuários e logins com autenticação SQL e Azure Active Directory (Azure AD)

SQL Server – Como executar scripts PowerShell e Prompt-DOS (MS-DOS) utilizando CLR (C#)

SQL Server – Como identificar locks, blocks e sessões bloqueadoras

3 Responses

Deixe uma respostaCancelar resposta