Developpez.com
Accueil
Quel DBA n’a jamais eu à faire face à un problème de performance provoqué par des blocage de plusieurs processus? En réalité, je suis sûr que non. Résoudre un problème de blocage de processus n’est pas toujours simple et peut exiger d’utiliser des outils qui simplifieront cette tâche. Il y a quelques mois, j’ai eu à gérer un tel scénario chez un de mes clients avec une cascade de processus bloqués dans une période donnée.
> Lire la suite (en anglais)
David Barbarin
MVP & MCM SQL Server
Lorsqu’on installe SQL Server, on a bien sûr à considérer comment placer les fichiers de bases de données. A ce stade, il est probable que vous suivrez les bonnes pratiques bien connues ou les guidelines fournis pour Microsoft mais êtes vous réellement au courant de ceux fournis par les vendeurs de stockage? Parlons en dans ce billet.
> Lire la suite (en anglais)
David Barbarin
MVP & MCM SQL Server
Il y a quelques jours, j’ai rencontré un problème intéressant chez un de mes clients concernant une opération de reconstruction d’index dans un environnement datawarehouse. Laissez moi vous présenter la situation: Un journée classique d’un administrateur de base de données avec une erreur presque habituelle dans la boîte email: “The transaction log for database ‘xxx’ is full”. Après avoir vérifié la base de données en question, nous avons remarqué que la taille du journal était de 60GB. Dans le même temps, nous avons identifié la source du problème qui concernait une reconstruction d’index effectuée dans la nuit via un plan de maintenance sur un index relativement important (environ 20GB) sur une table de fait.
> Pour lire la suite (en anglais)
David Barbarin
MVP & MCM SQL Server
During the night, you receive an alert concerning your SQL Server failover cluster or your availability groups. You’re in panic because the message displayed is « a failover has occured .. see the log for more details » …
Durant la nuit, vous recevez une alerte concernant un basculement d’une de vos instances SQL Server FCI ou de vos groupes de disponibilités. Mode panique !!! parce que le message qui vous arrive est le suivant : « a failover has occured .. see the log for more détails » … Bien sûr vous tentez de rester calme (après tout le basculement automatique est fait pour cela non ?) et après avoir été connecté à votre environnement vous ne trouvez rien … Que s’est-il passé ? Peut être qu’une intervention manuelle est à l’origine de cette alerte et vous n’êtes pas au courant.
Lire la suite (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Récemment j’ai rencontré un problème avec l’un de mes clients (oui je recontre beaucoup de problèmes à vrai dire …) qui m’expliquait que leur instance Server 2008 R2 avait une fuite de mémoire . Waaou une fuite mémoire! Le serveur posède 64 Go de RAM et l’instance de SQL Server concernée est limitée à 54 Go selon l’option de serveur de mémoire serveur max (GB ) . Cependant, il avait remarqué que SQL Server utilise 60 Go de mémoire, ce qui ne correspond pas à l’option serveur « max server memory (MB) ». Qu’Est-ce qui passe ici ? Je vous propose de voir cela dans le billet.
Lire la suite (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Au cours de mes audits clients, il arrive encore de trouver des bases de données avec une option de vérification de page configurée à NONE. J’ai toujours alerter mes clients sur ce type de configuration parce qu’elle peut avoir un impact certain sur l’intégrité des bases de données concernées. Un de mes clients me disait justement que de toute façon la vérification d’intégrité via les plans de maintenance pouvaient étaient faite pour cela mais est-ce vraiment le cas ?
Pour le savoir c’est par ici (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
C’est officiel je suis renouvelé pour la 5ème fois en tant que MVP SQL Server.
Enjoy !
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Transparent Data Encryption requières la création d’une clé de chiffrement de bases de données. Cette clé fait elle même partie d’une hiérarchie de chiffrement de clé utilisée par SQL Server. Cette hiérarchie offre une certaine souplesse dans la gestion des clés qui comprend la restriction des clés aux personnes autorisées, la sauvegarde des clés vers un stockage sécurisé et la rotation des clés de chiffrements pour éviter que celles-ci soient compromises par une personne malveillante. Comme vous pouvez l’imaginer un aspect important de cette gestion des clés est l’alignement de rotation des clés avec la stratégie de sauvegarde des bases de données.
>> Pour lire la suite
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Comme promis voici les slides de ma session sur les événements étendus. Malheureusement je n’ai pas pu effectuer toutes les démonstrations que je voulais .. eh oui il faut bien commencer par expliquer comment fonctionne les événements étendus et cela m’a pris un peu plus de temps que prévu 
. Mais ce n’est que partie remise, je garde cette partie pour une session uniquement orientée démonstration. J’espère pouvoir vous la présenter rapidement.
Merci aux personnes qui ont voulu assister à ma présentation et de assister de manière générale à ce premier SQL Saturdays Paris. Ce fut un bon moment d’échange et de rencontres !
David BARBARIN (Mikedavem)
MVP SQL Server
La vérification d’intégrité des bases de données s’effectue par la commande DBCC CHECKDB. Le résultat de cette commande est assez verbeuse par défaut et peut poser problème lorsque la vérification d’intégrité est utilisé dans des scripts T-SQL personnalisés de maintenance par exemple.
Posons un peu le contexte et le pourquoi de ce billet. En réalité j’ai eu l’occasion de travailler (comme beaucoup je pense) sur la création de plans de maintenance "custom". L’idée a été de construire un jeu de script permettant de pouvoir gérer l’ensemble des tâches que l’on peut avoir à utiliser pour la maintenance des bases de données. Une de ces tâches concerne la vérification d’intégrité des bases. Cette vérification se fait à l’aide de la commande bien connue DBCC CHECKDB. Dans notre contexte l’idée est de pouvoir exécuter une tâche de maintenance et d’enregistrer dans une table de log les données suivantes :
Ceci nous permet d’avoir une traçabilité des tâches de maintenance par la suite et pouvoir les consommer à des fins d’analyse.
Lorsque l’on scripte une vérification d’intégrité à l’aide de la commande DBCC CHECKDB on s’aperçoit rapidement que la gestion des erreurs liées à cette commande n’est pas triviale. C’est que nous verrons dans le cadre de ce billet.
Une sortie verbeuse
Voici la sortie d’une commande DBCC CHECKDB sur une base de données corrompue :
DBCC results for ‘DemoCorruptMetadata’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
DBCC results for ‘sys.sysrscols’.
There are 872 rows in 9 pages for object "sys.sysrscols".
DBCC results for ‘sys.sysrowsets’.
There are 124 rows in 1 pages for object "sys.sysrowsets".
DBCC results for ‘sys.sysclones’.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for ‘sys.sysallocunits’.
There are 138 rows in 2 pages for object "sys.sysallocunits".
DBCC results for ‘sys.sysfiles1′.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for ‘sys.sysseobjvalues’.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for ‘sys.syspriorities’.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for ‘sys.sysdbfrag’.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for ‘sys.sysfgfrag’.
There are 0 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for ‘sys.sysdbfiles’.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for ‘sys.syspru’.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for ‘sys.sysbrickfiles’.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for ‘sys.sysphfg’.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for ‘sys.sysprufiles’.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for ‘sys.sysftinds’.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for ‘sys.sysowners’.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for ‘sys.sysdbreg’.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for ‘sys.sysprivs’.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for ‘sys.sysschobjs’.
There are 2180 rows in 29 pages for object "sys.sysschobjs".
DBCC results for ‘sys.syscolpars’.
There are 698 rows in 11 pages for object "sys.syscolpars".
DBCC results for ‘sys.sysxlgns’.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for ‘sys.sysxsrvs’.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for ‘sys.sysnsobjs’.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for ‘sys.sysusermsgs’.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for ‘sys.syscerts’.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for ‘sys.sysrmtlgns’.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for ‘sys.syslnklgns’.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for ‘sys.sysxprops’.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for ‘sys.sysscalartypes’.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for ‘sys.systypedsubobjs’.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for ‘sys.sysidxstats’.
There are 130 rows in 1 pages for object "sys.sysidxstats".
DBCC results for ‘sys.sysiscols’.
There are 317 rows in 3 pages for object "sys.sysiscols".
DBCC results for ‘sys.sysendpts’.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for ‘sys.syswebmethods’.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for ‘sys.sysbinobjs’.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for ‘sys.sysaudacts’.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for ‘sys.sysobjvalues’.
There are 134 rows in 13 pages for object "sys.sysobjvalues".
DBCC results for ‘sys.syscscolsegments’.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for ‘sys.syscsdictionaries’.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for ‘sys.sysclsobjs’.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for ‘sys.sysrowsetrefs’.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for ‘sys.sysremsvcbinds’.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for ‘sys.sysxmitqueue’.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for ‘sys.sysrts’.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for ‘sys.sysconvgroup’.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for ‘sys.sysdesend’.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for ‘sys.sysdercv’.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for ‘sys.syssingleobjrefs’.
There are 156 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for ‘sys.sysmultiobjrefs’.
There are 107 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for ‘sys.sysguidrefs’.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for ‘sys.sysfoqueues’.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for ‘sys.syschildinsts’.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for ‘sys.syscompfragments’.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for ‘sys.sysftsemanticsdb’.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for ‘sys.sysftstops’.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for ‘sys.sysftproperties’.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for ‘sys.sysxmitbody’.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for ‘sys.sysfos’.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for ‘sys.sysqnames’.
There are 100 rows in 1 pages for object "sys.sysqnames".
DBCC results for ‘sys.sysxmlcomponent’.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for ‘sys.sysxmlfacet’.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for ‘sys.sysxmlplacement’.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for ‘sys.sysobjkeycrypts’.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for ‘sys.sysasymkeys’.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for ‘sys.syssqlguides’.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for ‘sys.sysbinsubobjs’.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for ‘sys.syssoftobjrefs’.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for ‘sys.queue_messages_1993058136′.
There are 0 rows in 0 pages for object "sys.queue_messages_1993058136".
DBCC results for ‘sys.queue_messages_2025058250′.
There are 0 rows in 0 pages for object "sys.queue_messages_2025058250".
DBCC results for ‘sys.queue_messages_2057058364′.
There are 0 rows in 0 pages for object "sys.queue_messages_2057058364".
DBCC results for ‘sys.filestream_tombstone_2089058478′.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2089058478".
DBCC results for ‘sys.syscommittab’.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for ‘sys.filetable_updates_2121058592′.
There are 0 rows in 0 pages for object "sys.filetable_updates_2121058592".
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
On se rend vite compte que la quantité d’information qui nous intéresse ici reste négligeable par rapport à la quantité totale d’information produite en sortie par notre commande. Ici la commande DBCC CHECKDB a produit 162 lignes d’information alors que moins de 10 lignes nous intéresse ici. Heureusement pour nous, nous pouvons jouer avec les options NO_INFOMSGS et ALL_ERRORMSGS pour limiter le nombre d’informations produites par DBCC CHECKDB. Pour rappel NO_INFOMSGS permet d’éliminer les messages d’information produites en sortie et ALL_ERRORMSGS permet d’afficher toutes les erreurs rencontrées lors d’une opération de vérification de bases de données. Par défaut seulement les 1000 premiers messages d’erreur sont affichés depuis SQL Server Management Studio.
Utilisation des options NO_INFOMSGS, ALL_ERRORMSGS
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
On a ici bien réduit le nombre de lignes produites par notre commande DBCC CHECKDB avec l’affichage du détail des erreurs rencontrées et 2 messages d’information qui ne sont ni plus ni moins qu’un récapitulatif de ces erreurs.
Gestion de la sortie de la commande DBCC CHECKDB dans un script T-SQL
Après avoir limité le nombre d’information utiles comment nous en servir pour construire une logique de maintenance par script ? L’idée ici est simplement de pouvoir détecter un problème d’intégrité et de prévenir les personnes concernées (notamment l’équipe DBA) qu’un problème est survenu à ce moment là
A priori 2 voies d’exploitation évidentes peuvent être prises en compte :
Avec la variable @@ERROR :
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
–> error during dbcc checkdb
La variable @@ERROR nous permet effectivement de pouvoir détecter les erreurs engendrées par la commande DBCC CHECKDB. Il suffit ensuite de coder la logique de maintenance qui nous intéresse. Cependant si on utilise notre script T-SQL dans un job SQL Server, les erreurs produites impliqueront une étape de job en échec. La question est donc de savoir ce résultat est attendu ou non ? Effectivement ce n’est pas parce qu’une commande DBCC CHECKDB produit une erreur qu’un job SQL Server doit forcément partir en échec. En réalité tout dépendra de la logique d’exécution globale concernée par ce script. On peut tout à fait traiter l’erreur de job et effectuer une surveillance sur l’état de ce job par envoi d’alerte ou via des outils tiers par exemple. On peut aussi vouloir enregistrer l’erreur sans que le job parte en échec, avec l’exécution d’étapes supplémentaires sans pour autant avoir une logique d’exécution complexe du style "si étape 1 échec –> aller à l’étape 3 sinon aller à l’étape 2 etc … A mon humble avis les jobs SQL Server ne sont pas vraiment désignés pour gérer des logiques complexes de workflow . Dans le cadre de notre script nous avons retenu une solution générique et simple : si une tâche génère une erreur alors celle-ci est enregistrée dans une table de log et l’information pourra être envoyé à posteriori par email. Le choix est laissé à l’utilisateur final.
Avec le bloc de gestion d’erreur BEGIN TRY, CATCH :
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
Comme on peut le remarquer le bloc de gestion TRY , CATCH ne va malheureusement pas nous être d’une grande utilité ici car les dernières lignes d’information produites par la commande DBCC CHECKDB empêchent notre bloc de gestion d’erreur de détecter les erreurs produites par notre commande DBCC CHECKDB quelques lignes plus haut.
Avons nous une autre alternative dans ce cas ? La réponse est oui. Il est également possible d’enregistrer les erreurs produites lors d’une vérification d’intégrité dans une table SQL en utilisant l’option TABLERESULTS de cette manière :
Utilisation de l’option TABLERESULTS
Il suffit ici d’extraire les informations produites dans la table et de gérer sa propre logique de maintenance. Dans mon cas je remplis une variable de table avec le résultat de la commande DBCC CHECKDB et l’option TABLERESULETS et je vérifie qu’au moins une ligne existe pour détecter la présence d’une erreur (la commande DBCC CHECKDB ne produit aucune ligne d’erreur si aucun problème d’intégrité n’est détecté). L’information est ensuite enregistrée dans une table de log avec le numéro d’erreur et sera envoyé par mail à postériori.
L’option TABLERESULTS est-elle là solution miracle ? Malheureusement non car la commande DBCC CHECKDB peut produire certaines erreurs propres à son exécution et qui stopper franchement le script en cours d’exécution. C’est le cas de certaines erreurs sévères rencontrés par la commande DBCC CHECKDB.
Gestion des erreurs liées à l’exécution même de la commande DBCC CHECKDB
Voici quelques cas où l’exécution de la commande DBCC CHECKDB provoquera une erreur grave et un arrêt net du script T-SQL en cours d’exécution.
- Après avoir corrompu la page PFS (page id = 1) à l’aide d’un éditeur hexadécimal de ma base DemoCorruptMetada le script SQL se stoppera net avec le message d’erreur suivant :
Msg 0, Level 11, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded
- De la même manière Après avoir supprimer une ligne dans la table système sys.syscolpars en redémarrant l’instance SQL Server en mode mono utilisateur et se connectant en mode administrateur dédié la commande DBCC CHECKDB génère le message suivant :
Msg 0, Level 20, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded
Pour une description des niveaux d’erreurs voir la documentation en ligne SQL Server (section "Understanding DBCC errors messages"). Dans ce cas il faudra effectivement gérer l’erreur de manière différente. Dans mon cas au début de chaque tâche une ligne est enregistrée dans une table de log. Si le script T-SQL est arrêté a date de fin de la tâche ne sera pas renseigné. Un email est envoyé pour les cas suivants :
Bonne gestion d’erreur !!
David BARBARIN (Mikedavem)
MVP SQL Server
© 2000-2020 - www.developpez.com
Partenaire : Hbergement Web
