IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

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


Sujet :

Administration SQL Server

  1. #1
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut 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+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  2. #2
    Expert confirmé
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Points : 4 043
    Points
    4 043
    Par défaut
    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.
    Rudi Bruchez
    Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
    LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
    LIVRES : Optimiser SQL Server -
    Microsoft SQL Server 2012 Security Cookbook
    - les bases de données NoSQL

    e-learning : LinkedIn Learning - Pluralsight

  3. #3
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    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+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  4. #4
    Expert confirmé
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Points : 4 043
    Points
    4 043
    Par défaut
    ç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
    Rudi Bruchez
    Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
    LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
    LIVRES : Optimiser SQL Server -
    Microsoft SQL Server 2012 Security Cookbook
    - les bases de données NoSQL

    e-learning : LinkedIn Learning - Pluralsight

  5. #5
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    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 SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    EXEC sp_procoption 
     @ProcName = 'dbo.RetablirVLF' 
     , @OptionName = 'startup' 
     , @OptionValue = 'on' ;

    Merci Rudi Bruchez pour ton aide,

    A+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    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 SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    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+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  9. #9
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  10. #10
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    Citation Envoyé par hmira Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  11. #11
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    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+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

  12. #12
    Expert confirmé
    Avatar de rudib
    Homme Profil pro
    Fakir SQL Server & NoSQL
    Inscrit en
    Mai 2006
    Messages
    2 573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Fakir SQL Server & NoSQL

    Informations forums :
    Inscription : Mai 2006
    Messages : 2 573
    Points : 4 043
    Points
    4 043
    Par défaut
    Citation Envoyé par hmira Voir le message
    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.
    Rudi Bruchez
    Rudi Bruchez EIRL, solutions MS SQL Server et NoSQL
    LinkedIn - [Outil libre de diagnostic SQL Server : Sql Trismegiste]
    LIVRES : Optimiser SQL Server -
    Microsoft SQL Server 2012 Security Cookbook
    - les bases de données NoSQL

    e-learning : LinkedIn Learning - Pluralsight

  13. #13
    Membre expérimenté

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Points : 1 668
    Points
    1 668
    Billets dans le blog
    8
    Par défaut
    Merci Rudi Bruchez pour ces explications complémentaires, techniques, forts intéressantes.

    A+
    "Une idée mal écrite est une idée fausse !"
    http://hamid-mira.blogspot.com

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 8
    Dernier message: 18/04/2011, 15h46
  2. [JPopupMenu] Comment recupérer le nombre de d'item ?
    Par Cyber@l dans le forum Composants
    Réponses: 2
    Dernier message: 14/05/2004, 10h22
  3. Comment compter le nombre de lettre identique ?
    Par divableue dans le forum ASP
    Réponses: 3
    Dernier message: 07/11/2003, 16h01
  4. comment connaitre le nombre ...
    Par mythtvtalk.com dans le forum Requêtes
    Réponses: 9
    Dernier message: 04/08/2003, 09h18
  5. Comment Rétablir un FORM ?
    Par Xavier dans le forum C++Builder
    Réponses: 3
    Dernier message: 19/11/2002, 13h14

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo