[SQL Server 2005] Tronquer les logs si session active

[CarlaJohnson]

Bonjour à tous,

Alors je pense que je n'ai pas compris quelque chose...

Petite explication avant de vous exposer mon problème:
- Nous faisons un backup Full de la base toutes les nuits à 01h00 du matin
- Entre 04h et 22h, toutes les heures nous faisons un backup des logs
- Tous ces backups sur le disque du serveur sont ensuite sauvegardé par un agent externe (HPDP).
- Une fois la sauvegarde de l'agent externe, nous supprimons les backups sur disque pour gagner de la place

Et voila ce qui se passe chaque jour:

=> backup full base -> backup log -> sauvegarde agent externe -> suppresion backup sur disque

Cependant aujourd'hui le backup full s'est bien exécuté mais à 04h les logs ne sont plus backupés. Je regarde le fichier .ldf qui fait 42Go. Le disque de backup est de 40Go...

Première question:
- Est-ce que le fait de lancer un backup des logs, il essaye de faire un backup des 42Go ?
On dirait que c'est ce qu'il essaye de faire car lorsque je suis le backup sur le disque, la taille du backup (fichier .trn) augmente jusqu'a plus de 30Go et le job de backup "plante" et les logs ne sont donc pas backupés.

Deuxième question:
- Les backups de logs ne "reprenne" pas qu'à partir du dernier backup full de la base ?
Car avant de lancer le backup des logs, j'ai fait un backup full de la base (17Go) et ensuite je pensais que le backup des logs serait minime mais il me backup les 40Go...

Troisième question:
- Est-il possible de tronquer les logs alors que des sessions sont actives sur la base ?
Car lorsque je lance ce code, il me dis que c'est impossible car des sessions sont actives sur la base...

Code :

1
2
3
4
USE DynamicsAxPR
GO
 
DBCC SHRINKFILE(PR_log, 100)

J'ai lu les petits papiers de SQLPro concernant les logs: http://sqlpro.developpez.com/cours/sqlserver/log/ mais je n'ai pas réussi à trouver les réponses à mes questions...

Merci de m'avoir lu.

Cordialement,

[dbaffaleuf]

1) Si tu le lances maintenant, il y a des chances oui.
2) C'est normal, un backup full ne recycle pas le journal de transactions, ce qui veut dire que le backup log suivant doit se retaper les 42 Gb de log records.
3) En fonction du temps de backup full, il serait peut être plus intéressant de passer ta base en mode SIMPLE pour laisser le checkpoint recycler les 42 Gb de transactions validées, faire le shrink, et repasser la base en mode FULL ensuite. Attention, une fois la base repassée en mode FULL, il faut reprendre un backup full pour réinitialiser la chaîne de backup.

Sais-tu pourquoi les backups de journaux n'ont pas tourné à partir de 04h00 ?

[CarlaJohnson]

Merci pour ta réponse dbaffaleuf

Peux-tu m'éclairer encore un petit peu :

- Passer une base du mode FULL à Simple ou inversement peut se faire "à chaud" ? Pas d'arrêt des services/sessions qui sont connectés sur la base ? Invisible pour les users applicatifs ?

- "checkpoint recycler les 42 Gb de transactions validées" c'est-à-dire ? unlocker les transactions qui sont "actives" et qui bloquent le troncage ?

Ok pour le backup directement après le passage en mode Full.

Envoyé par dbaffaleuf

Sais-tu pourquoi les backups de journaux n'ont pas tourné à partir de 04h00 ?

Manque de place sur le disque mais ne comprends pas pourquoi d'un coup, on backup chaque heure 100Mo environ et que arrivé 04h (pas de backup log entre 22h et 04h) du matin il veuille backuper 42Go ?


Il m'est arrivé la même chose ya deux semaines mais la taille du backup log était de 20Go donc possibilité de backup, contrairement à aujourd'hui où il backup 42Go.
Je synthétise le job sql de backup full de la nuit:
1 - backup incremental fait pas l'agent externe qui sauvegarde les backup du jour (.bak + .trn) qui sont sur disque
2 - Si backup incremental de l'agent externe ok, suppression des backups qui sont sur disque
3 - Si backup incremental de l'agent externe échou, il ne supprime pas les backup sur le disque
4 - Backup Full de la base
5 - Backup Full du disque fait par l'agent externe
6 - Toutes les heures backups des logs

L'agent externe a planté et n'a donc pas supprimé ce qui se trouvait sur disque, mais je ne pense pas que cela concerne les logs...

[dbaffaleuf]

Envoyé par CarlaJohnson

- Passer une base du mode FULL à Simple ou inversement peut se faire "à chaud" ? Pas d'arrêt des services/sessions qui sont connectés sur la base ? Invisible pour les users applicatifs ?

Aucun impact pour les users. La seule différence est le passage de SIMPLE à FULL où tu devras effectuer un backup full avant de recommencer à backuper les journaux.

Envoyé par CarlaJohnson

- "checkpoint recycler les 42 Gb de transactions validées" c'est-à-dire ? unlocker les transactions qui sont "actives" et qui bloquent le troncage ?

Non, il s'agit juste de passer les fragments de journaux qui contiennent ces 42 Gb de transactions validées (les VLF) inactifs, pour que le LOG MANAGER puisse les réutiliser pour les transactions qui suivent. Les transactions qui sont actives sont dans des fragments dits 'actifs', ces fragments ne peuvent pas être réutilisés.

