Optimisation de ma base de données

Bonjour,

J'ai un petit problème technique sur ma base de données sql server 2008. Je viens de m'apercevoir que ma log était de 72Go (idf), alors que ma base de données ne fait que 82Mo (mdf).

Pour essayer de solutionner mon problème, j'ai d'abord fait un SHRINKFILE afin de réduire ma log à 100Mo, mais sans succès, je suis encore 68 Go.

Code:

---

1 2 3 4 5

use toto go DBCC SHRINKFILE ( toto_Log,100 ) go

---

Comment puis je faire pour réduire la taille de ma log, car elle commence à devenir volumineuse sur mon disque.

Au niveau du paramétrage du fichier log, le paramétrage est le suivant : Pièce jointe 172672 ou pièce jointe.

Comment puis je faire pour limiter la casse ? puis je faire un truncate only et comment ?

Merci pour votre aide.

Bonsoir
Il y a de forte chance pour que ce "problème" vienne du mode de récupération (recovery model) de la base de données. Elle doit surement être en FULL, c'est à dire que toutes les transactions sont logué dans le fichier de log jusqu'à la réalisation d'un backup de log de cette base ce qui à pour conséquence de vider le journal de log de toutes les transactions commités (validés).

Dans votre cas :

• Soit vous n'avez pas de politique de backup de log et donc vous pouvez changer le mode de recovery de la base en SIMPLE par la commande suivante :
  
  Code:

  ---

  ALTER DATABASE [Mabase] SET RECOVERY SIMPLE

  ---

  ce qui à pour conséquence, de logguer seulement les transactions en cours, nécessaire en cas de crash serveur lors du redémarrage.
• Soit, il faut instaurer une politique de backup de log (avec des backups FULL aussi 8-))

Peu importe le choix que vous faites, il faudra réaliser au moins 1 fois le shrink du fichier de log. Ensuite, normalement le fichier de log prendra une taille au fil du temps qui variera très peu.

en plus, j'ai lancé la sauvegarde de la LOG et cette dernière est interminable.
Je précise que j'ai arrêté l'agent sql server, afin qu'un plan de maintenance ne démarre pas en même temps que je réalise ma sauvegarde.
Ais je bien fait ? Comment arrêter le backup s'en mettre en péril la base. Le fichier toto.trn est toujours à 0.

Code:

---

1 2 3

BACKUP LOG toto TO DISK = 'C:\Bakcup\toto.trn' go

---

La solution idéale serait de mettre en place une politique de sauvegarde. Mais comment puis je m'y prendre ?

Merci pour vos réponses.

Alors pour répondre à l'urgence, il est possible de passer la base en recovery simple, shrinker le fichier de log, remettre la base en full, faire un backup full et laisser la stratégie de backup full et de backup log reprendre leurs vies.

Pour définir une politique de sauvegarde, il faut savoir répondre à :

• Quelle est la taille de chacune des bases de données
• Quel est le volume des modifications de données
• Combien de temps la base de données peut-elle rester indisponible
• La perte de modifications est-elle cruciale
• Est-il facile de recréer les données perdues
• Qu'elles sont les périodes importantes d'utilisation de la base de données
• La base subit-elle des surcharges de travail ponctuelles pendant lesquelles il n'est pas envisageable de lancer une sauvegarde
• Les sauvegardes sont-elles conservées de façon cyclique (la nouvelle => suppression ancienne)
• Le serveur SQL est-il dans un environnement multiserveur avec une administration centralisée
• La rétention que l'on souhaite (ex : avoir une version de base de données d'il y a une 2 semaine, il faudra un backup full d'il y a 2 semaines ou plus),
• Le temps qu'on a pour faire les backups, car il est quand même préférable de faire ça en période de faible activité,

Ma sauvegarde s'est bien terminé, bien qu'elle est pris un peu de temps.

J'ai d'ailleurs regarder l'espace utilisé pour chacune des bases :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

dbcc sqlperf(logspace) go   Database Name Log Size (MB) Log Space Used (%) Status master 1,242188 37,73585 0 tempdb 4,554688 51,80103 0 model 82,42969 98,67311 0 msdb 16,17969 39,06326 0 FRP_COMMON_toto 56,30469 91,24549 0 FRP_COMMUNICATION_toto 68134,99 0,6876593 0 FRP_TRESORERIE_toto 407,7422 3,782501 0

---

On voit que le model est presque à 100%.

D'autre part, bien que FRP_COMMUNICATION_toto ne prenne que 1% de l'espace utilisé, mon fichier FRP_COMMUNICATION_tot. idf est toujours à 66Go. Comment puis je faire pour le diminuer.

