A coluna da primary key está próximo de atingir o limite do tipo de dados?

Fala pessoal, tudo bem?

Vou contar pra vcs um caso que tive nesse final de semana.
Uma empresa me ligou dizendo que o sistema não estava fazendo o que deveria ser feito e identificamos que uma tabela com uma chave primária do tipo int havia estourado o seu limite. Conseguimos achar isso no log da app, visto que no SQL não havia um erro explícito.
Diante disso precisei validar as outras tabelas tb já que o fornecedor do software não tinha essa validação, então comecei a criar alguns scripts que evoluíram para um report e depois para um job que envia de tempos em tempos caso tenha algum possível problema/ item a ser avalido por mim (agora) ou pelo cliente. 🙂

O script não está escrito da melhor forma possível, mas me atendeu nesse momento e está bem fácil de entender e vc pode modificá-lo para a forma que achar melhor.

declare @limite int = 60 -- Alterar esse valor de percentual para o limite desejado 

declare @id int = 1, @script nvarchar(max) 

drop table if exists #tab

-- gera uma tab temporária com as colunas, seu tipo e coloquei o limite de cada tipo para as validações futuras
select row_number() over (order by newid()) as ln,
    schema_name(tab.schema_id) as [schema_name], 
    pk.[name] as pk_name,
    ic.index_column_id as column_id,
    col.[name] as column_name, 
    tab.[name] as table_name,
	tp.name as column_type,
	0 as column_value,
	case tp.name  when  'tinyint' then 255 
							when 'smallint' then 32767
							when 'int' then 2147483648
							when 'bigint' then 9223372036854775807
							end limit
	into #tab
from sys.tables tab
    inner join sys.indexes pk         on tab.object_id = pk.object_id         and pk.is_primary_key = 1
    inner join sys.index_columns ic        on ic.object_id = pk.object_id         and ic.index_id = pk.index_id
    inner join sys.columns col         on pk.object_id = col.object_id        and col.column_id = ic.column_id
	inner join sys.types tp on tp.user_type_id = col.user_type_id
where tp.name  in ('smallint','int','bigint')
	
-- criei uma nova tabela para inserir o script e depois atualizar a primeira (poderia ter sido feito em uma tabela apenas)
drop table if exists #script

select 'update #tab set column_value = (select isnull(max('+column_name+'),0) from ' + table_name + ') where ln = '+ convert(varchar(100),ln) as script,  * into #script from #tab

-- loop para percorrer todas os registros e popular o maior ID de cada tabela/coluna
while exists (select 1 from #script where ln >= @id)
begin
	select @script = script from #script where ln = @id
	
	exec sp_executesql @script

	select @id = @id + 1
end


-- Criar nova tabela apenas com os registros com ultrapassaram o limite incluído na variável @limite
;with cte as (
select *, convert(numeric(12,2),(column_value * 100.) / limit) [% utilizado] from #tab) 
select * into #table_envio from cte where [% utilizado] > @limite    order by 10 desc

-- Se algum registro ultrapassar o limite, envia um email 

if exists (select 1 from #table_envio)
begin

	DECLARE @HTML VARCHAR(MAX);  
 
	SET @HTML = '
	<table border="1">
		<tr>
			<th>Tabela</th>
			<th>Colum value</th>
			<th>Limit</th>
			<th>% utilizado</th>
        
		</tr>' +  
		CAST ( 
		(
			SELECT 
				td = table_name, '',
				td = column_value, '',
				td = limit, '',
				td = [% utilizado], ''
            
			FROM #table_envio
			FOR XML PATH('tr'), TYPE
		) AS NVARCHAR(MAX) ) + '
	</table>';
 
 

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'DBA', 
		@recipients = 'vinicius@XXXXXXXXXXXXXXXXXX.com.br', 
		@subject = N'Validação de limite de coluna integer', 
		@body = @HTML, 
		@body_format = 'html'
END

Então é isso pessoal, o email nesse caso aí chegou assim.

Como tem a validação dos registros que ultrapassaram o limite acordo com o cliente, criei um job para executar uma vez por dia e enviar os alerta por e-mail.

Para corrigir o problema de fato e o sistema voltar a funcionar 100%, tivemos que excluir todas as constraints e índices que referenciavam a coluna, alterar o tipo de dados para bigint (no caso) e recriar as constraints e índice, o que levou um bom tempo pelo tamanho das tabelas!

Então é isso pessoal, obrigado e caso queriam dar alguma sugestão, é só me chamar ou comentar.

OBS: Esse limite é para o range de valores, porém você pode adicionar mais que que essa quantidade de registros caso seja uma chave composta,caso a coluna não seja uma chave (o que não era o caso aqui), ou caso não se importe em inserir valores negativos e sua app tratar isso.

Abs