Discussion :

[Mothership]

Bonjour,

J'aurai besoin d'éclaircissements sur la gestion des journaux de transaction...

Aujourd'hui, nous le sauvegardons fréquemment (toutes les 30 minutes sur une partition de notre serveur SQL), aucune action de SHRINK n'est effectuée. Nous procédons à des défragmentations/reconstructions d'indexes tous les jours pendant lequel nous passons la base en mode simple pour éviter une saturation des journaux (et l'envoi un SMS à notre collaborateur d'astreinte).
J'ai bien saisi que cette opération réinitialisait la chaîne de séquence, nécessitant une sauvegarde différentielle ou complète après l'opération.

Je ne souhaite plus utiliser le passage de la base en mode simple, même lors de lourds travaux de maintenance.
Je souhaite sauvegarder puis épurer fréquemment le contenu du fichier
Je ne souhaite pas non plus provoquer des extents du fichier de transaction, liés à un trop grand nombre de shrink...

J'ai dans l'idée d'avoir des fichiers d'une taille toujours identique, imaginons 2x 10 Go, et d'y faire le ménage régulièrement sans pour autant rendre l'espace au système. En période d'activité, je souhaite garder une sauvegarde toutes les 30 minutes voire plus fréquente, en période de maintenance la meilleure option serait peut être d'effectuer une ménage lorsque les données stockées atteignent 50 % de l'espace disque des fichiers.
Enfin je me demande aussi si l'exécution des sauvegardes des journaux pendant d'autres actions peut elle causer un problème (pendant une sauvegarde complète par exemple, une reconstruction d'indexes etc.)

Il est écrit par là que

En général, le journal des transactions est tronqué après chaque sauvegarde de journal conventionnelle

ou encore

La réalisation régulière de sauvegardes de journaux de transactions est nécessaire. Une sauvegarde de journal permet de restaurer non seulement les transactions sauvegardées, mais également de tronquer le journal pour supprimer les enregistrements de journaux sauvegardés du fichier journal.

or chez moi cela ne se produit pas. Je me demande si cela est du au fait que la réduction automatique n'est pas activée sur ma base de données (ce que l'on m'avait conseille à l'époque dans ce forum il me semble).

J'avoue que les sites du MSDN sont assez bien fait, mais ils manquent clairement d'exemple pour étayer les descriptions des divers commandes avec lesquelles je ne suis pas spécialement familiers.

Voilà comment je vois mon job de sauvegarde régulier en activité :
USE MaBasePRD
DBCC shrinkfile (MaBasePRD_log, NOTRUNCATE)
DBCC shrinkfile (MaBasePRD_log_2, NOTRUNCATE)

Cela vous parait il cohérent, ou il n'a peut être rien à faire d'autres que d'activer l'autoshrink sur MaBase

Pour la période hors activité, un peu d'aide pourrait m'être utile, je vois bien dans ma tête tarabiscoté une procédure stockée scrutant toutes les 10 minutes si le fichier est utilisée à plus de 50% mais je suis sur que quelqu'un à de meilleurs outils pour faire cela.

Merci

[Ptit_Dje]

Bonjour,

Lorsqu'il est dit que le fichier journal est tronqué, c'est la partie non-active du journal qui vient d'être sauvée qui est réutilisable dans le fichier. Cela n'a pas d'effet sur la taille du fichier lui-même.
Je n'ai jamais essayé sur une base de données définie en auto-shrink, c'est pas recommandé comme configuration effectivement !

Vous pouvez effectuer des sauvegardes du journal pendant la reconstruction d'index, pas de soucis. Comme cité plus haut, c'est la partie non-active du journal qui est sauvegardée, ce qui veut dire que les transactions encore ouvertes ne seront pas sauvegardées avant qu'elles ne soient commitées complètement (-> potentiellement à la prochaine sauvegarde).

Dans l'explication de votre job de sauvegarde, je vois diverses choses... Sauf un backup !!! Ne l'oubliez pas
Si vous avez de gros index à reconstruire, il se peut que le shrink soit non efficace de toute manière.
Vous pouvez eventuellement réduire la taille de vos fichiers, par contre ce n'est pas très performant et ils risquent de re-grossir d'eux même par la suite, l'idéal serait de pouvoir les laisser tranquillement la, surtout qu'avec une sauvegarde des logs toutes les 30 minutes, vous êtes bon (a priori, sans connaitre votre volume de transactions).

Bonne journée

[Mothership]

