Fichier de log qui ne veut pas se réduire

Version imprimable

19/10/2020, 11h06
Antoun

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 :

Citation:

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 !
19/10/2020, 19h02
SQLpro

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 +
19/10/2020, 23h45
Antoun
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,

Citation:

Envoyé par Antoun

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:

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

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.

++

20/10/2020, 10h25
Antoun

Citation:

Envoyé par mikedavem

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

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 !
26/10/2020, 07h47
Antoun

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 !
26/10/2020, 09h07
SQLpro

Citation:

Envoyé par Antoun

...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 !

Citation:

à 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 +
26/10/2020, 09h18
Antoun

OK, dans ce cas j'arrête de me prendre la tête.

Merci à tous !