Developpez.com

Une très vaste base de connaissances en informatique avec
plus de 100 FAQ et 10 000 réponses à vos questions

Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :



Récupération d'une table dont le chaînage est corrompu

sous Microsoft SQL Server

17 juillet 2004
MAJ: 15 janvier 2006

Par Fabien Celaia (Fadace)

Sommaire

Problématique

Il peut arriver qu'un fichier de base de données ait été corrompu par un problème matériel (fichier corrompu, disque abîmé, bug d'un contrôleur disque, etc.).

Le but de la démarche qui suit est l'extraction d'un maximum de données de cette table afin de pouvoir la supprimer et la recréer sur un fichier non corrompu.

Soulignons le fait que certaines des commandes qui vont suivre sont extrêmement intrusives. Il convient donc d'avoir une bonne connaissance du moteur de la base avant de se lancer dans une telle démarche.

La procédure officielle de récupération de données après un crash est de remonter une sauvegarde effectuée précédemment. Il se peut malheureusement que les sauvegardes précédentes aient déjà été corrompues. D'expérience, il apparaît que peu de DBA exécutent les commandes de vérification de validité (dbcc checkdb, dbcc checkalloc, dbcc checkcatalog) de la base avant d'exécuter les sauvegarde. La mise en place d'une stratégie permettant de minimiser les temps morts d'un moteur (Plan de maintenance, sauvegardes, réplications, haute disponibilité, etc) est la pierre angulaire d'une environnement stable, mais cette partie ne sera pas couverte ici.

Une autre source importante permettant de résoudre un certain nombre d'erreurs et le listing des erreurs retournées par le serveur.

Cette procédure a été validée sur un serveur MS-SQL serveur 2000 Enterprise Edition.

Déplorons que dès MS-SQL Serveur 2005, il n'est plus possible d'agir de cette manière sur les tables système, celles-ci étant blindées et remplacées par des vues système... inmodifiables ! En agissant de telle sorte, Microsoft quitte la stratégie d'ouverture initiée par Sybase pour se rapprocher de la façon de faire d'Oracle qui a depuis longtemps verrouillé ses tables de la même sorte.

La plupart des tables de MS-SQL sont, de manière interne, stockées sur des pages de données liées entre elles par des pointeurs.

La plupart des erreurs dues à une mauvaise allocation peuvent être résolues en exécutant un simple dbcc avec son option fix en mode mono-utilisateur. Ces commandes résolvent la plupart des erreurs relatives à l'entête de la page de données, mais ne fixent pas les corruptions relatives aux données stockées sur la page qui auraient pu être corrompues.

Accéder à la base corrompue

Si vous vous amusez à lire ce type d'article, c'est que vous savez passer d'une base a l'autre sur Microsoft SQL Server. Oui, bien sur, il s'agit de cette simple commande use NomDeBase. Si cette commande marche, vous pouvez directement passer plus bas.

Mais que ce passe-t-il si la base, lors du dernier rédemarrage du serveur, n'a pas pu être mise en ligne compte tenu de son état corrompu ?

Avant tout, il faut donc pouvoir accéder à ette base malgré ses problèmes. Il va nous falloir attaquer la table système master..sysdatabases comme suit:

sp_configure 'allow updates', 1  
go
RECONFIGURE WITH OVERRIDE
go
select status, status-256   -- à garder pour usage ultérieur
from master..sysdatabases 
where id=db_id('Northwind') 
go
update master..sysdatabases
set status=-32768
where id=db_id('Northwind')
go

... et redémarrer le serveur. Ce flag -32768 permet de remonter la base sans passer par sa phase de recovery. Autant dire qu'elle sera accessible, mais dans un état des plus instables.

Trouver les informations nécessaires

Information Comment la trouver Résultat
Nom du serveur select @@servername MONHOST\SQLSERVER
Nom de la base de données select db_name() Northwind
ID de la base select db_id('Northwind') 6
ID de l'objet corrompu Spécifié(objid) dans l'errorlog lors de l'apparition de l'erreur ou dans un résultat d'un dbcc 1977058079
Nom de l'objet select object_name(1977058079) EMPLOYEES
Page corrompue Spécifiée dans le message d'erreur (pageno, indid) 1396
Type de table  sp_help EMPLOYEES
sp_helpindex EMPLOYEES
Table utilisateur heap.
Script de création de la table et de ses indexes, ses contraintes, etc Via Entreprise Manager, SQL Query Analyzer ou n'importe quel outil de rétrogénération PowerAMC