Dois je comprendre qu'une fois la partie non active du journal sauvegardée, ces données sont normalement écrasées par les nouvelles entrées ?
Concernant la gestion au quotidien, cela ne devrait donc pas poser de problèmes par contre lors d'une reconstruction d'un index, la taille de ces derniers étant énorme c'est pour cela que le journal croit de manière si significative.

Pour la partie maintenance, j'ai spécifié dans mes reconstructions d'indexes notamment le paramètre SORT_IN_TEMPDB = ON, cela ne devrait il pas empêcher justement les journaux de transaction de croitre de manière significative ?
Si cela ne se produit pas est ce la taille de ma base temp_db qui pourrait être en cause ? Ou comme j'ai cru le lire à droite ou à gauche je ne sais plus, cette option n'est utile que lors de la création d'un index et non sa reconstruction ?
Suis je obligé de lancer une sauvegarde du journal après chaque index reconstruit ?

Peut on utiliser la commande suivante pour calculer la taille des indexes d'une table ?

USE RFXCOOPQAS
EXEC sp_spaceused MaTable
name rows reserved data index_size unused
MaTable 9825822 16110456 KB 6538504KB 9560344KB 11608 KB

Les indexes semblent être reconstruits un par un et non tous en même temps sur une table données, cette valeur me parait erronée.

J'avoue être un peu perdu sur les solutions envisageables...

[Ptit_Dje]

Dois je comprendre qu'une fois la partie non active du journal sauvegardée, ces données sont normalement écrasées par les nouvelles entrées ?

Oui, c'est exactement ca.

Concernant la gestion au quotidien, cela ne devrait donc pas poser de problèmes par contre lors d'une reconstruction d'un index, la taille de ces derniers étant énorme c'est pour cela que le journal croit de manière si significative.

Effectivement

Pour la partie maintenance, j'ai spécifié dans mes reconstructions d'indexes notamment le paramètre SORT_IN_TEMPDB = ON, cela ne devrait il pas empêcher justement les journaux de transaction de croitre de manière significative ?

SQL Server va trier les données de l'index en utilisant TempDB si necessaire. Cependant, lorsqu'il va remplacer l'index dans la base de données principale, il doit garder une copie des pages modifiées dans le fichier de log, afin de pouvoir faire un rollback au cas ou. Le fichier log de votre DB sera sollicité.

Si cela ne se produit pas est ce la taille de ma base temp_db qui pourrait être en cause ? Ou comme j'ai cru le lire à droite ou à gauche je ne sais plus, cette option n'est utile que lors de la création d'un index et non sa reconstruction ?

Depuis BOL:

Determines where the intermediate sort results, generated during index creation, are stored.

When ON, the sort results are stored in tempdb. When OFF, the sort results are stored in the filegroup or partition scheme in which the resulting index is stored.

Note:
If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored.

Suis je obligé de lancer une sauvegarde du journal après chaque index reconstruit ?

Obligé, non.
Par contre ca devrait effectivement vider la portion non-active de votre fichier de log.

J'avoue être un peu perdu sur les solutions envisageables...

Vous pouvez comme vous le suggeriez au dessus implémenter une telle solution.
Dans BOL, un script permet de gérer la reconstruction/réorganisation des index. Adaptez le de manière à inclure un log backup entre chaque index si cela vous aide à maintenir une taille de fichier acceptable.

Bonne journée

[mikedavem]

Je ne souhaite plus utiliser le passage de la base en mode simple, même lors de lourds travaux de maintenance.

Vous avez tout à fait raison ... vous avez 2 solutions :
- Rester en mode de récupération COMPLET et dimensionner vos journaux de façon à ce qu'ils puissent traiter l'activité transactionnelle de votre base ainsi que les opérations de maintenance
- Passer en mode BULK_LOGGED qui permettra de minimiser l'enregistrement des opérations de réindexation dans le journal mais qui ne cassera pas la séquence des LSN ... ce qui vous évitera de refaire une sauvegarde complète.

Pour la partie maintenance, j'ai spécifié dans mes reconstructions d'indexes notamment le paramètre SORT_IN_TEMPDB = ON, cela ne devrait il pas empêcher justement les journaux de transaction de croitre de manière significative ?

Non car l'utilisation de cette option permet simplement de gérer les résultats de tris de données dans la base tempdb. Si votre tempdb est sur un autre axe physique cela peut accélerer la reconstruction de votre index mais les opérations seront journalisées de toute façon.

Je suppose que vous utilisez la tâche par défaut de reconstruction de vos index dans les plans de maintenance. En fonction de la volumétrie de vos données et de vos index orientez vous vers une opération de réindexation (reconstruction, réorganisation et mise à jour de statistiques) personnalisée à base de script, ce qui vous permettra de ne vous attaquer qu'aux index qui en ont réellement besoin.

