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

QUESTION

Nous utilisons un logiciel […] qui s'appuie sur une base SQL Server.

Nous constatons que les fichiers LOG grossissent de façon incontrôlable.
À la cré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 fichiers de log, sont en fait ce que l'on appelle en français les « fichiers de journalisation ». Ils contiennent toutes les transactions effectuées dans la base de données (INSERT, UPDATE, DELETE..) enregistrées 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 eu 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.

P.-S. Si vous ne connaissez pas les noms logiques des objets (base et journal des transactions), veuillez exécuter la requête 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 entrainer 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 durant ce laps de temps.

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