Recherchons ensuite les informations concernant le début du chaînage de la table

Select indid, first, root, name
from sysindexes
where id = object_id('EMPLOYEES')

indid first root name ----- -------------- -------------- ------------ 1 0x880000000100 0x860000000100 PK_Employees 2 0x890000000100 0x890000000100 LastName 3 0x8B0000000100 0x8B0000000100 PostalCode 255 0x820000000100 0x820000000100 tEmployees

Microsoft a trouvé bon de coder les colonnes root et first en binaire. Afin d'éviter ces écueil, il faudra donc passer systématiquement par une interprétation de ces valeurs.

Il faut donc interpréter les valeurs hexadécimales de la manière suivante:

  • Découper en blocs de 2 octets:
    indid	first                   root                    name 
    -----	-------------------     --------------          ------------
    1	0x88 00 00 00 01 00     0x86 00 00 00 01 00	PK_Employees
    2	0x89 00 00 00 01 00     0x89 00 00 00 01 00	LastName
    3	0x8B 00 00 00 01 00     0x8B 00 00 00 01 00	PostalCode
    255	0x82 00 00 00 01 00     0x82 00 00 00 01 00	tEmployees
  • Tout se lit à l'envers, soit:
    indid   first                   root                    name 
    -----   -----------------       -----------------       ------------
    1       00 01 00 00 00 88       00 01 00 00 00 86       PK_Employees
    2       00 01 00 00 00 89       00 01 00 00 00 89       LastName
    3       00 01 00 00 00 8B       00 01 00 00 00 8B       PostalCode
    255     00 01 00 00 00 82       00 01 00 00 00 82       tEmployees

    Les 2 permiers groupes (en partant de la droite) représentent le no de ficher en 2 octets (ici 01 00 => 1)

    Les 4 groupes suivants (en partant de la droite) représentent le no de page

  • conversions en décimal:
    indid   first                   root                    name 
    -----   -------------------     -------------------     ------------
    1       Fichier 1, page 136     Fichier 1, page 134     PK_Employees
    2       Fichier 1, page 137     Fichier 1, page 137     LastName
    3       Fichier 1, page 139     Fichier 1, page 139     PostalCode
    255     Fichier 1, page 130     Fichier 1, page 130     tEmployees

La méthode

Dans le cas d'une corruption en indid = 1 ou 0, passons directement plus bas.

Dans celui d'une corruption de page de texte ou d'image (indid = 255), la méthode s'applique de la même manière. Pour trouver la 1ère page de ce chaînage corrompu, on peut soit se fier à champ first de sysindexes, soit partir de la page corrompue et remonter le chaînage à l'aide de la commande dbcc pglinkage, avec 0 comme dernière option. Dans notre cas de figure, celà donnerait:

dbcc traceon(3604)
go
dbcc pglinkage(6, 1396, 0, 2, 0, 0)
go

Object ID for pages in this chain = 1977058079. Page : 1395 Page : 1287 Page : 133 Page : 132 Page : 131 Page : 130 Beginning of chain reached. 6 pages scanned. Object ID = 1977058079. Last page in scan =130.

Ensuite, pour connaître la ligne incriminée, passons la valeur trouvée

Select ID_pers
from EMPLOYEES 
where convert(int, textptr(Photo))=130


ID_pers -------- 4

Vérifions le lien des pages de cette table à l'aide des commandes dbcc pglinkage et dbcc page. Notons que pour éviter de pousser en avant les corruptions, il serait bon de bénéficier de l'accès exclusif de la base (ou en tout cas de la table) incriminée.

use test
go
dbcc traceon(3604)       -- envoie les résultats à l'écran
go
dbcc page (6, 1396,0, 0) -- affiche la page corrompue en la lisant sur le disque (et pas en cache)
go

