Discussion :

[Tagginfo]

Nous avons placé une alerte sur la taille du log de Tempdb à 80 Go.
Nous avons bien reçu l'alerte dès le dépassement du seuil chaque minute comme prévu.

La taille de données (datafiles) est de 80 Go.
Le % d'espace non alloué était de presque 100% dans les data et de presque 0% dans le log.

Persuadé d'obtenir une erreur nous avons lancé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

dbcc shrinkfile (templog, 1000)

Or le fichier c'est réduit à moins de 90% de sa taille initiale

Il semblerait que le journal de Tempdb devrait se purger à 70% de l'espace occupé dans le log.

Questions :

1. Si c'est pas possible à 70%, est-ce automatiquement retenté par la suite ?
2. Comment suivre les "purges" du log de tempdb ?
3. Quelqu'un a déjà eu ça ?

[SQLpro]

Il n'est déjà pas normal que le journal des transactions de tempdb soit aussi gros que les données... 80 Go me parait hallucinant. Il doit y avoir de très mauvaises pratiques dans l'application.

DBCC SHRINKFILE fait que qu'il peut en fonction de la taille allouée. Si de nombreuses transactions sont en cours dans tempdb (ce que je soupçonne) alors il devient impossible de supprimer les parties du journal comportant ces données. Comme les espaces de stockages sont organisés en "extents" c'est à dire des blocs de 8 pages de 8ko contiguës alors il suffit qu'une seule des pages ait une données verrouillée par une transaction pour que l'extent ne puisse être libéré.

À vu de nez, je dirais qu'il est probable que le style de codage du développement doit être farci des tables temporaires utilisées outrancièrement dans des transactions gigantesques....

A +

[Tagginfo]

@SQLpro : merci d'avoir pris le temps de répondre.

Rassure toi ton nez a bonne vue : oui le code est farci de table temporaires et qui plus est la volonté locale est d'utiliser le mode 'snapshot' (à la transaction et par défaut dans quelques bases)

Par contre il me semble que la description que tu fais des bloc et des extents concernent les fichiers de données.
Le problème ne concerne pas les fichiers de données mais le log.
Selon cet article : https://technet.microsoft.com/en-us/...=sql.110).aspx il est plutôt question de "virtual log".

Du coup la question reste entière.

[janlouk]

Nous avons placé une alerte sur la taille du log de Tempdb à 80 Go.
Nous avons bien reçu l'alerte dès le dépassement du seuil chaque minute comme prévu.

La taille de données (datafiles) est de 80 Go.

En fait, ce n'est pas très clair, tu parles de la taille du log de 80 GB et Datafiles de 80 GB. Ils sont tous les deux à 80 GB?

[Tagginfo]

@jeanlouk:

Pour faire une réponse rapide : oui

Plus précisément :
- Les datafiles de tempdb sont définis 80 Go
- le Log file de tempdb a atteint 80 Go suite à des accroissements successifs

Vu que ce n'est pas la première fois qu'on a le problème, il existe maintenant une alerte.

Actuellement le fichier de log n'a pas raugmenté depuis hier mais son taux d'occupation interne est plus important.

NB: l'ensemble de l'occupation des bases se compte en To.

[SQLpro]

@SQLpro : merci d'avoir pris le temps de répondre.

Rassure toi ton nez a bonne vue : oui le code est farci de table temporaires et qui plus est la volonté locale est d'utiliser le mode 'snapshot' (à la transaction et par défaut dans quelques bases)

Par contre il me semble que la description que tu fais des bloc et des extents concernent les fichiers de données.
Le problème ne concerne pas les fichiers de données mais le log.
Selon cet article : https://technet.microsoft.com/en-us/...=sql.110).aspx il est plutôt question de "virtual log".

Du coup la question reste entière.

Oui, oui, oui... Je suis à Prague, très jolie ville d'ailleurs, entre deux sessions d'audition des développeurs pour mon client... Donc répondu un peu trop rapidement...

À part une transaction de m... dans les tables temporaires, je voit pas ce qui pourrais provoquer cela. En effet, le mode d'isolation snapshot n'a presque pas d'influence sur la taille du JT de ladite tempdb....

A +

[Tagginfo]

Oui, oui, oui... Je suis à Prague, très jolie ville d'ailleurs,A +

Ah le printemps à Prague ... ça a des parfums d'histoire

[Tagginfo]

Des news !

On a repéré que le taux d'occupation du journal de Tempdb redescendait drastiquement suite à un Checkpoint.

On a envisagé de faire des alertes à 80%, 85% et 90% du taux d'occupation (70% devant être fait automatiquement...) mais n'ayant pas trouvé d'option permettant de limiter le checkpoint à une seule base on s'est résolu à exécuter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
USE [master]
GO
ALTER DATABASE [tempdb] SET TARGET_RECOVERY_TIME = 600 SECONDS WITH NO_WAIT
GO

Depuis (2 jours) le journal est maintenu à une taille ridicule !
Pourquoi qu'on ait du faire ça ?

[SQLpro]

En quelle version êtes vous ? Si c'est avec 2016 il y a eut un changement dans la gestion des CHECKPOINT.

Voyez si le fait de mettre la tempdb avec SQL ALTER DATATASE tempdb SET TARGET_RECOVERY_TIME = 0 SECONDS fait quelque chose.

A +

[Tagginfo]

Oui nous sommes en 2016.

Quelle pourrait être la plus value de passer à 0 (SQL ALTER DATATASE tempdb SET TARGET_RECOVERY_TIME = 0 SECONDS) ?

L'action actuelle n'est pas sans effets :

[SQLpro]

Celle de revenir à l'ancien mode de gestion des CHECKPOINT, plus basé sur le temps que sur la quantité…..

A +

[Tagginfo]

J'ai un doute ... c'est quoi la valeur par défaut ?
J'avoue que comme un c...n je n'ai pas noté de manière formelle la valeur AVANT modification.
De ce que je peux en déduire des autres instances 0 semble être la valeur par défaut.

Du coup franchement j'hésite.
On n'est pas à l'abri d'un réglage qui "tomberait en marche" suite au retour à la valeur par défaut, mais c'est pas suffisant pour changer les valeurs en production.
Du moins pour l'instant.

[hmira]

Bonjour,

Il vous faudra trouver un "compromis" entre au moins ces 4 indicateurs :
- (Tempdb) IOPS,
- (Tempdb) MB/s,
- (Tempdb) Latence,
- (Tempdb) Taille limite raisonnable du fichier du journal des transaction (tempdb Log). A priori, si j'ai bien compris, celle-ci ne doit dépasser 80 Go.

Vous pouvez :
- Soit revenir à la valeur par défaut, avant SQL Server 2016, c.à.d. TARGET_RECOVERY_TIME = 0, comme suggéré par SQLPro et voir si cela vous convient,
- Soit utiliser la valeur par défaut SQL Server 2016 c.à.d. 60 secondes (1 minute),
- Soit essayer d'autres valeurs comprises entre 60 secondes (1 minute) et 600 secondes (10 minutes). 600 secondes (10 minutes) correspond à la valeur que vous avez vous même indiquée.
Vous pouvez par exemple, pour TARGET_RECOVERY_TIME, essayer des valeurs comme :
120 secondes (2 minutes)
180 secondes (3 minutes)
240 secondes (4 minutes)
300 secondes (5 minutes)

A+

[Tagginfo]

Nous avions décidé de "voir" ce que le retour au paramétrage de base pouvait faire.
Pour l'instant l'occupation du log de tempdb semble être maitrisé.


Pour info voici ce que ça donne dans notre monitoring.