lundi 2 juillet 2012

La page Web Microsoft SQL Server

Bonjour,

Toujours utile, le lien web de la page Microsoft sur l'outil SQL Server.
http://technet.microsoft.com/fr-fr/sqlserver


Bonne Lecture
Thierry

Identifier la version et l'édition SQL Server

Déterminer la version SQL Server 2008. Nous allons exécuter une requête SQL.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Voici le résultat :
  • La version du produit (par exemple, 10.0.1600.22)
  • Le niveau du produit (par exemple, RTM)
  • L'édition (par exemple, Enterprise)

  • Bonne Lecture
    Thierry

    mardi 15 mai 2012

    Les deadlocks !

    Pourquoi cette version de SQL Server ? Parce qu'elle voit arriver une nouvelle fonctionnalité appelé XEvents. C'est une fonctionnalité très avancée qui à terme remplacera le système de Trace de SQL Server (SQLtrace utilisé par le Profiler).
    Ce composant à l'avantage d'être léger et d'avoir un faible impact sur le moteur de base de données. Et c'est d'ailleurs pourquoi Microsoft a activé par défaut une session XEvents sur toutes les instances SQL Server 2008 et +. Cette session est connu sous le nom de system_health :
    SELECT name FROMsys.dm_xe_sessions
    Et les évènements qui y sont trace sont les suivant :
    • Toutes les erreurs de sévérité supérieure ou égale à 20 (erreurs critiques)
    • Toutes les erreurs relatives à la mémoire
    • Problème de processus ne cédant pas son tour (non yielding scheduler)
    • Interblocages / Deadlocks
    • Attente sur un latch pour plus de 15 secondes
    • Attente sur un verrou (lock) pour plus de 30 secondes
    • Attentes significatives sur d'autres types
    Avec l'avantage pour la plupart de ces évènements de tracer la/les commande(s) SQL impliquée(s) et la pile d'appel le cas échéant.
    Pour consulter le contenu du trace, c'est facile, utilisez la requête suivante :
    SELECTCAST(xet.target_data asxml) AS xe_content
    FROMsys.dm_xe_session_targetsAS xet
    JOINsys.dm_xe_sessionsAS xes ON xes.address = xet.event_session_address
    WHERE xes.name ='system_health'
    Si vous êtes intéressé spécifiquement par les deadlocks, vous n'avez plus qu'à utiliser la requête XQuery ci-dessous :
    '/RingBufferTarget/event [@name="xml_deadlock_report"]'

    Le buffer qui contient le trace est vidé en fonction de son usage, donc si votre serveur est très sujet à des erreurs critiques et des deadlocks les infos seront peut-être non présente.

    Bonne Lecture
    Thierry

    Mare !!! que le fichier de LOG (.LDF) grossisse. Quelle solution ?

    Qu'est-ce que le journal de transactions  ?

    Il est souvent confondu avec le fichier LDF de la base de données. En fait, le journal de transactions n'est pas un seul fichier, mais peut être composé de plusieurs fichiers LDF (extension par défaut du fichier). Mais il est vrai que dans l'énorme majorité des cas, seul 1 fichier LDF est présent dans une base de données, et c'est lui seul qui représente le journal (voir ici pour l'utilisation de plusieurs fichiers :
    Pour connaître le ou les fichiers du journal de transactions dans la base de données courante, utilisez le script suivant.
    select*fromsys.master_files
    wheretype= 1 and database_id =DB_ID()


    Ne pas confondre ces fichiers multiples avec les VLFs (Virtual Log Files) qui contrairement à leur nom ne sont pas des fichiers, mais des blocs qui composent le fichier LDF.
    Le journal de transactions contient l'ensemble des transactions exécutées sur la base de données courante. Pour simplifier imaginez que toutes les requêtes réalisant des écritures sont stockées dans ce journal avant même que les données soient inscrites sur le disque. Un certain nombre d'opérations systèmes génère aussi des écritures dans le journal.
    La finalité de ce journal est double. Il permet de maintenir les données dans le cache mémoire de SQL Server, le plus longtemps possible sans avoir à les écrire dans les fichiers de données. Mais aussi de permettre la récupération de la base de données en cas d'arrêt non prévu. En effet les données restant longtemps dans le cache, sans journal traçant les opérations d'écritures la base de données serait corrompu en cas de redémarrage du serveur. Il sert donc à améliorer les performances en limitant les écritures sur le disque, mais aussi permet de remettre la base de données dans un état cohérent en cas de redémarrage (et aussi en cas de restauration d'une base de données).
    Sans journal, le risque de corruption de la base de données est énorme et le serveur risque de ne pas tolérer le moindre arrêt sans pouvoir vider son cache vers le disque, ce qui est clairement aberrant dans un environnement de production.

    Pourquoi le journal de transactions grossi-t-il ?

    Toutes les modifications effectuées sur la base de données génèrent des transactions et font donc grossir les fichiers du journal de transactions. Il est donc normal que ce fichier grossisse au fil de l'activité de la base de données.
    Seulement sans aucunes actions de la part de l'administrateur de base de données, le journal grossira à l'infinie et cela sans aucuns avantages. En effet périodiquement les données présentes dans le cache sont inscrites sur le disque l'intérêt de la récupération des données est donc nul pour des transactions anciennes. Par contre, en cas de perte des disques où sont situées les données, ces anciennes transactions peuvent se révéler précieuses.
    C'est là que rentre en ligne de compte, la sauvegarde du journal de transactions. En effet comme indiqué ci-dessus les informations du journal de transactions anciennes ne servent pas au moteur pour redémarrer le serveur et remonter une base de données, mais peuvent être intéressante en cas de perte de fichier, et par extension en cas de perte disque ou de crash serveur sévère. Sans les transactions seules les sauvegardes complètes sont disponibles, or la restauration à un point fixe dans le temps vous expose à une perte systématique de données.
    Donc, utiliser des morceaux du journal de transactions et les combiner avec les sauvegardes complètes, permet de remonter la base de données à n'importe quel point dans le temps y compris le moment exact du crash serveur. De plus, une fois la sauvegarde du journal de transactions réalisée, la portion copié vers le fichier de sauvegarde, n'est plus utile dans le journal et peut être supprimée (à quelques conditions détaillées plus loin).
    Sauvegarder régulièrement le journal de transactions, permet de libérer de l'espace à l'intérieur des fichiers LDF. Cela ne changera rien à la taille des fichiers en eux même (attention à ne pas utiliser des options tel que Auto Shrink, voir plus loin), mais permet la réutilisation de l'espace interne des fichiers LDF.
    La commande DBCC SQLPERF(LOGSPACE) renvoie des informations sur la taille du journal de transaction et le pourcentage d'occupation de ce dernier. Cela peut se révéler très utile pour savoir où en est l'occupation du ou des fichiers. Attention cette commande requiert des privilèges sysadmin sur l'instance où elle est exécutée.



    Database nameLog Size (MB)Log Space Used (%)Status
    Master1.24218836.792450
    Tempdb0.492187575.892860
    Model0.742187544.210530
    Msdb8.17968816.09360

    Il est important de créer des fichiers LDF avec une taille correcte dès le départ. L'avantage est multiple, d'une part on évite de tomber sur trop d'opérations d'incrément de fichier (quand le fichier est plein), d'autre part la fréquence de sauvegarde du journal de transactions peut être réduite (passer de toutes les 15 minutes à toutes les 30 minutes par exemple) et finalement, en termes de performance, on diminue la fragmentation interne des fichiers, le traitement des transactions est plus rapide.
    On utilise souvent, comme valeur arbitraire de départ, le chiffre de 20%. C'est-à-dire que l'on met 20% de la taille estimée des données, comme taille de départ pour le journal de transactions. Cette valeur est une estimation qui variera grandement avec les différents modes de récupération et l'usage de la base de données. En cas d'écritures intensives, cette valeur peut avoisiner les 30 à 40% facilement. Dans des scénarios de chargement de données de Datawarehouse, y compris avec une base de données en mode de récupération simple, la taille du journal de transactions sera très importante.

    Comment diminuer la taille du ou des fichier(s) LDF ?

    Le seul moyen est de vider le journal est de sauvegarder ce dernier au travers de la commande suivante.
    -- Sauvegarde du journal de transaction de la base de données courante
    -- Par convention on donne l'extension de fichier TRN à ce type de sauvegarde

    BACKUP
    LOG MaBaseDeDonnees

    TODISK='c:\monrepertoire\monfichier.trn'

    A ce moment-là, une partie du journal de transactions est vidé. La plupart du temps, exécuter cette commande régulièrement suffira à maintenir la taille des fichiers du journal de transactions à une taille constante.
    Cependant, un certains nombres de situations risques de nécessiter de manuellement réduire la taille du ou des fichier(s) LDF.
    Parmi les raisons possibles :
    • Chargement de données, ayant augmenté la taille du journal plus que nécessaire
    • Opérations de maintenance d'index, ayant augmenté la taille du journal plus que nécessaire
    • Pas de sauvegarde du journal de transactions sur un environnement de développement ou de pré-production
    Dans tous les cas, cherchez la cause du problème et ne voyez pas le fait de tronquer le journal et de réduire la taille des fichiers comme la solution.
    Parmi les solutions possibles :
    • Augmenter la fréquence de sauvegarde du journal de transactions.
    • Manuellement exécuter une sauvegarde du journal lors d'étapes clef de maintenance de base de données ou de chargement.
    • Changer le mode récupération temporairement lors d'étapes clef de maintenance de base de données ou de chargement.
    • Vérifiez qu'un processus, tel que la réplication transactionnelle, le Database Mirroring ne bloquent pas la vidange du journal de transactions.
    Une fois la sauvegarde effectuée, il nous reste à réduire la taille du ou des fichiers grâce à la commande DBCC SHRINKFILE. J'insiste bien sur le fait que c'est SHRINKFILE et non SHRINKDATABASE qui est à utiliser. Le second fait réduire la taille de tous les fichiers et pas uniquement le contenu des fichiers du journal de transactions. C'est non seulement inutile et long, mais cela engendre de la fragmentation dans les données, ce qui est un effet de bord inadmissible pour les performances.
    De plus, ne passez jamais une base de données en AUTO_SHRINK, faute de quoi vous risquez de graves problèmes de performances, pour toutes les raisons évoqué ci-dessus. Vos fichiers de base de données risquent de passer le temps à jouer au yoyo en termes de taille !
    Le SHRINKFILE a de très fortes chances d'échouer à la première exécution. C'est pourquoi je vous conseille ce script.
    -- Vide le début du journal
    BACKUPLOG MaBase TODISK='C:\...'

    -- Tente de convaincre le moteur d'utiliser
    -- le début du journal de transactions
    CHECKPOINT

    -- Si la portition active n'est plus à la fin
    -- du journal de transactions, vide cette partie
    BACKUPLOG MaBase TODISK='C:\...'

    -- Le Shrink est maintenant possible
    DBCC SHRINKFILE(2, 10, TRUNCATEONLY)

    -- En cas d'échec retentez la liste ci-dessus

    Le script force le recyclage interne des portions du journal de transactions. La portion, dite active, du journal de transactions doit se déplacer au début du fichier grâce à la commande CHECKPOINT. La sauvegarde du journal de transactions peut alors vider la fin du fichier, et le SHRINKFILE libérer l'espace libre à la fin de ce dernier.
    Au niveau de la syntaxe de SHRINKFILE, le premier argument est le numéro du fichier à réduire (vous l'obtenez avec la requête du début de cet article). Le deuxième argument est la taille cible en Méga Octets. Le 3ème est optionnel dans le cas du journal de transactions, il permet d'indique que l'on souhaite uniquement libérer l'espace disque, sans réarranger le contenu interne du fichier.
    Sachez que ce script n'aura pas toujours d'effet, quelques causes possibles :
    • La partie active du journal se trouve actuellement à la fin du fichier du journal de transactions
    • Une sauvegarde du journal de transactions ou de données est actuellement en cours d'exécution
    • Une transaction longue est en cours d'exécution
    • Une réplication transactionnelle existe et les transactions associées n'ont pas encore été envoyées au distributeur
    • Un Database Mirroring est en place sur cette base de données et une ou plusieurs n'ont pas été inscrites dans le journal de transactions du miroir
    • Le Change Data Capture a été mis en place et des transactions n'ont pas encore été traitées par celui-ci.
    De plus, jamais la totalité du journal n'est jamais vidé, et donc la taille du fichier n'atteindra jamais 0 octets après un DBCC SHRINKFILE. Attention aussi à ne pas trop diminuer la taille du fichier, Je vous conseille de mettre une taille cible correcte en argument de cette commande pour éviter, à nouveau, des incréments sur les fichiers LDF, qui se révèlent très couteux.

    Changer le mode de récupération de la base de données ?

    Autre solution, passez votre base de données en mode de récupération simple ou journalisé en bloc. En effet en procédant de cette manière, le volume de transactions enregistré dans le journal diminuera fortement pour certaines opérations.
    C'est particulièrement intéressant pour les opérations de maintenance d'index ou de création d'index, les chargements de données (BULK INSERT / SELECT INTO / INSERT avec TF610) et les opérations de traitement de données volumineuses (plus de 8ko binaires ou texte). Celles-ci se retrouvent faiblement journalisées et écrivent beaucoup moins d'information dans le journal de transactions.
    Une exception cependant, la maintenance d'index en ligne, qui s'exécute toujours en journalisation complète, quel que soit le mode de récupération de la base de données.
    Il existe 3 modes de récupération : simple, journalisé en bloc (BULK LOGGED) et complet… Plus de détails sur ces derniers : xxxxx
    Un autre avantage non négligeable du mode de récupération Simple en plus de la journalisation simple de certaines opérations, est le fait qu'il est tronqué automatiquement. Dès que le moteur réalise un CHECKPOINT, le journal est vidé de sa portion inactive. Plus besoin de réaliser de sauvegarde du journal de transaction dans ce mode.
    -- Modifie le mode de récupération de la base de données
    -- Dans ce mode les BACKUP LOG ne peuvent se faire
    -- Le journal est tronqué automatiquement mais peut quand même grossir
    ALTERDATABASE MaBaseDeDonnees SET RECOVERY SIMPLE

    En contrepartie il est impossible de restaurer la base de données autrement que par une sauvegarde compète ou différentielle. On ne peut pas profiter du journal pour récupérer une base de données, avec un RESTORE LOG. Pas de restauration fine dans le temps, pas de restauration jusqu'à la période du crash serveur en cas panne sévère.

    Tronquer le journal sans le sauvegarder

    Il était possible jusqu'à SQL Server 2008 de tronquer le journal de transactions. C'est-à-dire de le vider sans sauvegarder son contenu. Cette option est assez dangereuse et je ne saurais que la déconseiller, particulièrement sur un environnement de production.
    Dans certains cas particulier, elle est néanmoins nécessaire (sur des environnements de développement par exemple). Elle doit être impérativement suivie d'une sauvegarde complète, sinon votre base de données restera implicitement en mode de récupération simple (quel que soit la méthode utilisée ci-dessous).
    Sous SQL Server 2000 ou 2005, vous pouvez utiliser les commandes suivantes.

    -- Ces 2 méthode ne sont plus supportées
    -- depuis SQL Server 2008
    BACKUPLOG MaBase WITH NO_LOG
    BACKUPLOG MaBase WITH TRUNCATE_ONLY
    Cependant je vous conseille la méthode suivante qui fonctionne sur toutes les versions du moteur de base de données de 2000 à 2008 R2.
    -- Remplace NO_LOG et TRUNCATE_ONLY
    -- Passe en mode simple --> réalise un TRUNCATE jusqu'au point de contrôle
    ALTERDATABASE MaBaseDeDonnees SET RECOVERY SIMPLE
    -- Repasse en mode complet
    ALTERDATABASE MaBaseDeD
    onnees SET RECOVERY FULL

    Vous pouvez faire de même avec le mode journalisé en bloc, en remplaçant FULL par BULK LOGGED. Après ces commandes, exécutez la sauvegarde complète pour rétablir le mode de récupération. La base de données reste en mode de récupération simple tant que la sauvegarde n'est pas exécutée, même si les paramètres de base de données prétendent le contraire.

    -- Impérativement faire une sauvegarde complète après
    BACKUPDATABASE MaBaseDeDonnees TODISK='monFichier.BAK'

    Une alternative existe pour sauvegarder le journal de transactions sans conserver les fichiers de sauvegarde : http://blogs.codes-sources.com/christian/archive/2008/05/06/sql-server-envoyer-une-sauvegarde-vers-le-p-riph-rique-nul.aspx
    Je conseille cette technique sur les serveurs de développement, là où la base de données doit conserver le même mode de récupération qu'en production sans d'embarrasser avec les sauvegardes.

    En conclusion

    • Sauvegardez régulièrement le journal de transaction
    • Sinon pensez à passer en mode de récupération simple
    • Ne réduisez la taille que des fichiers LDF, via SHRINKFILE, pour le remettre à une taille initiale correcte
    • N'utilisez jamais de SHRINKDATABASE
    • N'utilisez jamais l'option AUTO_SHRINK sur une base de données
     Bonne Lecture
    Thierry

    Les certifications Microsoft SQL 2012

    la certification en version Beta sous le numéro 71 et pas 70, pour passer l'examen 70-461 en Beta cherchez le 71-461.

    ExamenDate de disponibilitéPromo Code
    Exam 70-461: Querying Microsoft SQL Server 2012Du 29.03.2012 au 12.04.2012DEN461
    Exam 70-462: Administering Microsoft SQL Server 2012 DatabasesDu 29.03.2012 au 12.04.2012DEN462
    Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012Du 23.03.2012 au 6.04.2012DEN463
    Exam 70-464: Developing Microsoft SQL Server 2012 DatabasesDu 30.03.2012 au 13.04.2012DEN464
    Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012Du 30.03.2012 au 13.04.2012DEN465
    Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012Du 23.03.2012 au 6.04.2012DEN466
    Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012Du 23.03.2012 au 6.04.2012DEN467

    Bonne Lecture et Good Luck
    Thierry

    mercredi 9 mai 2012

    Le Rebuild Database sous SQL Server 2008

    Bonjour, voici de quelle manière vous pouvez lancer le rebuild si vous avez une grosse problèmatique sur les bases systèmes en autre la (master)

    setup.exe /QUIET
    /ACTION=REBUILDDATABASE
    /INSTANCENAME=[Nom de l'instance]
    /SQLSYSADMINACCOUNTS= [Nom du server\Compte Windows]
    /SAPWD=[Saisir le mot de passe SQL Server]
    /SQLCOLLATION=French_CI_AS


    Bonne Lecture
    Thierry

    Erreur, SQLAgent et DatabaseMail & SQLMail >= SQL Server 2005

    Bonjour, Si vous souhaitez modifier les paramètres de l'agent SQL, comme le système d'alertes / Activer le profil de messagerie. Vous allez le modifier, re-démarrer l'instance de l'agent et vous contastez que le profil de messagerie est resté avec le compte SQLMail !!! Erreur : La solution est de lancer le script suivant :


    *************************************************
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE
    GO

    sp_configure 'SQL Mail XPs', 0
    GO
    sp_configure 'Database Mail XPs', 1
    GO
    RECONFIGURE;
    GO

    EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    , N'UseDatabaseMail'
    , N'REG_DWORD'
    , 1
    GO

    EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    -- ATTENTION, Saisir votre porfil Name du DatabaseMail
    , N'ADMINSQL'
    , N'REG_SZ'
    , N'SQLMail Profile'
    GO

    Par précaution, vaut mieux re-démarrer l'agent SQL de votre instance !
    Aussi valider le changement avec : Exec master.dbo.xp_intance_regread

    Bonne Lecture Thierry