Voici les réponses à la politique de sauvegarde :

•Quelle est la taille de chacune des bases de données :
FRP_COMMON_toto 51Mo de DATA + 57 Mo de LOG
FRP_COMMUNICATION_toto 83 Mo de DATA + 66Go de LOG
FRP_TRESORERIE_toto 20 Mo de DATA + 408 Mo de LOG

•Quel est le volume des modifications de données : ca n'a pas l'air très volumineux

•Combien de temps la base de données peut-elle rester indisponible : Elle peut rester indisponible jusqu'à une demi journée, voir une journée.
•La perte de modifications est-elle cruciale : oui
•Est-il facile de recréer les données perdues : un peu compliqué, mais possible.
•Qu'elles sont les périodes importantes d'utilisation de la base de données : entre 08h00 et 17h00 - jours ouvrés
•La base subit-elle des surcharges de travail ponctuelles pendant lesquelles il n'est pas envisageable de lancer une sauvegarde : non
•Les sauvegardes sont-elles conservées de façon cyclique (la nouvelle => suppression ancienne) : oui, on sauvegarde tous les soirs et toutes les sauvegardes glissantes sur 5 semaines.
•Le serveur SQL est-il dans un environnement multiserveur avec une administration centralisée : centralisé
•La rétention que l'on souhaite (ex : avoir une version de base de données d'il y a une 2 semaine, il faudra un backup full d'il y a 2 semaines ou plus),
•Le temps qu'on a pour faire les backups, car il est quand même préférable de faire ça en période de faible activité : entre 18h00 et 08h00 du matin.

Merci pour votre aide.

En attendant à part de passer en base simple, comment puis je faire pour diminuer la taille du fichier idf. S'il n'y a un autre moyen, quelq'un peut il me donner les étapes, pour passer en mode simple, puis revenir de nouveau en version actuelle. Comment paramétrer la croissance automatique ?

1) tant que votre base est en mode de récupération (RECOVERY MODEL) FULL ou BULK LOGGED (complet ou journalisé en bloc) alors le fichier de sauvegarde empile les transactions, même si elles sont achevées depuis longtemps
ATTENTION : Le journal va donc grossir éternellement durant toute la vie de la base.
2) pour purger le journal de transaction vous devez impérativement faire une sauvegarde régulière dudit journal. Au moins une fois par jour, mais une stratégie plus intelligente consiste à la faire à une cadence plus rapide, par exemple toutes les heures, les dix minutes...
ATTENTION : une purge ne réduite pas la taille des fichiers. par exemple si vous purgez votre radiateur celui-ci ne se dégonfle pas !
3) pour réduire la taille d'un fichier (opération à ne faire que de manière très exceptionnelle !) il faut utiliser la commande DBCC SHRINKFILE...

A +

Donc si je comprends bien, je fais un backup de ma log, puis

comme ci dessous :

Code:

---

1 2 3 4 5 6 7 8

BACKUP LOG FRP_COMMUNICATION_toto TO DISK = 'C:\Backup_FRP_COMMUNICATION_toto_LOG.trn' go puis use FRP_COMMUNICATION_toto go DBCC SHRINKFILE ( XRT_COM_totoLog,100 ) go

---

C'est bien cela ? Pourquoi cette manipulation est exeptionnelle ? et si j'ai bien compris, ma taille de fichier va passer de 66Go à 100Mo.

L'informatique n'étant qu'une transposition du monde réel dans le virtuel, revenons au monde réel.

Considérons qu'une base de données est un parking dont les données sont des voitures....
Vous avez construit votre parking au fur et à mesure de l'arrivée des voitures... Vous auriez gagné du temps en dimensionnant correctement votre parking au début. Ceci est votre première erreur...

Vous avez commis une deuxième erreur en oubliant de noter le départ des voitures ce qui vous a conduit à rajouter de nouvelle places à chaque nouvelle entrée au lieu de recycler les anciennes places !

Maintenant vous avez décidé de casser 99% de votre parking, parce que vous considérez que la place est gaspillée... Mais cela ne risque t-il pas de se traduire à nouveau par une construction de place en fonction des voitures arrivant ?
C'est votre 3e erreur !

Autrement dit, un journal de transaction doit avoir une dimension suffisante pour absorber toutes les transactions entre deux sauvegardes dudit journal de transaction.

A +

Trés belle analogie :applo:

Citation:

---

Envoyé par SQLpro

Autrement dit, un journal de transaction doit avoir une dimension suffisante pour absorber toutes les transactions entre deux sauvegardes dudit journal de transaction.

---

Avec une marge, pour ne pas saturer lors des jours de match opérations exceptionnelles :aie:

Très belle analogie.

