réduire la taille du log de la BDD templog.ldf

[The_macrafT]

Bonjour à tous, merci de prendre le temps de m'aider à résoudre mon problème
(mon problème est dans le cadre pro)

J'ai un système Windows serveur 2003 avec MSSQL 2003.

Le système développé est entièrement fonctionnel depuis maintenant près de 8 mois et récemment le client me tel en se plaignant de lenteur du système.

Après diagnostique voila les conclusion :

Espace disque C: < 5Mo
source du problème fichier « C:\ Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\Data\ templog.ldf » = 95Go

Je n'ai pas de besoin direct de ce fichier log ... Apparement il est réinitialisé à chaque redémarrage du serveur (taille = 5Mo)

Le redémarrage ma permis de pallier temporairement au problème mais bon, dans 8 mois on me rappellera encore et puis c pas propre ...

bref Sauriez vous quelles solutions seraient applicable pour résoudre cette situation ?

Merci à vous

[elsuket]

Bonjour,

Vous pouvez proposer de déplacer les fichiers de TempDB vers d'autres disques à l'aide de l'instruction suivante suivie d'un redémarrage :

Code :

1
2
3
4
5
6
7
8
9
USE master
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'X:\tempdb.mdf')
GO
 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'Y:\tempdb.ldf')
GO

Pour de meilleures performances, si cela vous est possible, essayez de dédier un disque à chacun de ces fichiers.
Veillez également à séparer les fichiers de données des fichiers des journaux de transactions des bases de données héberges par l'instance en question.

Essayez également de voir quelles sont les requêtes qui ont recours à l'utilisation de TempDB (utilisation de variables de type TABLE, tables temporaires, tris (DISTINCT ou UNION sans ALL, GROUP BY, tables de hachage, réindexations, ...)

@++

[The_macrafT]

Merci pour tes conseils.

Grand merci pour le code aussi jvais pouvoir limiter la charge sur le disque C:

C'est vrais que j'ai d'autres lecteurs à grosse capacité mais cela n'empêcherais pas que cette base grossisse ?
Pensez-vous qu'un plan de maintenance avec un shrink de la bdd réduirait la taille de celle-ci ?

J'ai énormément de requette de type "join, table, order, etc ..." car je fais beaucoup de reporting horaire, journalier,mensuel etc ... sur plus de 50 unitées différentes (soit environ 50 rapports par heures contenant souvent plusieurs des ces fonctions) et pas mal de fonction d'inetgration de mesure etc ...

Donc j'en suis très dépendant ...

Donc le déplacement des bases pourrais convenir, mais je ne pense pas que se soit une finalité en soit. Pouvons nous limiter la taille de cette base ? ou le plan de maintenance est possible ?

Merci

[mikedavem]

Un petit complément aux propos de Elsuket :

- Dédier des disques ou une architecture disque rapide pour tempdb. Cette base est surtout sollicité en écriture.

Si votre base tempdb grossit, c'est peut être normal mais vous pouvez utiliser les vues systèmes appropriés et compteurs qui permettront de voir ce qui utilise cette base.

Pensez-vous qu'un plan de maintenance avec un shrink de la bdd réduirait la taille de celle-ci ?

L'utilisation de la commande SHRINK est à proscrire surtout si vous le faites de manière récurrente. C'est une opération qui doit respecter exceptionnelle car elle a des conséquence sur les performances de votre base.

J'ai énormément de requette de type "join, table, order, etc ..." car je fais beaucoup de reporting horaire, journalier,mensuel etc ... sur plus de 50 unitées différentes (soit environ 50 rapports par heures contenant souvent plusieurs des ces fonctions) et pas mal de fonction d'inetgration de mesure etc ...

Donc j'en suis très dépendant ...

Donc le déplacement des bases pourrais convenir, mais je ne pense pas que se soit une finalité en soit. Pouvons nous limiter la taille de cette base ? ou le plan de maintenance est possible ?

Merci

Encore une fois voyez les requêtes qui utilisent le plus tempdb. Vu que vous êtes très dépend de votre activité ... il faudra commencer par optimiser en amont ... c'est à dire vos requêtes .. L'expansion de tempdb n'est que la conséquence du problème (si problème il y a ..)

++

[The_macrafT]

L'utilisation de la commande SHRINK est à proscrire surtout si vous le faites de manière récurrente. C'est une opération qui doit respecter exceptionnelle car elle a des conséquence sur les performances de votre base.

En fait je veux la faire de manière récurante mais Tou les 6 mois par exemples.


Alors j'ai réussi à mettre les deux bases sur deux hdd différents nickel et sa à l'aire de fonctionner

Encore une fois voyez les requêtes qui utilisent le plus tempdb. Vu que vous êtes très dépend de votre activité ... il faudra commencer par optimiser en amont ... c'est à dire vos requêtes ..

bien en fait c'est un soft industriel qui me permet de faire du reporting et certains des code sont "fermée". sinon comme indiqué il m'arrive de faire des "order by" et autre code de se genre.

(je comprend mieux l'utilité de tempdb.mdf maintenant :p )

donc sa va être très du de modifier (optimiser?) le code...

J'aurais vraiment besoin de savoir si il est possible de diminuer (pendant l'exploitation) la taille du fichier tempdb.ldf (sachant que la taille de tempdb.mdf reste accéptable)

mercii

[mikedavem]

oui il est possible de faire un truncate de votre fichier log en exploitation ..

Cependant si votre fichier journal est amené à regrossir à nouveau vous allez crééer de la fragmentation physique de votre fichier ... Il vaut mieux réserver une bonne fois pour toute l'espace nécessaire que de tenter de réduire même tous les 6 mois le journal. En plus celui-ci se vide automatiquement car le mode de récupération de cette base est en SIMPLE.

++

[The_macrafT]

Envoyé par mikedavem

En plus celui-ci se vide automatiquement car le mode de récupération de cette base est en SIMPLE.

Quand dis qu'elle se vide automatiquement tu veux dire au redémarrage du serveur ?

[Jinroh77]

Elle se vide à chaque fin de transaction.
Attention, les informations se vide mais l'espace disque réservé, lui, reste inchangé. C'est le contenu qui est effacer, non le contenant.

[The_macrafT]

Envoyé par Jinroh77

Elle se vide à chaque fin de transaction.
Attention, les informations se vide mais l'espace disque réservé, lui, reste inchangé. C'est le contenu qui est effacer, non le contenant.

Merci

Comment dois-je faire alors pour adapter le contenant au contenu lol

[Jinroh77]

Il n'y a aucun intérêt à cela.
Pour comprendre,
Dans tous les composants participant au temps d'exécution d'un traitement sur un ordinateur en général, les accès disques sont ce qu'il y a de plus lent. le facteur entre en accès disque et mémoire est de 1000 voir plus.

Or si votre fichier .ldf, dont la base est en mode de récupération simple, atteint un jour la taille de 10Go, c'est qu'une transaction unique a nécessitée cette espace.

Au moment de ce besoin, le moteur de base de données a demandé au système de lui allouer ces 10Go d'espace sur le disque. Si on rajoute le fait que votre base n'est pas forcément bien paramétrée, il a demandé cela par palier de 10mo...
Chaque allocation d'espace disque coûte énormément de temps au moteur de base de données.