Envoyé par CarlaJohnson

Manque de place sur le disque mais ne comprends pas pourquoi d'un coup, on backup chaque heure 100Mo environ et que arrivé 04h (pas de backup log entre 22h et 04h) du matin il veuille backuper 42Go ?

Tu as un batch qui tourne entre 22h00 et 04h00 ?

[CarlaJohnson]

Merci pour tes précisions !

Concernant les batchs il faudrait que je demande au responsable applicatif savoir si en effet un batch tourne entre ces heures la...

Merci beaucoup !! je passerai en résolu une fois que je l'aurai fait !

[SQLpro]

ATTENTION : si votre journal de transaction est si important et qu'il ne "diminue" pas pour autant (ou tout du moins son utilisation ne diminue pas) lors c'est peut être qu'il y a une transaction encore active depuis des lustres....

Je pense que vous devriez vérifier ce point en faisant un
DBCC OPENTRAN
dans la base visée.

A +

[CarlaJohnson]

Merci SQLPro pour votre réponse.

Je vais tester tout ça demain une fois de retour au boulot en espérant que vous ayez vu juste

Merci.

[CarlaJohnson]

Bon je n'y comprends rien...

J'arrive matin et je trouve mon backup Full de la nuit ainsi que les backups des logs toutes les heures à partir de 04h00... Tout est normal !



Ca m'embête un peu de ne pas savoir d'oú ca vient...

[elsuket]

Bonjour,

Si aucune opération particulière n'a eu lieu hier (comme une réindexation, une mise à jour de données massive, ...) peut-être qu'il s'agit :

- de quelqu'un qui a le droit d'exécuter des requêtes sur la base de données
- ou de l'application qui est supportée par votre base de données qui a laissé une transaction ouverte (il se peut que l'utilisateur de l'application ait laissé quelque chose s'exécuter, ou que votre application laisse une transaction ouverte dans l'attente d'une saisie qui ne s'est ... jamais faite)

Dans ce dernier cas il faudrait corriger l'application.

@++

[CarlaJohnson]

Envoyé par dbaffaleuf

1) Si tu le lances maintenant, il y a des chances oui.
2) C'est normal, un backup full ne recycle pas le journal de transactions, ce qui veut dire que le backup log suivant doit se retaper les 42 Gb de log records.
3) En fonction du temps de backup full, il serait peut être plus intéressant de passer ta base en mode SIMPLE pour laisser le checkpoint recycler les 42 Gb de transactions validées, faire le shrink, et repasser la base en mode FULL ensuite. Attention, une fois la base repassée en mode FULL, il faut reprendre un backup full pour réinitialiser la chaîne de backup.

Re tout le monde,

Petite précision car j'ai du mal concernant les logs...

Un backup Full de la base ne recycle pas les logs j'ai maintenant compris mais un backup des logs doit recycler les logs et je devrais me retrouver avec un fichier .ldf tout petit non ?

Merci pour vos réponses.

[mikedavem]

Envoyé par CarlaJohnson

Re tout le monde,

Petite précision car j'ai du mal concernant les logs...

Un backup Full de la base ne recycle pas les logs j'ai maintenant compris mais un backup des logs doit recycler les logs et je devrais me retrouver avec un fichier .ldf tout petit non ?

Merci pour vos réponses.

Pas du tout. Une sauvegarde du journal ne fait que vider ce dernier et rend les VLF qui le constituent réutilisables.

Pour réduire le fichier journal il faut utiliser l'instruction DBCC SHRINKFILE (ou SHRINKDATABASE)

++

[SQLpro]

Envoyé par CarlaJohnson

Un backup Full de la base ne recycle pas les logs j'ai maintenant compris mais un backup des logs doit recycler les logs et je devrais me retrouver avec un fichier .ldf tout petit non ?

NON !!! le BACKUP LOG vide le journal de transaction mais n'en diminue pas la taille.
Les transactions sauvegardées sont considérées comme inutiles et l'écriture dans le journal écrase les ancienne données.
La seule commande qui permet de diminuer physiquement la taille du fichier du JT est DBCC SHRINK (SHRINKFILE ou SHRINKDATABASE).

A +

[CarlaJohnson]

Envoyé par SQLpro

NON !!! le BACKUP LOG vide le journal de transaction mais n'en diminue pas la taille.

Oups désolé Mr SQL

Mais merci pour l'info, je comprends un peu mieux

Faire un backup des logs, une fois celui ci terminé, on peut faire un shrink des logs si l'on veut réduire la taille du journal !

Merci beaucoup !

[CarlaJohnson]

Bonjour à tous,

J'ai remarqué qu'un plan de maintenance tournait la nuit de le dimanche à minuit...

Dans ce plan de maintenance il y a :
- Check Database Integrity
- Shrink Database (pas bien hein ?!)
- Reorganize Index (cause de l'augmentation du journal ?)
- Update Statistics
- Clean Up History

Je pense donc savoir maintenance d’où vient la croissance du journal des transactions qui se passe le weekend...

[mikedavem]

Oui c'est un problème classique. Les tâches de maintenance d'index sont en général coûteuses en terme d'espace au niveau du journal des transactions.

Une bonne façon de le détecter est de créer une alerte et de l'activer par exemple au dessus de 80% d'espace utilisé dans le journal. Avec l'heure de déclenchement vous pourrez corréler avec les dates d'exécutions des tâches de maintenace d'index de vos plans.

++