Discussion :

[minnemo]

Bonjour à tous,

Novice en matière de SSE 2005 j'aurais quelques petites questions qui cherchent réponses :
J'administre une base en recovery model FULL. Un backup full de cette base est effectué chaque semaine, une diff chaque soir de la semaine, et des journaux de transactions chaque 15 minutes.
Nous avons mis en place récemment deux plans de maintenance SQL qui tournent de 23h à 3h la nuit et qui amènent à la création d'importants fichiers .trn (de l'ordre d'une dizaine de Go pour certains). Suite à cela nous craignons une saturation disque.
J'aimerais savoir s'il est possible de modifier le plan de maintenance lié à la création des journaux de transaction chaque 15 minutes, pour l'interrompre entre cette tranche horaire ou le logging est important?
Mes craintes/questions:
- Le fichier .trn qui se créera à 3h15 du matin sera t-il lui aussi énorme ?
- quel sera l'impact de ce changement au niveau d'une restauration de la base ?

Merci pour vos futures réponses .

[SQLpro]

- Le fichier .trn qui se créera à 3h15 du matin sera t-il lui aussi énorme ?

Oui, le volume transactionnel sera globalement le même

- quel sera l'impact de ce changement au niveau d'une restauration de la base ?

Bonne question.
Tout dépend de ce que vous faites à ces heures creuses.

Si peu de personnes sont connectées et que des opérations reproductibles y sont faites, vous pouvez passez temporairement en mode BULK LOGGED.
Si rare sont les personnes à travailler et encore plus rare les mises à jour sensibles, vous pouvez passer temporairement en mode simple.

Bref, sans connaître la charge et le fonctionnel au fil des heures difficile de vous répondre.

A +

[minnemo]

Merci pour ta réponse.

On m'a dit qu'il serait peut-être possible d'empêcher un plan de maintenance de logguer des informations dans le fichier log. Penses-tu que celà soit possible ?

[SQLpro]

Tout dépend de ce que vous appelez le fichier de log. La confusion est aisé. Appelons un chat un chat :

• le terme fichier de log désigne généralement les journaux d'événement de Windows, (applicatif, sécurité, os...). Ce sont des informations qui y sont écrite au fil de l'eau et qui n'ont d'autre but que de renseigner sur ce qui s'est passé...
• le terme journal de transaction est le terme consacré pour enregistrer les transactions SQL, les images avant et après mise à jours des tables, et consigner la finalisation de la transaction.

Toutes les opérations de manipulation de données dans la base de données s'effectuent dans une transaction et toutes les écritures sont consignées dans le journal de transaction. Par principe, il est donc impossible de manipuler des données sans les journaliser...

Pour ce qui concerne les plans de maintenance, et la maintenance en général :
Si l'opération touche directement les données, par exemple une réindexation, alors elle sera transactionnée donc écrite dans le journal des transactions.
Si l'opération ne concerne pas les données (par exemple une sauvegarde) alors il n'y aura pas de consignation dans le journal de transaction car il n'y a pas
de transaction.
Dans les deux cas, il est possible dans le plan de maintenance, et dans la maintenance en général lorsque qu'elle s'effectue à travers l'agent SQL, de consigner les opérations réussies, échouées ou les deux dans un des journaux d'événements de l'OS comme dans les tables d'historisation de la base de données MSDB. Mais c'est vous qui choisissez !

A l'avenir merci de na pas entretenir la confusion et donc évitez de parler de fichier de log ce qui ne veut rien dire !

A +

[Mothership]

Blam blam comme on dit

Une réponse limpide.

[minnemo]

Merci désolé pour la confusion..

Encore une autre question, et pardonnez-moi si je dois vous offusquer une seconde fois :
Il arrive que je doive réaliser de temps un temps un shrink manuel du fichier .LDF à partir de l'interface graphique de SQL Server Management Studio. La procédure que je possède actuellement est le passage du recovery model de la base en "simple" (il est normalement toujours en "full"), puis la réalisation du shrink. Cependant après cette opération, mon plan de maintenance de création d'un journal de transaction (lancé toutes les 15 min) tombe en erreur jusqu'au dump différentiel de la base le soir.
Existe il un moyen de réaliser ce shrink sans faire tomber en erreurs les trn ?

Merci pour la réponse et désolé encore si ma question frôle l'hérésie

