Microsoft SQL Server 2000 et l'erreur 3628 (floating point exception)

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
go

Msg 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_PURITY de la commande dbcc 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_estimates
Column_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',
replace
Processed 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_errormgs
DBCC 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))) = 0
estimates_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} ])
dbcc traceon(3604)
go
dbcc page('curvelab', 1, 1971,3)
go

Slot 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 #cmds

Et 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_mae

estimates_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

update curvelab_estimates
set mae=0
from tmp_purity_curvelab_estimates_mae b,
  curvelab_estimates a
where a.estimates_id = b.estimates_id 
delete from curvelab_estimates
from curvelab_estimates a
inner join tmp_purity_curvelab_estimates_mae b   
on a.estimate_id = b.estimate_id
        

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 -c

1000 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
go

colid   name
-----   ---------------------------------
   12    mae

L'opération s'avèr en revanche fastidieuse pour les 518 incohérences comme c'est le cas ici.