Si à la fin de l'opération, vous réduisez la taille physique de votre fichier à ce qu'il reste dedans (c'est à dire rien), à la prochaine opération nécessitant autant d'espace, le moteur va re-perdre ce temps très important pour re-allouer l'espace disque.
Par contre, si le fichier n'est pas réduit (commande SHRINK), le contenant représentant toujours 10go, la transaction suivante qui aura besoin de 1àgo d'espace n'aura pas à perdre le temps de réallouer l'espace sur le disque, il sera déjà réservé sur le disque. d'où un gain important de performance.


Enfin, comme l'explique mikedavem il faut se poser la question de l'identification de la transaction qui a fait autant augmenter la taille de votre fichier.


Explication un peu longue mais que j'espère claire ?

[Papy Normand]

Bonjour,

Si j'ai bien compris cette discussion, elle concerne la place prise par le fichier log de la base tempdb de l'instance SQL server utilisée par SharePoint.
Serait-il possible de redémarrer cette instance ?
Si j'ai bien compris les explications données par Kaleen Delaney dans son livre Inside Microsof SQL Server 2005 : the Storage Engine ( pages 135/136 ), la base tempdb est automatiquement recrée à la taille de la base Model quand on redémarre l'instance SharePoint. De plus, celà permettrait d'utiliser un nouveau fichier log pour l'instance ( ce fichier log devient illisible quand il est trop grand ).
J'ai souvent vu des gens se plaindre de la place prise par les fichiers .ldf de tempdb et des fichiers log de l'instance parce qu'ils n'avaient jamais redémarré leur instance depuis plus d'un an.La seule solution que je connaisse est le redémarrage du service SQL Server associé à l'instance.

Bonne journée

[Jinroh77]

Envoyé par Papy Normand

Bonjour,

Si j'ai bien compris cette discussion, elle concerne la place prise par le fichier log de la base tempdb de l'instance SQL server utilisée par SharePoint.
Serait-il possible de redémarrer cette instance ?
Si j'ai bien compris les explications données par Kaleen Delaney dans son livre Inside Microsof SQL Server 2005 : the Storage Engine ( pages 135/136 ), la base tempdb est automatiquement recrée à la taille de la base Model quand on redémarre l'instance SharePoint. De plus, celà permettrait d'utiliser un nouveau fichier log pour l'instance ( ce fichier log devient illisible quand il est trop grand ).
J'ai souvent vu des gens se plaindre de la place prise par les fichiers .ldf de tempdb et des fichiers log de l'instance parce qu'ils n'avaient jamais redémarré leur instance depuis plus d'un an.La seule solution que je connaisse est le redémarrage du service SQL Server associé à l'instance.

Bonne journée

En redémarrant une instance SQL vous ne solutionnez absolument pas le problème.
Vous réalisez une opération pompier sans prendre en considération tous les effets indésirables.

Redémarrer une instance SQL provoque beaucoup plus de choses que vous ne semblez imaginer.
Alors que si l'on fait en sorte que la tempDB reste dans une taille correcte ou au pire de l'opération pompier, on réduit physiquement la taille du fichier de log, les effets indésirables ne seront pas du tout les mêmes.

[dbaffaleuf]

Envoyé par Papy Normand

la base tempdb est automatiquement recrée à la taille de la base Model quand on redémarre l'instance SharePoint. De plus, celà permettrait d'utiliser un nouveau fichier log pour l'instance ( ce fichier log devient illisible quand il est trop grand ).
J'ai souvent vu des gens se plaindre de la place prise par les fichiers .ldf de tempdb et des fichiers log de l'instance parce qu'ils n'avaient jamais redémarré leur instance depuis plus d'un an. La seule solution que je connaisse est le redémarrage du service SQL Server associé à l'instance.
Bonne journée

tempdb est recréé à sa taille initiale par défaut (8Mb). Model fait 2Mb par défaut. Model ne sert qu'à importer des objets systèmes comme dans toute création de base, et notamment lors de la séquence de startup pour tempdb. Et pour recycler le fichier ERRORLOG, tu n'es pas obligé de redémarrer l'instance (sp_cycle_errorlog).

Le contrôle de la taille des journaux de transactions sur disque ne dépend que du mode de récupération des transactions de la base concernée, et de l'espace nécessaire pour jouer les transactions, sur tempdb comme sur les autres bases. Il faut identifier quelles transactions sont ouvertes dans tempdb et pourquoi elles consomment autant d'espace. Redémarrer une instance devrait être la dernière option à considérer (on repart avec un buffer pool vide, il faut recompiler tous les plans et tous les compteurs exposés par les DMV sont aussi flushés).