Élément récurent dans les forums : au secours, le fichier de logs grossit de façon incontrôlable... les commandes de troncature et de compression de BD de SQL Enterprise Manager n'ont aucun effet ! Pas de remède miracle, avant de vous donner la solution, une petite explication...
Nous utilisons un logiciel [...] qui s'appuie sur une base SQL Server.
Nous constatons que le fichiers LOG grossissent de façon incontrolable. A la céation nous avons autorisé la croissance automatique des fichiers sans limite (si ce n'est celle du disque).
Le problème est le suivant :
- Comment réduire la taille des fichiers LOG, sachant que les commandes de troncature et de compression de BD de SQL Enterprise Manager n'ont aucun effet (mauvaise utilisation ou mauvaise procédure ?) ? - Après avoir réduit la taille des fichiers, puis fixé une taille maximale que se passe-t-il quand cette taille est atteinte :
le plus ancien enregistrement est écrasé par le nouveau ?
il y a une erreur et la gestion de ce cas est du ressort du créateur du logiciel ?
REPONSE
Les fichier de log, sont en fait ce que l'on apelle en français les "fichiers de journalisation". Ils contiennent toutes les transactions effectuées dans la base de données (INSERT, UPDATE, DELETE..) enregistrée en séquence. Ce sont des fichiers en croissance forte qui peuvent parfaitement dépasser de beaucoup la taille des données. Par exemple si, partant d'une base vide, on ajoute et supprime un millier de lignes dans une table, et cela plusieurs centaines de fois, la base sera quasi vide et le journal des transactions important. Il se peut donc que le journal des transactions vienne donc à occuper un espace disque important et si l'administrateur n'y prend pas garde, saturer les disques...
Les remèdes sont les suivants :
surveillance des quotas d'espace disque via l'OS (alertes administratives)
planification du vidage des journaux de transactions
limitation de la croissance du fichier de transaction (dangereux)
Mais il se peut que vous ayez du mal à vider votre journal de transaction ! Pourquoi ce comportement ? Tout simplement par sécurité.
En effet les différentes sauvegardes : base complète, base différentielle ou journal des transactions permettent de récupérer une base corrompue. Tant que ces fichiers ne sont pas tronqués, la récupération est en principe toujours possible. C'est pourquoi tant que la sauvegarde base + journal n'a pas eut lieu, il n'est pas possible de tronquer les fichiers.
EN CAS D'URGENCE voici comment réduire un journal des transactions à une valeur voulue :
L'exemple suivant illustre ceci avec la base de données toto et essaye de réduire le journal toto_log à 200 Mo :
1. Exécutez ce code dans l'analyseur de requête :
USE toto
GO
DBCC SHRINKFILE(toto_log, 200)
Cet ordre tente de récupérer les espaces vides du journal de transaction.
Vérifiez la taille du journal de transaction dans l'entreprise manager (au besoin, rafraichissez l'affichage F5)
2. : Si le journal toto_log est toujours plus grand que la taille voulue, exécutez ce code dans l'analyseur de requête :
BACKUP LOG toto WITH TRUNCATE_ONLY
Attention, cet ordre effectue une "fausse" sauvegarde du journal de transaction, c'est à dire qu'il ne sauvegarde rien, mais transforme l'espace des transactions terminées en espace mort. Par sécurité, vous pouvez faire une sauvegarde du journal des transactions, ce qui prendra plus de temps mais contribuera au même but.
Vérifiez la taille du journal de transaction dans l'entreprise manager (au besoin, rafraichissez l'affichage F5)
3. : Si le journal est toujours plus grand que la taille demandée, exécutez ce code dans l'analyseur de requête :
DBCC SHRINKFILE(toto_log, 200)
Vérifiez la taille du journal de transaction dans l'entreprise manager (au besoin, rafraichissez l'affichage F5)
Le journal de transactions devrait être réduit à 200 Mo.
Si ce n'est pas le cas :
vérifiez qu'une sauvegarde complète de la base a été précédemment entreprise
vérifiez que le mode de recouvrement de votre base de données n'est pas le mode "simple". Pour ce faire : dans entreprise manager, cliquez droit sur la base voulue, sélectionnez l'entrée de menu "propriétés", onglet "option", item "récupération", champs "Modèle".
NOTA : toto est le nom logique de la base, toto_log est le nom logique du journal de transactions
PS : si vous ne connaissez pas les noms logique des objets (base et journal des transactions), veuillez exécuter la requete suivante :
SELECT CASE WHEN status & CAST(0x40 AS INT) = CAST(0x40 AS INT)
THEN 'journal des transactions'
ELSE 'données'
ENDas contenu,
nameas nom_logique
FROM sysfiles
ATTENTION : la limitation de la taille du fichier de transaction peut s'avérer un remède pire que le mal : il peut entraîner le blocage du serveur. De même un fichier de transaction ridiculement petit entrainera des performances notablement dégradées. Inversement, une grande taille n'affecte pas les performances du fait qu'il est écrit en séquentiel. En principe un journal des transactions doit être capable de recevoir les écritures de transaction d'au moins une période de temps donné (heure, journée, semaine...) avant sauvegarde. Pour obtenir des performances, sa taille minimale doit donc être basée sur la moyenne de remplissage de ce laps de temps.