BUFFER: [...] PAGE HEADER: Page header for page 0x214C5800 pageno=1396 nextpg=1397 prevpg=1395 objid=1977058079 timestamp=0000 000132a0 nextrno=31 level=0 indid=0freeoff=1969 minlen=42 page status bits: 0x101 (0x0100 (PG_ADDEND), 0x0001(PG_DATA))

Le résultat obtenu nous retourne les pointeurs sur la page suivante (nextpgs) et la page précédente (prevpgs) du chaînage. Nous répétons cette commande sur ces 2 autres pages:

dbcc page (5, 1395)
dbcc page (5, 1397)
go


[...] PAGE HEADER: Page header for page 0x21509800 pageno=1395 nextpg=1396 prevpg=1394 objid=1977058079timestamp=0000 0001327d nextrno=31 level=0 indid=0 freeoff=1970 minlen=42 page status bits: 0x101 (0x0100 (PG_ADDEND), 0x0001(PG_DATA)) [...] PAGE HEADER: Page header for page 0x21509800 pageno=1397 nextpg=1398 prevpg=1396 objid=1977058079timestamp=0000 000132c3 nextrno=31 level=0 indid=0 freeoff=1970 minlen=42 page status bits: 0x101 (0x0100 (PG_ADDEND), 0x0001 (PG_DATA))

Le but est de déterminer si ces 2 pages sont correctes, si elles pointent sur le même objet : comparons donc scrupuleusement les objid et indid, si le chaînage est correct (ce qui est le cas dans cet exemple précis). Remarquons qu'il ne s'agit que d'un hasard si le chaînage semble se suivre (1394 -> 1395 -> 1396 -> 1397 -> 1398). Ceci étant fait, nous pourrons alors essayer de récupérer la majeure partie des données de la table en évitant la page incriminée:

-- le dernier chiffre détermine la direction: 0 = en arrière; 1 = en avant 
dbcc pglinkage (5, 1395,0, 1, 0, 0) 
dbcc pglinkage (5, 1397,0, 1, 0, 1)
go

Object ID for pages in this chain = 1977058079. Beginning of chain reached. Page : 896 Page : 895 Page : 894 Page : 893 Page : 892 Page : 891 Page : 890 Page : 889 Page : 888 Page : 855 Page : 854 Page : 853 Page : 852 Page : 851 Page : 850 Page : 849 513 pages scanned. Object ID = 1977058079. Last page in scan = 849. Object ID for pages in this chain = 1977058079. End of chain reached. Page : 1397 Page : 1398 Page : 1399 Page : 1400 Page : 1401 Page : 1402 Page : 1403 Page : 1404 Page : 1405 Page : 1406 Page : 1407 Page : 1408 Page : 1409 Page : 1410 Page : 1411 15 pages scanned. Object ID = 1977058079. Last page in scan = 1411.

Nous allons effectuer la récupération de la table à l'aide de la commande en ligne bcp, en y affectant le paramètre -b1 permettant de décharger la table ligne après ligne. Compte tenu de notre corruption, cette commande échouera lorsqu'elle atteindra la ligne fatidique.

bcp test..EMPLOYEES out Fichier1.bcp -c -b1 -Usa -Pxxx

Starting copy...
1000 rows successfully bulk-copied to host-file.
2000 rows successfully bulk-copied to host-file.
3000 rows successfully bulk-copied to host-file.
4000 rows successfully bulk-copied to host-file.
5000 rows successfully bulk-copied to host-file.
6000 rows successfully bulk-copied to host-file.
7000 rows successfully bulk-copied to host-file.
...
[crash]

Maintenant, nous déplaçons le curseur du début de la table sysindexes sur la page suivant la page corrompue. Attention ! N'oublions pas qu'il faut le faire en mode binaire:

Valeur décimale = 1397 Valeur hexadécimale = 0x575 Fichier = 1 Valeur à mettre dans sysindexes = 0x057500000100 Puis nous redémarrons le serveur afin de rafraîchir les données se trouvant dans la structure mémoire DBINFO. Il n'existe malheureusement aucune commande permettant de le faire sans redémarrer le serveur:

