none
Impossible de shrinker un fichier transaction log RRS feed

  • Question

  • Bonjour,

    Pardonnez par avance ma maladresse je suis débutant sur SQL Server!
    Nous disposons d'une base de donnée SQL assez sensible, et je souhaite réduire la taille de son fichier log qui aujourd'hui pèse 162 Go, et continu de grossir. L'espace disque va finir par être insuffisant.

    Lorsque j'exécute la requête "DBCC SQLPERF (logspace)" il affiche une occupation de l'espace de plus de 99%.
    Ensuite j'exécute un "DBCC SHRINKFILE (nom_log, 50000)" rien ne se passe mon fichier log fait toujours 162 go.
    Ma base de donnée est en mode SIMPLE et le fichier log est configuré de la sorte :
    Initial Size = 160 564 MB
    autogrowth = By 500 MB restricted growh to 2 097 152 MB

    Ma question est simple, comment je peux réduire ce fichier de log même de 50 Go ?

    Merci d'avance et bonne journée !


    Julien.

    mercredi 1 février 2017 08:45

Réponses

  • Il y a donc de la réplication qui a du être mise en oeuvre à un moment donné (ou qui est encore utilisée).

    Il doit y avoir un job de log reader qui ne fonctionne plus.

    SI par contre il n'y a plus de réplication paramétrée, un repldone devrait permettre de vider la log.

    exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

    a exécuter connecté sur la base en question.

    Ensuite un petit CHECKPOINT

    Et pour finir, un SHRINKFILE (JAMAIS de shrink database ou de AutoShrink, cela ne devrait même pas exister sur SQL Server) pour ramener le LDF à une dizaine de GB

    DBCC SHRINKFILE (nomlogisuefichierldf,10240)


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    • Marqué comme réponse DarkMoutch vendredi 3 février 2017 12:55
    vendredi 3 février 2017 10:04

