Tronquer le log (journal des transactions) [Résolu]

[camboui]

Bonjour,

J'ai transféré 3 DB qui tournaient sur SQLserver2005 vers SQLserver2008. Pour cela j'ai simplement fait un backup depuis SS2005 puis un restore sur SS2008. Les scripts et programmes divers semblent fonctionner normalement sauf un script, celui qui permet de tronquer le log.
J'ai toujours fait comme ceci avec succès sur SS2005 (et même SS2000 à l'époque):

Code :

1
2
3
4
5
6
USE tmpDB
GO
BACKUP LOG tmpDB WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(tmpDB_Log) -- shrink
GO

Et voilà que ça ne fonctionne plus.
Voici les messages obtenus en fonction des DB:

Code :

Cannot shrink log file 2 (tmpDB_log) because of minimum log space required.

Code :

1
2
Cannot shrink log file 2 (tmpDB_Log) because
the logical log file located at the end of the file IS IN USE.

J'ai ainsi 4 DB dont 3 qui refusent de tronquer le LOG, même après avoir détacher la DB, arrêter les services SQL ou même redémarrer le serveur. Pour la 4ème DB ça fonctionne, mais je n'ai pas fait de restore depuis SS2005, je l'ai créée directement sous SS2008.
Je vais bientôt avoir un soucis avec ces fichiers qui grossissent de 10GB par jour.
Y a-t-il un problème de compatibilité avec les backup de SS2005 vers SS2008 ?
Une solution ?
Merci.

[aieeeuuuuu]

Bonjour

Envoyé par camboui

Bonjour,
Je vais bientôt avoir un soucis avec ces fichiers qui grossissent de 10GB par jour.

Est-ce là la seule raison qui vous pousse a tronquer les logs ?
Quel est le mode de récupération de vos BDD, effectuez-vous des sauvegardes régulières ?

[camboui]

Merci pour votre réaction rapide.
Mais je ne comprend pas vos questions. Ou plutôt, je ne comprend pas en quoi elles vont résoudre le problème.
Vos question sont d'ordre décisionnel ou conceptuel. Mon problème est technique: un fichier qui grossit va finir par saturer les moyens hardware mis à disposition (disque). A moins qu'il ne grossise pas "indéfiniment" ?
En fait, je n'ai pas besoin de journal de transaction.

[elsuket]

Bonjour,

Ho que si vous en avez besoin !
N'importe quel moteur de base de données relationnelle SQL digne de ce nom l'implémente

Les questions d'aieeeuuuuu sont tout à fait d'ordre technique : si vous tronquez le fichier du journal des transactions, c'est que vous le gérez mal.
Cela rompt le chaînage des sauvegardes !

Au contraire, si vous faites des sauvegardes régulières de votre fichier du journal des transactions, à une fréquence qui convient au métier de votre entreprise, vous ne devriez pas avoir de problèmes.

On tronque le fichier du journal des transactions quand on a besoin d'espace disque, ce qui signifie :

- que c'est une manœuvre d'urgence
- que la méthode de gestion du fichier du journal des transaction doit être passée en revue
- que l'achat d'espace disque supplémentaire est peut-être urgent

Qu'est-ce qui vous a amené à dire que vous n'avez pas besoin de ce fichier ?
Quel est le mode de restauration de votre base de données ?

Code :

1
2
3
SELECT	recovery_model_desc
FROM	sys.DATABASES
WHERE	name = 'maBD'

Vous êtes vous demandé pourquoi ce fichier ne cesse de grossir ?
Quelle quantité de données votre entreprise peut se permettre de perdre ?
Avez-vous un plan de reprise d'activité après catastrophe / haute disponibilité ?

@++

[mikedavem]

Cf réponse ici

++

[aieeeuuuuu]

A moins qu'il ne grossise pas "indéfiniment" ?

Non en effet, pas forcément !
Mais cela dépend... du mode de récupération et des sauvegardes effectuées !

