L'incrément d'identité saute dans la base de données SQL Server

Question

Dans une de mes tables Fee dans la colonne "ReceiptNo" de la base de données SQL Server 2012, l'incrément d'identité a soudainement commencé à sauter à 100s au lieu de 1 en fonction des deux choses suivantes.

1. si c'est 1205446, il saute à 1206306, si c'est 1206321, il saute à 1207306 et si c'est 1207314, il saute à 1208306. Ce que je veux vous faire remarquer, c'est que les trois derniers chiffres restent constants, c'est-à-dire 306, chaque fois que le saut se produit, comme le montre l'image suivante.

2. ce problème se produit lorsque je redémarre mon ordinateur

Answer 1

Vous rencontrez ce comportement en raison d'une amélioration des performances depuis SQL Server 2012.

Par défaut, il utilise désormais une taille de cache de 1 000 lors de l'allocation des données. IDENTITY valeurs pour un int et le redémarrage du service peut "perdre" des valeurs inutilisées (la taille de la mémoire cache est de 10 000 pour la colonne bigint / numeric ).

Ceci est mentionné dans la documentation

SQL Server peut mettre en cache les valeurs d'identité pour des raisons de performances et certaines des valeurs attribuées peuvent être perdues lors d'une panne de la base de données ou d'une panne du redémarrage du serveur. Cela peut entraîner des lacunes dans la valeur de l'identité lors de l'insertion. l'insertion. Si les lacunes ne sont pas acceptables, l'application doit utiliser son propre mécanisme pour générer des valeurs clés. propre mécanisme pour générer des valeurs de clé. L'utilisation d'un générateur de séquence avec le site NOCACHE L'option peut limiter les écarts aux transactions qui ne sont jamais jamais engagées.

D'après les données que vous avez montrées, il semble que cela se soit produit après la saisie des données du 22 décembre, puis, au redémarrage, le serveur SQL a réservé les valeurs. 1206306 - 1207305 . Après la saisie des données pour les 24 et 25 décembre, un autre redémarrage et le serveur SQL a réservé la plage suivante. 1207306 - 1208305 visible dans les entrées du 28.

À moins que vous ne redémarriez le service à une fréquence inhabituelle, il est peu probable que les valeurs "perdues" modifient de manière significative la plage de valeurs autorisées par le type de données.

Si, pour une raison ou une autre, cela constitue un réel problème pour vous, voici quelques solutions de contournement possibles...

1. Vous pouvez utiliser un SEQUENCE au lieu d'une colonne d'identité et définir une taille de cache plus petite par exemple et utiliser NEXT VALUE FOR dans une colonne par défaut.
2. Ou appliquer le drapeau de trace 272 qui rend le IDENTITY allocation enregistrée comme dans les versions jusqu'à 2008 R2. Ceci s'applique globalement à toutes les bases de données.
3. Ou, pour les versions récentes, exécutez ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF pour désactiver la mise en cache de l'identité pour une base de données spécifique.

Vous devez savoir qu'aucune de ces solutions de contournement n'assure l'absence de lacunes. Cela n'a jamais été garanti par IDENTITY car cela ne serait possible qu'en sérialisant les insertions dans la table. Si vous avez besoin d'une colonne sans espace, vous devrez utiliser une solution différente de l'une des deux solutions suivantes IDENTITY o SEQUENCE

Answer 2

Ce problème survient après le redémarrage du serveur SQL.

La solution est la suivante :

• Exécuter SQL Server Configuration Manager .

• Sélectionnez Services du serveur SQL .

  

• Cliquez à droite sur SQL Server et sélectionnez Propriétés .

• Dans la fenêtre qui s'ouvre sous Paramètres de démarrage , type -T272 et cliquez sur Ajouter puis appuyez sur Appliquer et redémarrer.

  

Answer 3

De SQL Server 2017+ vous pourriez utiliser MODIFIER LA CONFIGURATION DE LA PORTÉE DE LA BASE DE DONNÉES :

IDENTITY_CACHE = { ON | OFF }

Active ou désactive le cache d'identité au niveau de la base de données. La valeur par est ON. La mise en cache d'identité est utilisée pour améliorer les performances d'INSERT sur tables avec des colonnes d'identité. Pour éviter les lacunes dans les valeurs de la colonne colonne Identity dans les cas où le serveur redémarre de manière inattendue ou serveur secondaire, désactivez l'option IDENTITY_CACHE. Cette option est similaire à l'option existante SQL Server Trace Flag 272, sauf qu'elle peut être définie au niveau de la base de données plutôt que niveau du serveur.

(...)

G. Définir IDENTITY_CACHE

Cet exemple désactive le cache d'identité.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Answer 4

Je sais que ma réponse peut être tardive. Mais j'ai résolu le problème d'une autre manière en ajoutant une procédure stockée de démarrage dans SQL Server 2012.

Créez la procédure stockée suivante dans la base de données principale.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

Ensuite, ajoutez-le dans le Start up en utilisant la syntaxe suivante.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

C'est une bonne idée si vous avez peu de tables, mais si vous devez le faire pour de nombreuses tables, cette méthode fonctionne toujours mais n'est pas une bonne idée.

Answer 5

Ce problème est encore très courant chez de nombreux développeurs et applications, quelle que soit leur taille.

Malheureusement, les suggestions ci-dessus ne résolvent pas tous les scénarios, c'est-à-dire qu'en cas d'hébergement partagé, vous ne pouvez pas compter sur votre hôte pour définir le paramètre de démarrage -t272.

De plus, si vous avez des tables existantes qui utilisent ces colonnes d'identité comme clés primaires, cela représente un énorme effort de supprimer ces colonnes et d'en recréer de nouvelles pour utiliser la solution de contournement de la séquence BS. La solution de la séquence n'est valable que si vous concevez les tables à partir de zéro dans SQL 2012+.

En résumé, si vous utilisez Sql Server 2008R2, restez-le. Sérieusement, restez-y. Jusqu'à ce que Microsoft admette qu'ils ont introduit un énorme bug, qui est toujours là même dans Sql Server 2016, alors nous ne devrions pas mettre à niveau jusqu'à ce qu'ils l'assument et le corrigent.

Microsoft a carrément introduit un changement de rupture, c'est-à-dire qu'ils ont cassé une API qui ne fonctionne plus comme prévu, du fait que leur système oublie l'identité actuelle lors d'un redémarrage. Cache ou pas, c'est inacceptable, et le développeur de Microsoft du nom de Bryan doit l'assumer, au lieu de dire au monde que c'est "par conception" et une "fonctionnalité". Bien sûr, la mise en cache est une fonctionnalité, mais perdre la trace de ce que la prochaine identité devrait être, N'EST PAS UNE FONCTION. C'est un putain de BUG ! !!

Je vais partager la solution de contournement que j'ai utilisée, parce que mes bases de données sont sur des serveurs d'hébergement partagé, et aussi parce que je ne vais pas supprimer et recréer mes colonnes de clés primaires, ce qui serait un énorme problème.

Au lieu de cela, voici mon hack honteux (mais pas aussi honteux que ce bug POS que microsoft a introduit).

Hack/Fix :

Avant vos commandes d'insertion, il suffit de réensemencer votre identité avant chaque insertion. Ce correctif n'est recommandé que si vous n'avez pas le contrôle administratif de votre instance Sql Server, sinon je suggère de réensemencer au redémarrage du serveur.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

Il suffit d'ajouter ces trois lignes juste avant l'insertion, et tout devrait bien se passer. Cela n'affectera pas vraiment les performances, c'est-à-dire que cela ne sera pas perceptible.

Goodluck.