Comment rétablir le nombre de VLF de la base tempdb après redémarrage de l'instance ?

Bonjour,

Comment faut-il procéder pour rétablir à l'identique, et de manière automatique, le nombre de VLF de la base tempdb après un redémarrage de l'instance SQL Server ?

Imaginons le scénario suivant :
Le fichier log de la base tempdb fait 16 Go, redimensionné manuellement par pallier de 4 Go, (exemple 64 Mo puis 4 Go d'accroissement à chaque étape), soit au total 68 VLF (68 VLF = 4 VLF + ( (16 Go/4) * 16 VLF ) ) (1)

(1) : il s'agit de l'algorithme applicable jusqu'à SQL Server 2012 inclus. Pour SQL Server 2014 et supérieur l'algorithme a changé, mais le problème soulevé reste le même !

Selon ce scénario, nous avons des fichiers VLF de taille moyenne chacun de 240 Mo. On est bien dans le domaine du raisonnable, c.à.d. dans la zone verte.

Au redémarrage de l'instance, la base tempdb est recréée avec seulement 16 VLF, (soit 16 fichiers VLF de taille moyenne chacun de 1024 Mo), et on perd le paramétrage préalablement défini ci-dessus, (soit 68 fichiers VLF d'une taille moyenne chacun de 240 Mo). En effet, SQL Server ne permet pas aux administrateurs de définir le nombre de VLF pour la base tempdb au démarrage de l'instance.

Remarque :
D'après mon analyse entre 512 Mo et jusqu'à 8 Go pour le fichier log, c'est moins problématique parce qu'après un redémarrage, même si on perd le paramétrage et on repart sur 16 fichiers VLF, on reste dans une zone verte c.à.d la taille du fichier VLF individuel variera entre 32 Mo et et 512 Mo. On est bien dans la zone raisonnable, ce que moi j'appelle la zone verte.

En revanche pour les gros fichiers log dont la taille peut excéder plusieurs centaines de Go, je pense que cela peut être problématique de repartir sur 16 VLF après redémarrage (?)
Imaginez par exemple un fichier de log de 400 Go doté de seulement 16 VLF soit des fichiers VLF de taille moyenne de 25 Go chacun ! Cette configuration ne deviendrait-elle pas problématique ? j'ai de sérieux doutes (?).

Donc je reviens à ma question initiale : "Comment faut-il procéder pour rétablir à l'identique, et de manière automatique, le nombre de VLF de la base tempdb, après un redémarrage de l'instance SQL Server ?"


Merci pour vos réponses éclairées.

A+

Bonjour,

A priori, je ne vois qu'un script. Par exemple, tu crées un script qui redimensionne plusieurs fois le log de tempdb, et tu le mets dans un travail de l'agent SQL planifié pour s'exécuter au démarrage (c'est une option de planification disponible).

Je ne pense pas que ça te donne une garantie de l'exact nombre de VLF, mais tu t'approches, et je ne verrais pas de raison de vouloir retrouver le nombre exact de VLF.

Bonjour Rudi Bruchez

Et Merci beaucoup pour cette réponse ingénieuse.

Oui, effectivement, tu as raison, le but premier n'est pas de retrouver l'exact nombre des VLF, mais du moins de s'en approcher le plus possible et de retrouver un paramétrage presque similaire au paramétrage initial.

Tu as raison aussi concernant cette option de planification, de viens de vérifier, il existe une option de "schedule type" qui permet de lancer une tâche planifiée au démarrage de l'Agent SQL Server, mais si j'ai bien compris d'après l'intitulé de cette option, il s'agit à priori du démarrage de l'Agent SQL Server et non pas du démarrage de l'instance SQL server (?). Bon il faut que je fasse des tests grandeur nature et je te dirais ce qu'il en est.

Merci.

A+

ça revient pratiquement au même, parce que redémarrer le service SQL Server entraîner le redémarrage du service de l'agent SQL

Oui, mais on peut redémarrer l'Agent sans redémarrer l'Instance.

Et, c'est bien ce que je craignais, je viens de faire le test et le script est exécuté à chaque démarrage ou redémarrage de l'Agent SQL Server, et ce, même si l'Instance ne redémarre pas, ce qui est bien conforme à l'intitulé et à la définition de cette option.

Même si je rajoute une technique permettant de détecter ou distinguer le redémarrage ou non récent de l'instance lors de démarrage de l'Agent SQL Server, cela me parait présenter de réels risques. En effet, redimensionner le fichier log de tempdb, est une affaire souvent délicate dès lors que le Serveur est fortement sollicité etc..

J'ai gardé ton idée ingénieuse mais en utilisant une procédure stockée topée pour être exécutée au démarrage de l'instance et uniquement au démarrage de l'instance. J'ai donc finalement regroupé le script dans une procédure stockée nommée dbo.RetablirVLF puis marqué la dite procédure pour être exécutée automatiquement au démarrage de l'instance.

Code:

---

1 2 3 4 5

EXEC sp_procoption @ProcName = 'dbo.RetablirVLF' , @OptionName = 'startup' , @OptionValue = 'on' ;

---

Merci Rudi Bruchez pour ton aide,

A+

Vous devriez mettre un temps de latence important dans cette procédure (au moins 5 secondes) car rien ne dit que la base tempdb sera opérationnelle et créée dès le démarrage de l'instance, les objets comme la tempdb se construisant au fur et à mesure et en particulier cela peut durer assez longtemps si vous avez dimensionné la tempdb de façon importante...
Regardez donc dans le journal d'événement de SQL Server a quel moment il voit la tempdb... (Starting up database 'tempdb'.)

A +

Bonjour SQLpro,

Et Merci pour cette remarque très intéressante et très subtile.

J'ai effectué des tests de faisabilité sur une instance locale où la taille des fichiers data et log ne sont pas importantes (quelque centaines de méga octets). Le principe de redimensionnement des VLF, fonctionne bien, et donc pour les tests que j'ai effectués en local (instance locale) je n'ai pas eu besoin de rajouter un temps de latence.
Quand vous pouvez le constater ci-dessous dans le journal d'événement de SQL Server, présentés dans l'ordre chronologique croissant, la procédure RetablirTempdbVLF (que j'ai renommée , elle s'appelait RetablirVLF dans les posts précédents) est toujours exécutée en dernier, et ce, une fois que toutes les bases systèmes et/ou utilisateurs eurent été initialisées ainsi que d'autres initialisations diverses eurent été également effectuées.

Code:

---

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

Starting up database 'master'. Starting up database 'msdb'.   Starting up <bases utilisateur ..> Starting up <bases utilisateur ..> Starting up <bases utilisateur ..>   Starting up database 'model'. Clearing tempdb database. Starting up database 'tempdb'.   Launched startup procedure 'RetablirTempdbVLF'.

---

Maintenant, comme je l'ai dit, je trouve votre remarque très intéressante et j'aurais peut être besoin de rajouter une latence dans d'autres contextes où comme vous le sous-entendez, dès lors que la base temps est dimensionnée de façon importante, ce qui sera forcément le cas dans certains environnement de production. Donc à vérifier.
Ceci dit, il n'est pas non plus exclu qu'aucune latence ne sera nécessaire si les procédures topées pour être exécutée automatiquement au démarrage de l'instance, ne sont exécutées qu'une fois la base tempdb est opérationnelle (c.à.d qu'une fois que toutes les initialisations inernes systèmes eurent été terminées (?))

Votre remarque est aussi intéressante d'autant plus que dans le journal des événements, je vois des initialisations de certaines bases de données utilisateurs effectuées avant l'initialisation de la base système tempdb ! Est-ce que cela peut avoir un lien ou un impact avec notre sujet (?). Je ne sais pas (?).

Conclusion :
J'ai bien pris note de votre remarque, pour le cas échéant rajouter une latence d'au moins 5 secondes.

Merci.

A+

Ce qui serait idéal c'est que les fichiers de journalisation soient créés de la même manière que les fichiers de données si l'IFI est activé (Instant File Initialisation)

A +

Citation:

---

Envoyé par SQLpro

Ce qui serait idéal c'est que les fichiers de journalisation soient créés de la même manière que les fichiers de données si l'IFI est activé (Instant File Initialisation)

---

Bonjour SQLPro,

Qu'entendez-vous exactement par cette phrase ? J'avoue ne pas bien saisir le sens exact de cette phrase (?)
- En ce qui concerne le fichier de journalisation, dans le cas présent, j'ai un seul fichier de journalisation, redimensionné par la procédure stockée dbo.RetablirTempdbVLF, objet de cette discussion.
- En ce qui concerne les fichiers de données, il y a 4 Fichiers de données créés à l'identique (taille initiale, autogrow, etc), et ce, en tenant compte du nombre de processeurs logiques etc.
- l'IFI est déjà activé. l'IFI même activé, n'a aucun impact sur les fichiers des journaux de transactions, pour ces derniers, il y a toujours une Initialisation, c.à.d il y a toujours un "zeroing". l'IFI est utile uniquement pour les fichiers de données.
- Les fichiers de journalisation, dans le cas présent il y en a qu'un, n'ont évidemment pas les mêmes caractéristiques (taille initiale, autogrow, etc. ) que les fichiers de données au nombre de 4.

Merci pour vos éclaircissements.

A+

Citation:

---

Envoyé par hmira

l'IFI même activé, n'a aucun impact sur les fichiers des journaux de transactions, pour ces derniers, il y a toujours une Initialisation, c.à.d il y a toujours un "zeroing". l'IFI est utile uniquement pour les fichiers de données.

---

C'est justement cela le problème. Si les fichiers du JT pouvaient être initialisés immédiatement, on aurait pas besoin de temporiser la création de la base.

A +

Bonjour SQlpro,

OK. Maintenant je comprends mieux le sens votre remarque. Parce qu'avant je croyais, qu'il fallait que j'ajuste d'autres paramétrages de fichiers etc. Autres paramétrages que j'aurais oubliés.

En réalité, vous faites un constat sur le fonctionnement interne de l'initialisation des fichiers des journaux de transactions qui ne bénéficient pas de l'IFI même activé. Constat et souhait que je partage entièrement.

Sur ce point, traitant des JT et de l'IFI, on peut peut-être espérer une évolution et améliorations dans les prochaines version de SQL Server. A moins que le mode fonctionnent interne des JT, selon des aspects purement techniques, exigent une initialisation (?). Ce point est quand même important et est en lien avec le RTO.

Merci,

A+

Citation:

---

Envoyé par hmira

Sur ce point, traitant des JT et de l'IFI, on peut peut-être espérer une évolution et améliorations dans les prochaines version de SQL Server. A moins que le mode fonctionnent interne des JT, selon des aspects purement techniques, exigent une initialisation (?). Ce point est quand même important et est en lien avec le RTO.

---

En effet, le fichier de JT étant séquentiel, il faut l'initialiser. Le fichier de données est alloué avec des mécanismes de pages d'allocation interne, donc le moteur de stockage sait quelles sont les zones allouées du fichier de données. Il n'a pas cette connaissance pour le JT.

Merci Rudi Bruchez pour ces explications complémentaires, techniques, forts intéressantes.

A+