C'est pourquoi je vous posais ces questions, afin de savoir pourquoi votre TL grossit. D’ailleurs grossit-t-il vraiment de 10Go par jour ? ou est-ce que vous extrapolez en voyant qu'il fait 10Go au bout de 24h ?

Mes questions ne vont pas résoudre le problème, mais vont permettre d'y trouver une solution rapide et peut être bien meilleure qu'un truncate du TLog tous les jours, qui s'apparente un peu à détruire votre piscine en septembre pour la reconstruire en mai, tous les ans



bref :
1/ quel est le mode de récupération de vos BDD ?
2/ faites vous des sauvegardes ?

[camboui]

Tout d'abord, merci à mikedavem, il m'a permit de trouver une solution. Comme quoi les réponses les plus courtes sont les meilleures...
A la question X réponse Y. Point

Cependant, je dois avouer une chose, je ne connais pas bien sql server. Ainsi certaines de vos questions m'échappe. Par exemple 'mode de récupération' , moi pas comprendu.
Pire, j'exécute depuis longtemps sans me poser de question la commande suivante (et dire que personne n'a sourcillé en la voyant):

Code :

BACKUP LOG tmpDB WITH TRUNCATE_ONLY

avec à chaque fois un drole de message:

Code :

1
2
Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' IS NOT a recognized BACKUP OPTION.

Et pourtant ça marchait bien jusqu'à présent, je pouvais tranquilement faire un DBCC SHRINKFILE juste après avec succès. La commande me fait défaut maintenant sous SS2008 et je dois explicitement faire une sauvegarde du backup sur disque. C'est inutile puisqu'après je delete quand même le fichier.

SQL server doit être à mon service et non pas le contraire. J'ai des requêtes SQL qui doivent me donner des résultats. Le moyen de parvenir à me donner ces résultats est du ressort de SQL server (je suis prg C++, pas admin db).

J'utilise plusieures DB à différentes fins. Bien sûr, les plus importantes sont backupées tous les jours.
Celle qui m'occupe ici fait 6GB de data avec un log qui grossit de 10Gb par jour. Après 5 jours, les data font toujours 6GB mais le log 50GB. C'est là que je me suis inquiété en constatant que la troncature du LOG ne fonctionnait plus. D'où problème critique d'espace disque à brève échéance.
Les données dans cette DB ne sont pas importantes, on peut les 'perdre', on s'en fout. Ce qu'on ne peut pas perdre c'est la structure (table, index, view, sp, etc). Plus qu'un backup, c'est le script pour récréer la DB en cas de besoin qui est important.

J'espère que vous comprenez mieux maintenant pourquoi je n'ai pas besoin de LOG.

bref, en réponse aux questions:

1/ quel est le mode de récupération de vos BDD ?

'FULL'. Mais peut-être devrais-je utiliser 'SIMPLE' ?

Code :

ALTER DATABASE [tmpDB_log] SET RECOVERY SIMPLE WITH NO_WAIT

2/ faites vous des sauvegardes ?

Oui, mais ce n'est pas critique comme je l'ai expliqué. Cependant, je n'ai pas fait de sauvegarde depuis mon transfert de SS2005 vers SS2008 (j'ai aussi changé de machine). Je suis en cours de migration, donc je fais des tests et toutes les étapes de production ne sont pas encore actives.

Voilà, j'espère que vous me comprenez maintenant
Donc, puis-je paramétrer une DB sans devoir me soucier de l'espace disque qu'elle va consommer à cause d'un log grossissant intempestivement?
Sinon, puis-je tronquer le LOG sans devoir nécessairement le mettre sur disque, exemple improbable:

Code :

BACKUP LOG TO NULL

Et merci encore à tous pour vos interventions

[elsuket]

SQL server doit être à mon service et non pas le contraire. J'ai des requêtes SQL qui doivent me donner des résultats. Le moyen de parvenir à me donner ces résultats est du ressort de SQL server.

Certes, mais SQL Server, ce n'est pas comme les LEGO.
SQL Server a une documentation, il faut prendre la peine de la lire.