Suis je obligé de lancer une sauvegarde du journal après chaque index reconstruit ?

Si l'intervalle des 30 min ne suffit pas pour empêcher les journaux de grossir démesurément, vous pouvez implémentez une alerte SQL Server qui détectera un seuil que vous aurez fixé de remplissage du journal et qui répondra par une sauvegarde automatique de ce journal ... ce qui peut éviter un accroissement trop important du journal. Vous avez cette possibilité ou comme cité plus haut passer en mode BULL_LOGGED pour minimiser les enregistrements dans le journal.

++

[Mothership]

BOL = ?
Book online ?

[Ptit_Dje]

Oui.
Voici un lien vers un script de reorganisation/rebuild d'index:
http://blogs.msdn.com/joaol/archive/...rver-2005.aspx

[SQLpro]

Bonjour,

J'aurai besoin d'éclaircissements sur la gestion des journaux de transaction...

Aujourd'hui, nous le sauvegardons fréquemment (toutes les 30 minutes sur une partition de notre serveur SQL), aucune action de SHRINK n'est effectuée. Nous procédons à des défragmentations/reconstructions d'indexes tous les jours pendant lequel nous passons la base en mode simple

pas bon placez le en mode BULK LOGGED, sinon vous perdez toute le bénéfice du mode FULL !

pour éviter une saturation des journaux (et l'envoi un SMS à notre collaborateur d'astreinte).
J'ai bien saisi que cette opération réinitialisait la chaîne de séquence, nécessitant une sauvegarde différentielle ou complète après l'opération.

Ce que le BULK LOGGED vous aurais évité !

Je ne souhaite plus utiliser le passage de la base en mode simple, même lors de lourds travaux de maintenance.
Je souhaite sauvegarder puis épurer fréquemment le contenu du fichier
Je ne souhaite pas non plus provoquer des extents du fichier de transaction, liés à un trop grand nombre de shrink...

Si vous sauvegardez régulièrement le JT à l'aide de BACKUP LOG, la partie sauvegardée du JT sera réutilisé. En outre en mode BULK LOGGED les opération de reconstruction/defrag d'index ne sont pas journalisées.

J'ai dans l'idée d'avoir des fichiers d'une taille toujours identique, imaginons 2x 10 Go, et d'y faire le ménage régulièrement sans pour autant rendre l'espace au système.

Toujours identique = utopie (par exemple le jour un un gros bacth de mise à jour de structure sera lancé il vous sera impossible de maîtriser la volume enregistré dans le JT). En revanche en surveiller l'utilisation oui, c'est de la bonne pratique !

En période d'activité, je souhaite garder une sauvegarde toutes les 30 minutes voire plus fréquente

De quel type? Complète ?? Diff ???, LOg ???????

, en période de maintenance la meilleure option serait peut être d'effectuer une ménage lorsque les données stockées atteignent 50 % de l'espace disque des fichiers.

Il ne faut pas raisonner en ces terme, et vous pouvez aller jusqu'à 70% de taux d'occupation des disques sans problème.

Enfin je me demande aussi si l'exécution des sauvegardes des journaux pendant d'autres actions peut elle causer un problème (pendant une sauvegarde complète par exemple, une reconstruction d'indexes etc.)

Non, c'est unn opération d'une extrême simplicité. mais il ne faut pas que d'autres sauvegardes interfèrent ni que de grosses transactions aient lieux (rebuild d'index par exemple)

[/QUOTE]Il est écrit par là que
ou encore or chez moi cela ne se produit pas. Je me demande si cela est du au fait que la réduction automatique n'est pas activée sur ma base de données (ce que l'on m'avait conseille à l'époque dans ce forum il me semble).

J'avoue que les sites du MSDN sont assez bien fait, mais ils manquent clairement d'exemple pour étayer les descriptions des divers commandes avec lesquelles je ne suis pas spécialement familiers.

Voilà comment je vois mon job de sauvegarde régulier en activité :
USE MaBasePRD
DBCC shrinkfile (MaBasePRD_log, NOTRUNCATE)
DBCC shrinkfile (MaBasePRD_log_2, NOTRUNCATE)

Le shrinkfile est la pire des choses. Lisez ce que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p5...fichiers-et-t/

Cela vous parait il cohérent, ou il n'a peut être rien à faire d'autres que d'activer l'autoshrink sur MaBase
[quote]
Non plus. Il faut que vous trouviez le bon équilibre en terme de taille des fichiers pour n'avoir jamais aucune opératioon ni de croissance ni de décraoissance. C'est ça le travail du DBA !