exec sp_configure "allow updates",1 
go
RECONFIGURE WITH OVERRIDE
go
update sysindexes set first = 0x057500000100
where indid = 0 
and id = 1977058079
go
shutdown
go

Nous effectuons ensuite un second bcp out. L'option -b1 n'est plus nécessaire. Il va nous générer un fichier contenant la fin de la table.

bcp wsb..EMPLOYEES out Fichier2.bcp -c -Usa -Pxxx -S MonSQLServeur

Starting copy...
1000 rows successfully bulk-copied to host-file.
1451 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (451000.00 rows per sec.)

Nous arrivons à la phase la moins agréable de l'opération: la récupération de la page corrompue. Nous allons éditer la page et exécuter les inserts à la main. Compte tenu du format retourné, il n'est pas toujours possible de le faire aisément. Si tel était le cas, essayez de récupérer suffisamment d'information afin de retrouver ces quelques enregistrements par un autre biais.

dbcc traceon(3604)
go
dbcc page (5, 1396,1,0)
go


Page read from disk. BUFFER: Buffer header for buffer 0x214C5000 (Mass head) page=0x214C5800 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000 bmass_next=0x00000000 bmass_prev=0x00000000 bvirtpg=83887476 bdbid=5 bmass_head=0x214C5000 bmass_tail=0x214C5000 bcache_desc=0x2022A360 bpool_desc=0x00000000 bdbtable=0x00000000 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=2048 (2K pool) bunref_cnt=0 bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED)) bbuf_stat=0x0 (0x00000000) Buffer blpageno=1396 bpg_size=2k Mass blpageno=1396(Buffer slot #: 0) bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000 Latch and the wait queue: Latch (address: 0x214C5020) latchmode: 0x0 (FREE_LATCH) latchowner: 0 latchnoofowners: 0 latchwaitq: 0x00000000 latchwaitqt: 0x00000000 Latch wait queue: PAGE HEADER: Page header for page 0x214C5800 pageno=1396 nextpg=1397 prevpg=1395 objid=1977058079 timestamp=0000000132a0 nextrno=31 level=0 indid=0 freeoff=1969 minlen=42 page status bits: 0x101 (0x0100 (PG_ADDEND), 0x0001 (PG_DATA)) DATA: Row-Offset table at the end of the row: (, , ) Offset 32 - row ID=0 row length=62 # varlen cols=2 214C5820 ( 0): 02000000 003e2143 656c6169 61202020 .....>!Celaia 214C5830 ( 16): 20202020 20202020 20202020 20202020 214C5840 ( 32): 20202020 20000000 01a33e00 46616269 .....>.Fabi 214C5850 ( 48): 656e3593 00004fd0 8a00033a 322c en5...O....:2, Row-Offset table for variable-length columns: [1, 44, 6] [2, 50, 8] Offset 94 - row ID=1 row length=63 # varlen cols=2 214C585E ( 0): 02010000 003e2243 656c6169 61202020 .....>"Celaia 214C586E ( 16): 20202020 20202020 20202020 20202020 214C587E ( 32): 20202020 20000000 01a33f00 44616e69 .....?.Dani 214C588E ( 48): 656c6535 9300004f d08a0003 3b332c ele5...O....;3, Row-Offset table for variable-length columns: [1, 44, 7] [2, 51, 8] [...] Offset 1844 - row ID=29 row length=63 # varlen cols=2 214C5F34 ( 0): 021d0000 003e3e43 656c6169 61202020 .....>>Celaia 214C5F44 ( 16): 20202020 20202020 20202020 20202020 214C5F54 ( 32): 20202020 20000000 01a33f00 44616e69 .....?.Dani 214C5F64 ( 48): 656c6535 9300004f d08a0003 3b332c ele5...O....;3, Row-Offset table for variable-length columns: [1, 44, 7] [2, 51, 8] Offset 1907 - row ID=30 row length=62 # varlen cols=2 214C5F73 ( 0): 021e0000 003e3f43 656c6169 61202020 .....>?Celaia 214C5F83 ( 16): 20202020 20202020 20202020 20202020 214C5F93 ( 32): 20202020 20000000 01a33e00 46616269 .....>.Fabi 214C5FA3 ( 48): 656e3593 00004fd0 8a00033a 322c en5...O....:2, Row-Offset table for variable-length columns: [1, 44, 6] [2, 50, 8] OFFSET TABLE: Row - Offset 30 (0x1e) - 1907 (0x0773), 29 (0x1d) - 1844 (0x0734), 28 (0x1c) - 1782 (0x06f6), 27 (0x1b) - 1719 (0x06b7), 26 (0x1a) - 1657 (0x0679), 25 (0x19) - 1594 (0x063a), 24 (0x18) - 1532 (0x05fc), 23 (0x17) - 1469 (0x05bd), 22 (0x16) - 1407 (0x057f), 21 (0x15) - 1344 (0x0540), 20 (0x14) - 1282 (0x0502), 19 (0x13) - 1219 (0x04c3), 18 (0x12) - 1157 (0x0485), 17 (0x11) - 1094 (0x0446), 16 (0x10) - 1032 (0x0408), 15 (0x0f) - 969 (0x03c9), 14 (0x0e) - 907 (0x038b), 13 (0x0d) - 844 (0x034c), 12 (0x0c) - 782 (0x030e), 11 (0x0b) - 719 (0x02cf), 10 (0x0a) - 657 (0x0291), 9 (0x09) - 594 (0x0252), 8 (0x08) - 532 (0x0214), 7 (0x07) - 469 (0x01d5), 6 (0x06) - 407 (0x0197), 5 (0x05) - 344 (0x0158), 4 (0x04) - 282 (0x011a), 3 (0x03) - 219 (0x00db), 2 (0x02) - 157 (0x009d), 1 (0x01) - 94 (0x005e), 0 (0x00) - 32 (0x0020),

Nous allons maintenant supprimer la table corrompue. Pour ce faire, il nous faut modifier à nouveau la table sysindexes afin de lui redonner ses valeurs originelles et ... redémarrer le serveur.

update sysindexes set first = 0x880000000100 where indid = 1  and id = 1977058079
go
shutdown
go

Supprimons ensuite la table en la tronquant avant tout. N'utilisez surtout pas la commande delete qui générerait à nouveau la corruption. Utilisez l'ordre truncate table.

truncate table EMPLOYEES
go
drop table EMPLOYEES
go

Microsoft ayant amputé le mirroring de devices de sa version 2000, iln'est plus possible de dépacer aisément une partie de base de données. Je ne saurai donc vous conseiller de migrer le plus rapidement possible tous les objets de cette base sur un autre fichier, ceci afin d'éviter de réécrire sur la partie du disque qui cause problème.

Recréons maintenant la table, mais sans les indexes, ceci afin d'autoriser le chargement rapide via bcp

Create table EMPLOYEES (...)

Chargement des 2 fichiers générés précédemment, puis insertion manuelle des données découvertes précédemment

bcp Northwind..EMPLOYEES in Fichier1.bcp -c -Usa -Pxxx -S MonSQLServeur
bcp Northwind..EMPLOYEES in Fichier2.bcp -c -Usa -Pxxx -S MonSQLServeur

Récréation des indexes manquants

Create unique clustered index on ADRESSE(ID)
Create nonclustered index on...
go

Sauvegarde de la base de données, après avoir testé son intégrité à l'aide des commandes usuelles dbcc checkdb, dbcc checkalloc, dbcc checkcatalog...

Conclusion

Si vous m'avez suivi jusqu'ici, vous aurez compris qu'une bonne stratégie de sauvegarde ou de réplication reste la méthode la plus simple et la plus rapide pour s'en sortir lors de corruptions.

J'ajouterai cependant qu'il existe certains programmes permettant d'aller encore plus loin dans la décorruption de bases en permettant l'édition et la modification des pages. Bien plus encore que la procédure que vous venez de suivre, ils demandent de la prudence et de l'expertise afin que le médicament ne devienne pas pire que le mal.

Historiquement, le traceflag -T 4011 permettait d'accéder à un menu caché qui permettait de le faire, mais il a été désactivé dans les versions actuelles.

Copyright © fadace. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Contacter le responsable de la rubrique SGBD & SQL