Introduction
Des traitements avec SQL Server 2000 comme par exemple la mise à jour
hebdomadaire des statistiques peuvent remonter des erreurs avec le message 3628
(a floating point exception occurred) :
update statistics curveLab_estimates goMsg 3628, Level 16, State 1, Server SRVWINFR3, Line 1 A floating point exception occurred in the user process. Current transaction is canceled.
Ce message est caractéristique de l’injection d’une donnée incohérente (Not
A Number) dans une colonne de type float.
Un précédent article paru en mars 2010 présente la nouvelle option "with
data_purity" dans les commandes dbcc checkdb et dbcc checktable, nouvelle
option introduite avec SQL Server 2005 (MS SQL Server 2005 et l’option WITH
DATA_PURITY des commandes DBCC CHECKDB et DBCC CHECKTABLE). L’option WITH
DATA_PURITY précise les colonnes pour lesquelles les données incohérentes ou
hors intervalle ont pu être injectées et acceptées lorsque la base était en
version SQL Server 7.0 ou SQL Server 2000. Ces incohérences sont générées
généralement par des exceptions applicatives non gérées, l’article précité
décrit comment générer ces types d’incohérences avec C# .NET.
Avec SQL Server 2000 et très probablement SQL Server 7.0, pas de chance, la ou les colonnes infectées ne sont pas mentionnées dans le message d’erreur. Voici quelques pistes pour retrouver facilement ces lignes et colonnes infectées afin de corriger leurs valeurs.
3 options possibles présentées ici :
- Sauvegarde de la base de données en version SQL Server 2000 et restauration temporaire vers un serveur SQL Server 2005 ou 2008 disponible afin de passer l’option
WITH DATA_PURITYde la commandedbcc checkdb. - Interrogation des colonnes de type float pour la table incriminée.
- Export des données avec bcp pour détecter vers quelle ligne et quelle colonne l’export tombe en échec.
Contexte
La base de données s’appelle curvelab et la table qui présente une incohérence et génère cette erreur 3628 s’appelle curvelab_estimates, table dont la structure est donnée ci-dessous (le résultat est épuré pour la lisibilité) :
exec sp_help curvelab_estimatesColumn_name Type ------------------------------------- ------------------------ estimates_ID int dte datetime country_iso varchar country_name varchar crncy varchar beta0 float beta1 float beta2 float beta3 float tau1 float tau2 float mae float msqe float bidask float freq int mat_max float country_type varchar Identity Seed Increment Not For Replication ------------ ---------- ------------ ------------------- estimates_ID 1 1 0
Points à noter : la table comporte une colonne identity estimates_ID servant
de clé primaire et 10 colonnes de type float, soit 10 incohérences par ligne
possibles.
Méthode 1 : BACKUP/RESTORE vers SQL Server 2005/2008 pour lancer l’option WITH DATA_PURITY de la commande DBCC CHECKDB ou DBCC CHECKTABLE
Il s’agit de la méthode la plus simple et la plus efficace pour retrouver les lignes et colonnes infectées sans devoir interroger toutes les colonnes de type float de la table, surtout si cette table est volumineuse, par ailleurs c’est éventuellement l’occasion de vérifier l’intégralité de la base et ne pas se cantonner à la table présentant ces symptômes.
Les 2 conditions ci-dessous doivent être au moins réunies :
- Un serveur SQL Server 2005 ou 2008 est disponible (si possible un serveur de test et non de production) pour accueillir une sauvegarde de la base SQL Server 2000.
- L’espace disque est disponible et la base SQL Server 2000 n’est pas trop volumineuse.
Une sauvegarde de la base curvelab est réalisée en version 2000 :
backup database curvelab to disk='D:\MSSQL\curvelab.2005.bak'Cette sauvegarde est envoyée ensuite sur un serveur SQL Server 2005, la migration de SQL Server 2000 vers SQL Server 2005 est automatique lors de la restauration :
restore database curvelab from disk='E:\curvelab.2005.bak' with move 'curvelab_data' to 'E:\curvelab.mdf', move 'curvelab_log' to 'E:\curvelab.ldf', replaceProcessed 4576 pages for database 'curvelab', file 'CurveLab_data' on file 1. Processed 1 pages for database 'curvelab', file 'CurveLab_log' on file 1. Converting database 'curvelab' from version 539 to the current version 611. Database 'curvelab' running the upgrade step from version 539 to version 551. ... Database 'curvelab' running the upgrade step from version 610 to version 611. RESTORE DATABASE successfully processed 4577 pages in 0.392 seconds (95.631 MB/sec).
La commande dbcc checkdb with data_purity, all_errormsgs est alors exécutée
sur la base migrée en version 2005 (la sortie est épurée pour la lisibilité)
:
dbcc checkdb('curvelab') with data_purity, all_errormgsDBCC results for 'curveLab_estimates'. Msg 2570, Level 16, State 3, Line 4 Page (1:1971), slot 53 in object ID 725577623, index ID 1, partition ID 329026431811584, alloc unit ID 47551455100928 (type "In-row data"). Column "mae" value is out of range for data type "float". Update column to a legal value. Msg 2570, Level 16, State 3, Line 4 Page (1:1971), slot 53 in object ID 725577623, index ID 1, partition ID 329026431811584, alloc unit ID 47551455100928 (type "In-row data"). Column "msqe" value is out of range for data type "float". Update column to a legal value. Msg 2570, Level 16, State 3, Line 4 Page (1:1972), slot 31 in object ID 725577623, index ID 1, partition ID 329026431811584, alloc unit ID 47551455100928 (type "In-row data"). Column "mae" value is out of range for data type "float". Update column to a legal value. Msg 2570, Level 16, State 3, Line 4 Page (1:1972), slot 31 in object ID 725577623, index ID 1, partition ID 329026431811584, alloc unit ID 47551455100928 (type "In-row data"). Column "msqe" value is out of range for data type "float". Update column to a legal value. ... There are 40082 rows in 730 pages for object "curveLab_estimates". CHECKDB found 0 allocation errors and 518 consistency errors in table 'curveLab_estimates' (object ID 725577623).
518 inconsistences sont détectées sur 2 colonnes de la table
curvelab_estimates : mae et msqe. Dans notre malheur seules 2 colonnes sont
infectées sur les 10.
La table ayant une colonne identity (estimates_id), les lignes à corriger
sont alors très facilement repérables avec la requête ci-dessous pour les 2
colonnes infectées :
select estimates_id, case isnumeric(convert(varchar(100), coalesce(msqe,0))) when 1 then 0 else 1 end, convert(varchar(100), coalesce(msqe,0)), case isnumeric(convert(varchar(100), coalesce(mae,0))) when 1 then 0 else 1 end, convert(varchar(100), coalesce(mae,0)) from curvelab_estimates where isnumeric(convert(varchar(100), coalesce(msqe,0))) = 0 or isnumeric(convert(varchar(100), coalesce(mae,0))) = 0estimates_id ------------ ---- -------------------- ---- ---------------------- 22915 1 -1.#IND 1 1.#QNAN 22949 1 -1.#IND 1 1.#QNAN … (259 rows affected)
Si en revanche, la table est bien trop volumineuse et que la requête
ci-dessus prend énormément de temps, les autres informations remontées par dbcc
checkdb ou dbcc checktable avec l’option with data_purity peuvent
éventuellement être d’une aide précieuse, en effet les numéros du fichier de
bases de données, de la page de données et de la ligne dans la page sont donnés
: Page (1:1971), slot 53.
La commande dbcc page de MS SQL Server avec l’option 3 pour afficher les
données des lignes en clair va permettre de retrouver la valeur de la clé
primaire (estimates_id) :
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) go dbcc traceon(3604) go dbcc page('curvelab', 1, 1971,3) goSlot 53 Offset 0x1d83 --------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 520F7D83: 00640030 00005983 00000000 00009b34 0.d..Y......4... … estimates_ID = 22915 dte = Oct 13 2008 12:00AM country_iso = GB country_name = United Kingdom Index-Linked crncy = GBP beta0 = 0 beta1 = 0 beta2 = 0 beta3 = 0 tau1 = 0 tau2 = 0 mae = 1.#QNAN msqe = -1.#IND bidask = 0 freq = 2 mat_max = 48 country_type = Index-Linked …
Les données incohérentes sont d’ailleurs retrouvées dans la commande dbcc
page.
La base SQL Server 2005 peut être supprimée, toutes les colonnes ayant des incohérences sont détectées et les lignes sont identifiées.
Méthode 2 : Interrogation des colonnes de type float de la table
Cela peut faire sourire en 2011, mais le parc SQL Server 2000 est encore très très présent mondialement, la fin de support de cette version a même été repoussée à décembre 2013. Si malheureusement aucune plateforme SQL Server 2005 ou SQL Server 2008 n’est disponible, que faire ?
Une solution simple peut consister à générer dynamiquement grâce aux tables
systèmes syscolumns et systypes des requêtes qui vont tester la validité de
chaque colonne float d’une table. Chacune des requêtes contiendra bien entendu
la ou les colonnes définissant la clé primaire de la table.
Pour écrire les requêtes ci-dessous détectant des valeurs non valides dans les colonnes de type float
select estimates_id ,
convert(varchar(100), coalesce(beta0,0)) as beta0
from curvelab_estimates
where isnumeric(convert(varchar(100), coalesce(beta0,0))) = 0
select estimates_id ,
convert(varchar(100), coalesce(beta1,0)) as beta1
from curvelab_estimates
where isnumeric(convert(varchar(100), coalesce(beta1,0))) = 0
...la génération dynamique de celles-ci peut être codée ainsi en s’appuyant sur
syscolumns et systypes :
declare @pkcolonnes varchar(1024)
declare @tablename sysname
select @tablename='curvelab_estimates'
select @pkcolonnes='estimates_id'
select ' select '+ @pkcolonnes + ' , '
+ 'convert(varchar(100), coalesce('+name+',0)) as ' + name + ' from '+ @tablename
+' where isnumeric(convert(varchar(100), coalesce('+name +',0))) = 0'
from syscolumns where object_name(id) = @tablename
and xtype = (select xtype from systypes where name='float')La variable @tablename identifie la table et la variable @pkcolonnes donne
la liste des clés de la table, clés séparées par des virgules. Ici estimate_id
est la seule colonne clé, si la clé est composite, on aura par exemple : select
@pkcolonnes = ' instrument_id, portfolio_id '.
Pour non seulement générer automatiquement les requêtes mais en plus les
exécuter, des exécutions dynamiques peuvent être associées en stockant le texte
SQL de ces requêtes dans une table temporaire #cmds :
declare @cmd varchar(2048)
declare @pkcolonnes varchar(1024)
declare @tablename sysname
select @tablename='curvelab_estimates'
select @pkcolonnes='estimates_id'
create table #cmds ( commande varchar(2048))
insert into #cmds (commande)
select ' select '+ @pkcolonnes + ' , '
+ 'convert(varchar(100), coalesce('+name+',0)) as ' + name + ' from '+ @tablename
+' where isnumeric(convert(varchar(100), coalesce('+name +',0))) = 0'
from syscolumns where object_name(id) = @tablename
and xtype = (select xtype from systypes where name='float')
declare c_loop cursor for select commande from #cmds
open c_loop
fetch next from c_loop into @cmd
while @@fetch_status = 0
begin
exec(@cmd)
fetch next from c_loop into @cmd
end
close c_loop
deallocate c_loop
drop table #cmdsEt toutes les lignes avec des colonnes float invalides sont listées pour analyse et corrections avec les équipes applicatives et fonctionnelles :
…
estimates_id tau2
------------ ------------------------------------
(0 row(s) affected)
estimates_id mae
------------ ------------------------------------
22915 1.#QNAN
22949 1.#QNAN
…Allons plus loin que lister à présent : pour préparer les futures commandes
update ou delete en fonction des décisions fonctionnelles à prendre pour
corriger, il est envisageable de stocker les résultats dans des tables
physiques temporaires grâce à la commande select into au lieu de les lister
:
select ' select '+ @pkcolonnes + ' , '
+ 'convert(varchar(100), coalesce('+name+',0)) as ' + name
+ ' into tmp_purity_'+@tablename+'_'+name
+ ' from '+ @tablename
+' where isnumeric(convert(varchar(100), coalesce('+name +',0))) = 0'
from syscolumns where object_name(id) = @tablename
and xtype = (select xtype from systypes where name='float')Avec l’adaptation ci-dessus une table tmp_purity_curvelab_estimates_mae est
générée et elle contient la ou les clés de la table curvelab_estimates ayant
des données incohérentes pour la colonne mae de type float :
select * from tmp_purity_curvelab_estimates_maeestimates_id mae ------------ --------------------------- 22915 1.#QNAN 22949 1.#QNAN ...
Il ne reste plus que les opérations de correction à réaliser en fonction du choix des équipes fonctionnelles et applicatives, voici 2 exemples : suppression des lignes ou mise à 0
|
|
Méthode 3 : Export des données avec la commande bcp
Cette méthode est pratique si la table est peu volumineuse et qu’il y a très très peu d’incohérences. Le binaire bcp en mode caractères génère systématiquement une erreur lorsqu’une donnée incohérente est rencontrée :
D:\> bcp "select * from curvelab..curvelab_estimates order by estimates_id" queryout curvelab_estimates.bcpc -Usa -t";" -S SRVWINFR3 -c1000 rows successfully bulk-copied to host-file. Total received: 22000 SQLState = 22003, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Numeric value out of range 22898 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1062 Average : (21561.21 rows per sec.)
Le fichier résultat s’arrête exactement à la ligne et à la colonne qui présente une incohérence :
…
23221;2008-10-27 00:00:00.000;GB;United Kingdom Index-Linked;GBP;0.0;0.0;0.0;0.0;0.0;0.0;Dans cet exemple, le binaire bcp s’est arrêté pour l’id 23221 et la colonne
12 qui correspond à la colonne mae
select colid, name from syscolumns where object_name(id)='curvelab_estimates' and colid=12 gocolid name ----- --------------------------------- 12 mae
L’opération s’avère en revanche fastidieuse pour les 518 incohérences comme c’est le cas ici.