Donc si ma base ne fait que 80Mo, la taille de ma log devrait être aux alentours de 20Mo.

Et là je suis à 72Go.

j'ai lu que la commande DBCC SHRINKFILE permettait de diminuer la taille du fichier idf. Donc ma question est ? puis je lancer plusieurs fois cette commande.

D'autre part pour limiter la casse et arrêter que ma log ne grandissent, que dois je faire au niveau :
Pièce jointe 172738

Avant de repartir sur des bases saines, que me conseillerez vous ? pour limiter que la taille du log n'augmente.

Merci pour votre aide.

question pour les néophites, comment être sur que les données contenus dans mon journal transactionnel log a bien été intégrés dans ma base de données.

Bonjour,

Voici la commande que je souhaite lancer sur ma base de données.
Pourriez vous me donner les préconisations :
Je pense demander aux utilisateurs de se connecter, arreter l'instance, puis le démarrer. lancer un backup.
Et faire la manip ci-dessous.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

USE FRP_COMMUNICATION_toto; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE FRP_COMMUNICATION_toto SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (XRT_COM_totoLog, 1); GO   -- Reset the database recovery model. ALTER DATABASE FRP_COMMUNICATION_toto SET RECOVERY FULL; GO

---

Après que dois je faire pour que ma log transactionnel ne croit pas comme précédemment, en la vidant tous les soirs par exemple.

Merci pour vos réponses

Là je pense que vous devriez suivre un cours d’administration de SQL Server....

A +

C'est pas bon ?

Votre script (copier/coller de la MSDN) est "bon", dans la mesure où il va effectivement permettre de réduire la taille de votre fichier de log à 1 Mo.

Cependant, deux choses :
- Ceci est à lancer VRAIMENT PONCTUELLEMENT car met en péril la capacité à restaurer la base à partir des logs en cas de crash
- Réduire la taille du LOG à 1 Mo est stupide, puisqu'au bout de quelques minutes il fera une taille considérablement plus grande.

Donc déjà, commencez par décider d'une taille de log convenable.
Vu la taille de la base (82 Mo si j'ai bien suivi) il devrait rester assez petit. Mettez quand mêle 10 Mo pour être sûr qu'il ne va pas fragmenter sur le disque.

Ensuite, pour que le LOG ne grossisse pas sans arrêt, il faut le sauvegarder.

Si votre fichier de LOG est devenu aussi gros, c'est parce que vous ne le sauvegardez pas.

Que se passe-t-il si vous sauvegardez la base à minuit, et qu'un administrateur crame le serveur en voulant se dépêcher de terminer un truc à 18h15 ?
=> Vous perdez toute votre journée de travail ! Vraiment dommage...

Vous devez donc backuper (et conserver un historique) à intervalle très régulier (toutes les heures ou toutes les 10 minutes, par exemple).
=> Le contenu du fichier est alors marqué comme sauvegardé, et est libéré pour contenir de nouvelles données. Ainsi, la taille sur le disque ne grossi plus.

Vous serez alors capable, par la même occasion, de retrouver votre base de données dans son état de 18h14m59s, soit juste avant la connerie, ou au pire 18h05 au plus tôt, si vous avez un backup toutes les 10 minutes.

Merci pour toutes ce informations.

En fait, je vais passer la log à 20 MO.

Ou je me suis fait piéger, dans mon plan de maintenance, j'ai sauvegardé la base compléte, en pensant que les DATA + lol seraient sauvegardés.
Or je m'aperçois que seulement la DATA est sauvegardé, car seul la commande ci-dessous est référéncé dans la Transact SQL

Code:

---

1 2	BACKUP DATABASE [FRP_COMMON_TOTO] TO DISK = N'C:\PRODUITS\Microsoft .....

---

Pour sauvegarder la log, j'aurai du avoir :

Code:

---

1 2	BACKUP LOG FRP_COMMUNICATION_TOTO TO DISK

---

D'ailleurs est ce normal ?

Pièce jointe 172806

Quel commande faut-il passer pour sauvegarder les 2 en même temps.

Merci pour vos réponses.

Il faut faire un second plan de maintenance et faire un backup des journeaux de transactions. Ce sont 2 choses séparées.
Et faite s'en un 3ème, pour un backup DIFF. Ca ira plus vite en cas de restore.

Donc on est bien d'accord que lorsque l'on dit compléte, elle ne prend que la DATA et pas le journal des transactions ?

si c'est cela, ça m'a induit en erreur.

C'est un peu plus complexe que cela. La complète sauvegarde les données et le journal, mais ne le purge pas. Seule la sauvegarde du journal sauvegarde le journal et le purge !

A +