[mikedavem]

Bonjour,

Evitez de faire des shrink sur vos fichiers. Cela a pour conséquence une bonne fragmentation.. Si vous faites une opération SHRINK à chaque fois alors que votre journal grossit , il faut peut être dimensionner correctement votre fichier ldf (Nb de transactions que dois accueillir le journal pour une période donnée, la fréquence des sauvegardes de log ...) pour éviter cette opération...

Cependant vous n'avez peut être pas le choix

Si vous passez en mode Recovery Simple alors que vous étiez en FULL vous tronquez effectivement le journal des transactions mais vous ne pouvez plus profiter des vos sauvegardes de journaux... jusqu'à la prochaine sauvegarde de votre base.

Pensez donc à repasser en mode recovery "FULL" et faire une sauvegarde de votre base immédiatement pour éviter votre erreur de transactions via votre plan de maintenance.

++

[SQLpro]

Premièrement évitons les abus de langage... le terme dump n'a rien à voir avec la sauvegarde d'une base de données. Certains SGBDR qui sont encore dans l'incapacité matérielle de générer de véritables sauvegardes (comme MySQL par exemple) vous propose pour pallier leur défaut, une commande dump qui n'a rien à voir avec une sauvegarde.
Une sauvegarde est un mécanisme de copie binaire des données d'une base qui capture les pages de données de la base ainsi que l'ensemble des transactions effectuées depuis le démarrage de la base et ce jusqu'au moment ou la sauvegarde s'arrête. Ceci est la seule méthode connue pour obtenir une sauvegarde cohérente de la base de données, alors que la base continue d'être exploitée.
En revanche un dump est étymologiquement un déversement de données (qui peuvent être incohérente) d'un espace à un autre (par exemple mémoire vers affichage), pouvant produit un fichier. Pour une base de données et contrairement à une sauvegarde un dump n'assure aucune cohérence, ou s'il le permet c'est en bloquant les accès en écritures afin de se prémunir des données non intègres qui pourrait résulter du séquencement de ce vidage (par exemple l'écriture dans la fichier des données des factures avant celle des clients alors qu'entre temps un nouveau client avec une nouvelle facture est inséré).

Donc ne parlez plus de DUMP ce terme n'a pas cours dans les SGBDR. En revanche vous pouvez parler de :

• sauvegardes
• script SQL de rétro ingéniérie du schéma de la base
• script SQL de chargement de données

Pour répondre à la problématique de purge de vos fichiers du JT, sachez simplement qu'il suffit de sauvegarder la journal de transaction (BACKUP LOG) pour préparer son vidage. Si vous ne le faites pas, le journal sera en croissance éternelle dans le cas ou votre mode de journalisation est FULL ou BULK LOGGED, ce qui vous permet de vous récupérer d'une erreur fonctionnelle (par exemple la suppression intempestive de lignes d'une table). En revanche si votre mode de journalisation est SIMPLE, chaque transaction achevée constitue un espace mort recyclable dans le journal.

Maintenant la question à vous poser est la suivante :
pourquoi journalisez vous vos données en mode FULL si vous ne faites jamais de sauvegardes du journal de transaction ? Ceci est bien évidemment incohérent !
Soit vous êtes intéressé à permettre de récupérer d'une erreur fonctionnelle, et donc que vous utilisez le mode FULL, définissez un plan de sauvegarde intégrant régulièrement des sauvegardes du JT (par exemple toutes les heures).
Si vous n'êtes pas intéressé par cette possibilité dans ce cas journalisez en mode SIMPLE.
Dans les deux cas, votre journal sera automatiquement purgé des transactions obsolètes et vous en maîtriserez parfaitement la croissance.

En cas d'urgence, plutôt que de repasser au mode SIMPLE de journalisation, ce qui est un aberration comme vous l'avez constaté, il suffit de faire un BACKUP LOG ... WITH TRUNCATE ONLY qui a pour effet de vider le journal sans produire de fichier de sauvegarde...
Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/sqlserver/log/

Enfin, pour votre gouverne, vous auriez intérêt comme le suggère elsuket, de dimensionner largement tous vos fichiers de base (données et JT) en taille pré établie pour une utilisation à terme (par exemple 3 ou 5 ans d'exploitation) afin de ne plus jamais avoir aucune opération ni de croissance ni de décroissance. En effet ces opérations sont très hautement impactantes sur le plan des performances. Pour vous en convaincre, lisez l'article que j'ai écrit à ce sujet et faite donc le test associé : http://blog.developpez.com/sqlpro?ti..._fichiers_et_t

A +

[SQLpro]

Au moins tu m'a obligé à rajouter une entrée dans mon blog...
http://blog.developpez.com/sqlpro

A +

[minnemo]

Au moins tu m'a obligé à rajouter une entrée dans mon blog...
http://blog.developpez.com/sqlpro

A +

J'aurais au moins servis à quelque chose alors

En fait, j'exploite la base de données (en réalité il y en a plusieurs sur différents serveurs) en tant qu'infogéreur. Je ne peut rien entreprendre, ni changer quoique ce soit sans l'avis de mon client.
La base est en effet en mode FULL car nous avons une obligation contractuelle de restauration d'une base de donnée à H-2 en cas d'erreur fonctionnelle.
Pour cela nous générons toutes les 15 minutes un journal de transaction via un plan de maintenance sous Sql Server.
La génération d'une sauvegarde (full ou diff) en pleine journée est également impossible, ou alors très contraignante (une plateforme de PRA étant alimenté chaque jour par les différentes sauvegardes créées, et gérée sur un autre site par une autre société).

[minnemo]

Petit aparté sur ton blog :

Pire ! C'est possible...
Obnubilé par la taille des fichiers d'une base de données, certains développeurs débutant et certains pseudo DBA (ceux qui n'ont généralement jamais suivi une formation de DBA - administrer des bases de données, c'est un vrai métier...) veulent à tout prix, réduire la taille des fichiers... Ils mettent en œuvre pour cela un plan de maintenance avec réduction de la taille des fichiers (SHKRINFILE, SHRINKDATABASE, AUTO_SHRINK...) ce qui fait que non seulement ils perdent du temps à faire croitre les fichiers, mais aussi à les faire décroitre et recroitre encore... Bref, un vrai accordéon (ces pseudo DBA devrait se faire musiciens, ils ont plus à y gagner visiblement...).
Posons une analogie. Imaginons que vous construisez un parking. Il est plein, mais il y a encore de la surface non structurée. Une voiture arrive. Vous vous mettez à lisser le béton, peindre le sol et délimiter la place. La voiture repart, vous prenez votre marteau piqueur afin de détruire votre travail... Au moins avec une telle stratégie, c'est le secteur du bâtiment qui va y gagner !!!

C'est affreux mais je me suis reconnu la dedans.. Et malheureusement je suis aussi doué en musique qu'en SQL.. Suis je irrécupérable ?
Aurais-tu une bible pour débutant pour les DBA musicien maçon comme moi ?

[SQLpro]

DBA SQL Server est un cursus de formation d'environ 12 jours... Le mieux étant de faire la formation de base puis de voir... Pour ma part j'organise depuis des années les formations SQL Server (cours, support, exercices) chez Orsys àa la Défense et Rudi Bruchez (voir rudi.developpez.com) m'a rejoint il y a maintenant 2 ans pour les donner.

Sinon il y a quelques bouquins d'admin :
http://sqlpro.developpez.com/SQL_ser...SQL_Server.pdf
comptez 700 à 1000 pages...
parmi lesquels mon préféré est le Dan Wood
Cependant rien ne remplacera les conseils distillés au cours d'une formation, conseils bien évidemment absent des ouvrages !

N'oubliez d'ailleurs pas que le formation professionnelle à votre poste de travail est une obligation légale de votre employeur et que celui-ci peut se la faire rembourser par l'intermédiaire des fonds mutualisés qu'il verse déjà comme FAFIEC ou AGEFOS...

A +

[elsuket]

Bonjour,

Hé oui mais quand on débute on ne peut pas demander une formation, on a encore tout à démontrer !
Alors on devient DBA accidentellement, et on apprend tout sur le tas ...
Pour ma part je me suis formé au tout début avec SQL Server 2005 Bible de Paul Nielsen, puis sur ce forum avec l'aide immense de SQLPro

Il n'y a aucun cursus scolaire (et pas qu'en France) qui permettre d'apprendre l'administration de base de données.
Seules les formations permettent d'acquérir rapidement le savoir, mais ce métier s'apprend de toute façon par l'expérience.

@++