Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.
Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.
Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.
--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND t.name = 'compteur_identite'))
BEGIN
DROP TABLE dbo.compteur_identite
END
--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT NULL
, [max_id] NUMERIC(38,0) NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)
--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name
FROM sys.schemas s
INNER JOIN sys.objects o ON o.schema_id = s.schema_id
WHERE s.name <> 'sys'
AND o.name NOT IN ('MASTERKEY', 'spt_values') --- ici exclure ce qui n'est pas nécessaire
OPEN curseur
FETCH NEXT FROM curseur INTO @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
PRINT @schema + '.' + @table;
--- insertion dans la table de sortie
INSERT INTO dbo.compteur_identite ( [schema]
, [table], [index_pk]
, [column], [type]
, [seed], [increment]
, [max_id], [current]
, [nombre de lignes restant]
, [critique])
SELECT s.name AS [schema]
, o.name AS [table], i.name AS [index_pk]
, c.name AS [column], t.name AS [type]
, IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
, CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
, CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
WHERE i.type = 1 --- pk
AND s.name = @schema
AND o.name = @table
;
FETCH NEXT FROM curseur INTO @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;
--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
;
Bien sût cette requête doit être adaptée a vos besoins.
Quelques infos clé :
- Le nom de la table dbo.compteur_identite est arbitraire choisissez celui qui vous fait plaisir ;-)
- Dans la déclaration du curseur sur la liste des tables a surveiller retirer les tables inutiles, moi j'ai retiré le schéma sys et les deux tables 'MASTERKEY' et 'spt_values'.
- Calcul de la colonne "Critique", j'ai posé un seuil à 1 million de lignes restant le seuil doit être ajusté en fonction de la vitesse de remplissage et de votre capacité à agir lorsqu'il n'y a plus assez de place.
- Les filtres sur les types de colonne sont assez obscur, je les ai déterminé par recherche : Tout ce qui a une collation c'est des colonnes de type texte : pas de problème ici. Tout les type avec une longeur à 0 ou négative (il y en an si si) sont filtré pour que les calculs restent cohérents, les type avec une xprec à 0 sont aussi filtrées (Exemple les type images).
- La requête finale est a ajusté en fonction de vos besoins et peut être jouée autant de fois que nécessaire.
Enjoy !
