MS SQL Server et les problématiques du journal de transaction

Date de publication : 2/07/2004

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

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


Article


Article

QUESTION

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 :

  1. vérifiez qu'une sauvegarde complète de la base a été précédemment entreprise
  2. 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'
       END as contenu,
       name as nom_logique
FROM sysfiles
 
contenu                  nom_logique      
------------------------ ------------------
données                  DB_DS_GIMA_H_Data
journal des transactions DB_DS_GIMA_H_Log 

(2 ligne(s) affectée(s))
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.

pour en savoir plus : http://support.microsoft.com/?id=272318


Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

Copyright © 2004 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide SGBD & SQL - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Hébergement - Participez - Copyright © 2000-2009 www.developpez.com - Legal informations.