Les sauvegardes ne sont pas à négliger, et SQL Server ne peut pas décider à votre place du meilleur plan de sauvegarde pour votre base de données.

Vous faites deux erreurs :

- la première, vouloir utiliser WITH TRUNCATE_ONLY, je vous ai expliqué pourquoi
- la seconde, rétrécir votre fichier, parce que tôt ou tard l'espace qu'il a pris et que vous lui ôtez sera repris par SQL Server, et toutes vos requêtes attendront que le fichier du journal des transactions ait grossi.

C'est comme pêcher et remettre le poisson à l'eau pour le re-pêcher !
En plus en faisant cela vous augmentez le nombre de fichier virtuels de votre fichier du journal des transactions, donc vous augmentez son temps de récupération si elle crashe ou que vous la restaurez.
Enfin vous favorisez la fragmentation de ce fichier, dont vous pourriez vous passer si votre fichier était proprement géré ... et cela s'apprend

Donc je réitère les questions que j'ai posé dans mon précédent post, ainsi que le résultat de la requête

@++

[camboui]

Réponses dans le post #7 que j'ai édité

[SQLpro]

Lisez la documentation pour comprendre pourquoi TRUNCATE ONLY ne fonctionne pas....

Il suffit de surligner BACKUP DATABASE et de lire l'aide en ligne...
"
Remarque :
Les options BACKUP LOG WITH NO_LOG et WITH TRUNCATE_ONLY ont été supprimées.
....
"

En effet il est stupide de mettre votre base de données en mode de récupération FULL (complet - ce qui journalise un maximum) et demander de supprimer le contenu du journal, alors que pour minimiser les entrées du journal il suffit de placer votre mode de récupération en "simple".
Comme c'était incohérent, la version 2008 à rendu cette option inopérante, à juste titre !

A +

[camboui]

Je me demande quand a-t-on besoin d'un mode de récupération 'FULL'. J'ai l'impression que le mode 'SIMPLE' suffit toujours.
Perso, j'ai juste besoin d'un système transactionnelle, mais pas de conserver l'historique des transactions termninées.
Ça arrive souvent de se dire: "ah ben non, la transaction faite il y a 3 heures on l'annule" ?
Qu'est-ce que je risque en mettant toutes les BD en 'SIMPLE', sachant que des backups sont fait régulièrement ?
Merci.

[elsuket]

Le mode de récupération n'a logiquement rien à voir avec la validation ou l'annulation d'une transaction.
Il s'agit de d'assurer l'intégrité des données en cas de crash quelconque (de l'UPDATE sans WHERE par un développeur à l'attaque nucléaire ).

Le mode de récupération FULL est conçu pour ne perdre aucune donnée entre deux sauvegardes complètes ou différentielles de la base de données.

Donc si votre entreprise est un hôpital ou une banque, et qu'il s'agit respectivement de la base de données qui gère les visites ou les transactions, vous ne voulez et pouvez pas vous permettre de perdre la moindre donnée.

Dans le mode de récupération SIMPLE, vous considérez que vous pouvez perdre une certaine quantité de données : c'est typiquement le temps entre deux sauvegardes complètes de base de données, qui peuvent convenir par exemple à un petit commerce.

@++

[camboui]

OK merci, j'y vois un peu plus clair (enfin, je crois...)

Je vais passer toutes les DB en mode SIMPLE. Il semble qu'on a pas besoin du mode FULL. D'ailleurs je pense que personne ne saurait que faire si une bourde avait eu lieu plus tôt dans la journée, à part reprendre le backup de la veille...
Merci

[elsuket]

à part reprendre le backup de la veille...

Un peu plus en fait : remonter la sauvegarde de la veille sur la même instance et "replacer" les données par UPDATE / INSERT entre les deux bases

Pensez aussi aux captures instantanées de base de données );

@++

[camboui]

Comment puis-je penser à des choses que j'ignore ???
Ne me montrez pas trop de choses, je ne vais plus savoir où donner de la tête