Toutes les réponses

  • Bonjour

    Commencez par faire un backup log, votre base devant être en mode de récupération complet.

    Ensuite vous pourrez diminuer un peu sa taille, mais en fonction de la stratégie de réindexation et de la taille des grosses tables, il faudra évaluer quel est l'espace minimum requis.

    Planifiez à minima un backup complet tous les soir, plus un backup log toutes les heures ou 30 minutes, ce qui vous donne le RPO minimum. Dernier point : si al base est sensible, peut être de la haute disponibilité serait nécessaire. Ou tout du moins un minimum de formation pour n DBA par accident.

    Cdlt

    Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM


    jeudi 2 février 2017 17:41
  • Bonjour,

    quelle est la taille du fichier principal de la base ?

    Les logs sont normalement vidés par la sauvegarde, mais le fichier de log ne diminuera pas automatiquement sauf si l'on a configuré la base en mode AutoShrink.

    Les logs ne sont pas vidés si:

    - il n'y a pas de sauvegarde correcte de SQL avec un agent SQL adapté

    - Les requêtes ne sont pas terminées (ou n'arrivent pas à se terminer...)

    - Le fichier de la base principale a atteint sa taille maximale autorisée.

    A bientôt,


    Thierry DEMAN. Exchange MVP. MCSE:Messaging 2013,MCSE:Server Infrastructure 2012(83 MCPs). MCSA Office 365 https://mvp.microsoft.com/en-us/mvp/Thierry%20Deman-7660 http://base.faqexchange.info

    vendredi 3 février 2017 07:49
    Modérateur
  • Bonjour,

    Merci pour vos réponses.
    Etant donné que la base est en mode simple, je ne peux sauvegarder les logs. (si j'ai bien saisi)
    Des sauvegardes sont faites toutes les nuits,
    La base fait 30go mais divisé en 3 types de fichiers, et aucune limite de taille n'est configurée.
    Ce weekend je vais passer cette requête et voir si ca améliore les choses. je l'ai testé sur une base de test et j'ai pu vider les logs de cette manière :

    -- To permit log backups, before the full database backup, modify the database -- to use the full recovery model. USE master; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO -- Create AdvWorksData and AdvWorksLog logical backup devices. USE master GO EXEC sp_addumpdevice 'disk', 'AdvWorksData', 'Z:\SQLServerBackups\AdvWorksData.bak'; GO EXEC sp_addumpdevice 'disk', 'AdvWorksLog', 'X:\SQLServerBackups\AdvWorksLog.bak'; GO -- Back up the full AdventureWorks2012 database. BACKUP DATABASE AdventureWorks2012 TO AdvWorksData; GO -- Back up the AdventureWorks2012 log. BACKUP LOG AdventureWorks2012 TO AdvWorksLog; GO

    Cependant j'ai une question, Est ce que SQL Server a besoin d'un minimum d'espace disque pour vider les logs ? (en %).
    Si je n'y arrivait pas, est ce que l'espace disque insuffisant peut être la cause ?

    Merci!

    Julien.

    vendredi 3 février 2017 08:46
  • Si la base est en mode de récupération simple, le problème est ailleurs.

    En mode de récup simple, la log est "automatiquement" vidée lorsque le % de remplissage du fichier LDF, du journal, dépasse 70%. A ce moment un CHECKPOINT est issu. Les dirty pages sont flushées dans le ou les fichiers de data, et al log "purgée".

    Vous pouvez manuellement soumettre la commande CHECKPOINT.

    La log, même en mode de récup simple ne peut être vidée si de la réplication transactionnelle, du database mirroring/groupe de disponibilité, change data capture sont utilisés mais défaillants, pour compléter les raisons avancées par Thierry. De plus on ne peut pas vider la log lorsqu'un snapshot est en cours de création. Dans ce cas, pas de truncate de log possible.

    Que renvoie la requete select * from sys.databases    ?

    Et principalement la colonne log_reuse_wait_desc. Nous aurons le fin mot de l'histoire;


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    vendredi 3 février 2017 09:17
  • j'ai lancé un select * from sys.databases
    pour la base concerné dans la colonne "log_reuse_wait_desc" j'ai REPLICATION

    Julien.

    vendredi 3 février 2017 09:52
  • Il y a donc de la réplication qui a du être mise en oeuvre à un moment donné (ou qui est encore utilisée).

    Il doit y avoir un job de log reader qui ne fonctionne plus.

    SI par contre il n'y a plus de réplication paramétrée, un repldone devrait permettre de vider la log.

    exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

    a exécuter connecté sur la base en question.

    Ensuite un petit CHECKPOINT

    Et pour finir, un SHRINKFILE (JAMAIS de shrink database ou de AutoShrink, cela ne devrait même pas exister sur SQL Server) pour ramener le LDF à une dizaine de GB

    DBCC SHRINKFILE (nomlogisuefichierldf,10240)


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    • Marqué comme réponse DarkMoutch vendredi 3 février 2017 12:55
    vendredi 3 février 2017 10:04
  • Merci de votre aide,
    Je peux lancer les requêtes en live ? Ca ne va pas bloquer la base le temps de la requête et la rendre indisponible ?

    Julien.

    vendredi 3 février 2017 10:08
  • Vous pouvez y aller pendant la prod.

    Pour le shrinkfile, cela va faire monter un peu lse IOs, si votre sous système disque n'est pas très rapide, attendez une période de moindre activité (12-14 ou le soir)


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    vendredi 3 février 2017 10:14
  • Bonjour,

    le problème est que cela existe !

    Et que cela est parfois utile, comme sur la TempDB, par exemple...

    Mais la tendance est rarement au rétrécissement des bases.

    A+


    Thierry DEMAN. Exchange MVP. MCSE:Messaging 2013,MCSE:Server Infrastructure 2012(83 MCPs). MCSA Office 365 https://mvp.microsoft.com/en-us/mvp/Thierry%20Deman-7660 http://base.faqexchange.info

    vendredi 3 février 2017 12:53
    Modérateur
  • Ca a marché ! j'ai gagné 150go d'espace disque !
    Initial size est passé a 10 252mb
    J'ai limité l'autogrowth à 100 000mb plutôt que de le laisser illimité

    Merci !

    Julien.

    vendredi 3 février 2017 12:54