Pour la période hors activité, un peu d'aide pourrait m'être utile, je vois bien dans ma tête tarabiscoté une procédure stockée scrutant toutes les 10 minutes si le fichier est utilisée à plus de 50% mais je suis sur que quelqu'un à de meilleurs outils pour faire cela.

Merci

Non, pas comme ça !

A +

[SQLpro]

Dois je comprendre qu'une fois la partie non active du journal sauvegardée, ces données sont normalement écrasées par les nouvelles entrées ?

Oui, il y a reprise de la partie morte du journal. En ce sens il est écrit de manière circulaire !

Concernant la gestion au quotidien, cela ne devrait donc pas poser de problèmes par contre lors d'une reconstruction d'un index, la taille de ces derniers étant énorme c'est pour cela que le journal croit de manière si significative.

Donc encore une fois BULK LOGGED

Pour la partie maintenance, j'ai spécifié dans mes reconstructions d'indexes notamment le paramètre SORT_IN_TEMPDB = ON, cela ne devrait il pas empêcher justement les journaux de transaction de croitre de manière significative ?

Aucune incidence sur le journal, mais cela pénlise les perf. En effet SORT in TEMPDB fait que le tri est journalisé dans la tempdb alors qu'il pourrait être fait en mémoire ce qui irait plus vite.
On choisit cette oprtion lorsque :
1) la base travaille 24h/24 sans jamais de termps mort
2) on est short en RAM

Si cela ne se produit pas est ce la taille de ma base temp_db qui pourrait être en cause ? Ou comme j'ai cru le lire à droite ou à gauche je ne sais plus, cette option n'est utile que lors de la création d'un index et non sa reconstruction ?
Suis je obligé de lancer une sauvegarde du journal après chaque index reconstruit ?

Vous ête à côté de la plaque. Voir ci avant !

Peut on utiliser la commande suivante pour calculer la taille des indexes d'une table ?

USE RFXCOOPQAS
EXEC sp_spaceused MaTable
name rows reserved data index_size unused
MaTable 9825822 16110456 KB 6538504KB 9560344KB 11608 KB

pas glop car asynchrone ! Mieux vaut utiliser les tables systèmes

Les indexes semblent être reconstruits un par un et non tous en même temps sur une table données, cette valeur me parait erronée.

J'avoue être un peu perdu sur les solutions envisageables...

Il serait peut être temps de prendre un petit cours d'Admin SQL Server ?

A +

[Mothership]

Il serait peut être temps de prendre un petit cours d'Admin SQL Server ?

Une formation SQL server, j'en ai fait une "dense" de 4,5 jours en suivant du cours MSFT pur, l'administration s'est limitée en gros aux sauvegardes (full, diff et la restauration) ainsi que la sécurité de la BD (post installation). Le reste n'ont été que des cours sur la création de projet et des DB associées ce n'était pas inintéressant mais pas du tout ce que j'en attendais. Bref, c'est pour ça que je browse un peu le web et recherche des réponses de personnes dans ce forum par exemple à ce sujet . Il est évident que ma formation sur le sujet est incomplète.
Merci à tous d'ailleurs pour vos réponses.

Pour le mode BULK LOGGED je vais tester et ainsi rayer mes questions concernant la gestion des périodes de maintenance ou plutôt de faible activité sur ma base.

Pour les indexes et leur reconstruction j'utilise des procédures stockées créées lors d'une prestation d'un certified SQL (avant ma arrivée dans l'entreprise). Je les ai depuis adapté avec "l'aide" du blog de Pinale Dave et quelques autres ressources sur le oueb. Malheureusement le switch en mode simple avant la reconstruction ne faisait pas partie de mes modifications. Cela est pour bientôt

Pour la partie SORT_IN_DB ainsi que d'autres paramètres, ils sont eux aussi hérités de ces procédures, je vais y mettre un terme et revoir tout cela en m'aidant du blog cité par Ptit_Dje

Merci à tous pour ces infos

[SQLpro]

Au lieux d'aller voir en anglais de mauvais blog, lisez les articles que j'ai écrit sur la défrag des index des SGBDR. En plus c'est en français !
http://sqlpro.developpez.com/optimis...ntenanceIndex/
http://blog.developpez.com/sqlpro/p8...-des-statisti/
...

A +

[Mothership]

Sortez vos bottes aujourd'hui parce que ça caille par ici, on est pas à Miami...

J'ai l'impression d'être bien habillé pour l'hiver


merci