IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

Fichier de log qui ne veut pas se réduire [2016]


Sujet :

MS SQL Server

  1. #1
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut Fichier de log qui ne veut pas se réduire
    Bonjour,

    J'ai mis en place un datawarehouse de recette, en copiant le datawarehouse de production. Suite à plusieurs alimentations, mon fichier .mdf fait 16 Go, ce qui correspond à la taille de la prod et ne me pose pas de problème. Par contre, le fichier _log.ldf pèse 54 Go ! De plus, ce sont des données de test, sur un datawarehouse qui est uniquement alimenté par ETL et n'a donc besoin d'aucune sauvegarde ni même d'aucun mécanisme transactionnel.

    J'ai tenté de faire un DBCC SHRINKFILE, par l'interface graphique et par SQL, mais ça n'a absolument aucun effet. J'ai aussi tenté de faire un BACKUP LOG TO DISK, mais ça m'a juste créé un fichier .trn de 54 Go, sans réduire le moins du monde la taile du fichier .ldf.

    J'ai fait un DBCC SQLPERF(LOGSPACE), voici les résultats :
    Log size : 50247,99 MB
    Log space used : 99,92655 %
    Status : 0
    Je comprends bien qu'il refuse de réduire la taille du fichier de log parce qu'il considère que toutes ces infos sont absolument indispensable, mais j'aimerais lui expliquer qu'il se trompe...

    J'aurais donc deux questions :
    1) dans l'immédiat, comment je sors de cette situation, à part détruire et re-créer la database (ce qui reste une option envisageable).
    2) à l'avenir, quels réglages je dois faire pour qu'il arrête de conserver inutilement les transactions après le commit ?

    EDIT :
    Bon, j'ai fini par comprendre qu'il fallait faire une sauvegarde Full avant le shrink. Ça m'a permis de régler le point 1, et je me suis empressé d'envoyer la sauvegarde à la corbeille.
    Je suis cependant preneur de conseil sur le point 2, parce que j'avoue que je suis un peu perdu dans tout ça !


    Merci pour toute info !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    1) le SHRINKFILE n'est opérationnel que si la sauvegarde du JT a été préalablement effectuée.
    2) la réduction n'est possible que si aucune ancienne transaction ne persiste.

    Pour s'assurer de ce dernier point, lancez la commande :

    DBCC OPENTRAN dans la base contextuelle. Cela donnera la date de la plus ancienne transaction encore vivante !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    Salut, et merci de ta réponse !

    J'ai fait le DBCC OPENTRAN, il me répond "Aucune transaction ouverte active."

    Je commence à comprendre un tout petit mieux le système, donc je vais réorienter ma question de départ. Le datawarehouse fait une quinzaine de Go de données, il est intégralement effacé et réalimenté toutes les nuits (no comment) par un processus ETL pas très bien construit, donc j'imagine que ça doit entraîner entre 20 et 50 Go de journalisation ; ce processus a néanmoins la politesse de fermer toutes ses transactions.

    Mon objectif est d'abord que le processus ETL se déroule sans contention, et ensuite de libérer un maximum d'espace disque à l'issue du processus (ces données volatiles n'ont absolument aucun besoin de sauvegarde en environnement de Test, et probablement pas non plus en Prod).

    Je crois comprendre que la solution serait quelque chose comme ça :
    • laisser le fichier .ldf sans limite (j'ai tenté de le limiter, ça fait juste planter l'alimentation)
    • effectuer une sauvegarde complète à l'issue du processus
    • détruire la sauvegarde
    • effectuer un SHRINKFILE


    Ça te semble correct ? Tu as une meilleure stratégie ?
    Est-ce qu'un CHECKPOINT ou une autre commande permet d'effacer les transactions validées et prises en compte par le lazy writer ?
    Qu'est-ce que je peux espérer améliorer en changeant le mode de sauvegarde ?

    En te remerciant,
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  4. #4
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    Citation Envoyé par Antoun Voir le message
    Salut, et merci de ta réponse !

    J'ai fait le DBCC OPENTRAN, il me répond "Aucune transaction ouverte active."
    Est-ce que tu as lancé la commande dans le contexte de la base de données concernée?

    Tu peux aussi utiliser les DMVs qui vont bien pour cela:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    SELECT 
        dtat.transaction_id,
        dtat.[name],
        dtat.transaction_begin_time,
        dtdt.database_id
    FROM sys.dm_tran_active_transactions dtat
        INNER JOIN sys.dm_tran_database_transactions dtdt
            ON dtat.transaction_id = dtdt.transaction_id;
    Citation Envoyé par Antoun Voir le message
    Mon objectif est d'abord que le processus ETL se déroule sans contention, et ensuite de libérer un maximum d'espace disque à l'issue du processus (ces données volatiles n'ont absolument aucun besoin de sauvegarde en environnement de Test, et probablement pas non plus en Prod).

    Je crois comprendre que la solution serait quelque chose comme ça :
    • laisser le fichier .ldf sans limite (j'ai tenté de le limiter, ça fait juste planter l'alimentation)
    • effectuer une sauvegarde complète à l'issue du processus
    • détruire la sauvegarde
    • effectuer un SHRINKFILE


    Ça te semble correct ? Tu as une meilleure stratégie ?

    Est-ce qu'un CHECKPOINT ou une autre commande permet d'effacer les transactions validées et prises en compte par le lazy writer ?
    Qu'est-ce que je peux espérer améliorer en changeant le mode de sauvegarde ?
    S'il s'agit bien d'un datawarehouse, je commencerai par changer le mode de récupération à SIMPLE pour plusieurs raisons:
    - Pas besoin de s'occuper de la sauvegarde du journal de transactions pour le vider. Un checkpoint automatique se chargera alors de vider le journal lorsque celui-ci est occupé à > 70%.
    - Si le processus ETL fait du BULK LOAD alors on peut espérer faire du chargement avec un minimum de journalisation (prérequis ici)

    Faire un SHRINK du fichier journal en mode yoyo (shrink après chaque chargement ETL) ne fera que de générer des IO supplémentaires et éventuellement provoquer une fragmentation du système de fichier sous jascent. Il vaut mieux dans ce cas dimensionner correctement le volume et le fichier journal pour absorder la charge du processus ETL.

    ++

  5. #5
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    Citation Envoyé par mikedavem Voir le message
    Est-ce que tu as lancé la commande dans le contexte de la base de données concernée?
    Si "dans le contexte" ça veut dire avec un USE sur la bdd, oui.

    Citation Envoyé par mikedavem Voir le message
    S'il s'agit bien d'un datawarehouse, je commencerai par changer le mode de récupération à SIMPLE pour plusieurs raisons:
    - Pas besoin de s'occuper de la sauvegarde du journal de transactions pour le vider. Un checkpoint automatique se chargera alors de vider le journal lorsque celui-ci est occupé à > 70%.
    - Si le processus ETL fait du BULK LOAD alors on peut espérer faire du chargement avec un minimum de journalisation (prérequis ici)

    Faire un SHRINK du fichier journal en mode yoyo (shrink après chaque chargement ETL) ne fera que de générer des IO supplémentaires et éventuellement provoquer une fragmentation du système de fichier sous jascent. Il vaut mieux dans ce cas dimensionner correctement le volume et le fichier journal pour absorder la charge du processus ETL.
    OK, je vais essayer comme ça.
    Merci beaucoup !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  6. #6
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    Bonjour,

    Au final, j'ai passé le datawarehouse de test en recovery mode simple, en limitant le .ldf à 7,5 Go (pour un .mdf de 16 Go). J'ai exécuté plusieurs processus d'alimentation, dont chacun effectue un TRUNCATE des tables pour les re-remplir à nouveau.

    Ce qui me chiffonne, c'est quand je regarde les dates de dernière modif des fichiers : le .mdf n'a pas bougé depuis une dizaine de jours, seul le .ldf absorbe la charge. Pourtant, cette base a été intégralement vidée et re-remplie plusieurs fois, j'ai même fait une sauvegarde et un shrink du log. J'ai aussi fait un CHECKPOINT manuel, qui n'a évidemment rien changé.

    Est-ce que ce comportement est normal ? Comment puis-je forcer SQL Server à passer les données du .ldf vers le .mdf ?

    à J'ai tendance à penser que si les dernières données étaient dans le .mdf plutôt que dans le .ldf, cela me permettrait à la fois de gagner de l'espace disque et d'améliorer l'efficacité des requêtes, est-ce que je me trompe ?

    Merci pour vos indications !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Antoun Voir le message
    ...Ce qui me chiffonne, c'est quand je regarde les dates de dernière modif des fichiers : le .mdf n'a pas bougé depuis une dizaine de jours, seul le .ldf absorbe la charge. Pourtant, cette base a été intégralement vidée et re-remplie plusieurs fois, j'ai même fait une sauvegarde et un shrink du log. J'ai aussi fait un CHECKPOINT manuel, qui n'a évidemment rien changé.

    Est-ce que ce comportement est normal ? Comment puis-je forcer SQL Server à passer les données du .ldf vers le .mdf ?
    Ce comportement est tout à fait normal et SQL Server a sans aucun doute enregistré les dernières données dans les fichiers de données. Cepedant pour des raisons d'efficacité des écritures physique, SQL Sevrer ne passe pas par Windows pour écrire dans son "storage", mais par des routines interne de l'OS SQL Server (appelé SOS pour SQL Server Operating System), ce qui fait que Windows est "aveugle" de certaines opérations effectuées sur les fichiers. Ainsi, la date de ces fichiers sera mis à jour si vous "fermez" la base par détachement, ou arrêt !

    à J'ai tendance à penser que si les dernières données étaient dans le .mdf plutôt que dans le .ldf, cela me permettrait à la fois de gagner de l'espace disque et d'améliorer l'efficacité des requêtes, est-ce que je me trompe ?
    Oui, vous vous trompez..... Les données sont toujours accédées en mémoire. Jamais directement sur le disque....

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    Octobre 2006
    Messages
    6 277
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 6 277
    Points : 11 733
    Points
    11 733
    Par défaut
    OK, dans ce cas j'arrête de me prendre la tête.

    Merci à tous !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase, 2ème édition

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2010] Fonction Dir qui ne veut pas passer au fichier suivant
    Par Tekiila_67 dans le forum Macros et VBA Excel
    Réponses: 42
    Dernier message: 04/05/2018, 09h00
  2. [JScrollPane] qui ne veut pas se mettre en haut a gauche
    Par Cyber@l dans le forum AWT/Swing
    Réponses: 4
    Dernier message: 24/11/2006, 11h41
  3. fichier SWF qui ne veut pas se lancer
    Par arnaud_verlaine dans le forum Flash
    Réponses: 12
    Dernier message: 06/09/2006, 10h15
  4. JOptionPane qui ne veut pas se fermer!
    Par benthebest dans le forum AWT/Swing
    Réponses: 6
    Dernier message: 29/12/2005, 23h05
  5. un fichier qui ne veut pas être supprimé!!!!
    Par en_stage dans le forum Autres Logiciels
    Réponses: 4
    Dernier message: 22/10/2005, 02h08

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo