Skip to content

Pourquoi le journal des transactions continue-t-il de croître ou manque-t-il d'espace ?

C'est la réponse la plus exacte que nous puissions vous donner, cependant, observez-la attentivement et analysez si elle s'adapte à votre travail.

Solution :

Une réponse plus courte :

Vous avez probablement soit une transaction de longue durée en cours d'exécution (maintenance d'un index ? suppression ou mise à jour d'un gros lot ?), soit vous êtes dans le mode de récupération " par défaut " (plus bas sur ce qu'on entend par défaut) de Full et vous n'avez pas pris de sauvegarde du journal (ou ne les font pas assez fréquemment).

Si c'est un problème de modèle de récupération, la réponse simple pourrait être Switch to. Simple mode de récupération si vous n'avez pas besoin de récupération ponctuelle et de sauvegardes régulières des journaux. Beaucoup de gens, cependant, font de cette réponse leur réponse sans comprendre les modèles de récupération. Lisez la suite pour comprendre pourquoi cela est important, puis décidez de ce que vous allez faire. Vous pouvez également commencer à effectuer des sauvegardes de journaux et rester en mode de récupération Full . Fullrécupération.

Il pourrait y avoir d'autres raisons, mais ce sont les plus courantes. Cette réponse commence à plonger dans les deux raisons les plus courantes et vous donne des informations de base sur le pourquoi et le comment derrière les raisons ainsi que d'explorer certaines autres raisons.


Une réponse plus longue :
Quels sont les scénarios qui peuvent faire en sorte que le journal continue de grossir ? Il y a beaucoup de raisons, mais généralement ces raisons sont des deux modèles suivants : Il y a un malentendu sur les modèles de récupération ou il y a des transactions qui s'exécutent longtemps. Lisez la suite pour plus de détails.

Principale raison 1/2 : ne pas comprendre les modèles de récupération.

(Être en Mode de récupération complète et ne prenant pas sauvegardes de journaux - C'est la raison la plus courante - la grande majorité de ceux qui rencontrent ce problème sont.)

Bien que cette réponse ne soit pas une plongée profonde dans les modèles de récupération de SQL Server, le sujet des modèles de récupération est essentiel à ce problème.

Dans SQL Server, il existe trois modèles de récupération :

  • Full,
  • Bulk-Logged et
  • Simple.

Nous ignorerons Bulk-Logged pour l'instant nous dirons que c'est un modèle hybride et que la plupart des gens qui sont dans ce modèle le sont pour une raison et comprennent les modèles de récupération.

Les deux qui nous intéressent et leur confusion sont la cause de la majorité des cas de personnes ayant ce problème sont les suivants Simple et Full.

Entracte : Récupération en général

Avant de parler des modèles de récupération : parlons de la récupération en général. Si vous voulez approfondir encore plus ce sujet, il suffit de lire le blog de Paul Randal et autant de billets sur ce sujet que vous voulez. Pour cette question, cependant :

  1. Récupération en cas de crash/redémarrage
    Un des buts du fichier journal des transactions est de la récupération en cas de crash/redémarrage. Pour le roulement en avant et le roulement en arrière du travail qui a été soit fait (roulement en avant/redo) avant un crash ou un redémarrage et le travail qui a été commencé mais pas terminé après un crash ou un redémarrage (roulement en arrière/undo). C'est le rôle du journal des transactions de vérifier qu'une transaction a commencé mais n'a jamais été terminée (retour en arrière ou crash/redémarrage avant que la transaction ne soit validée). Dans cette situation, c'est le travail du journal de dire "Hey... ceci n'a jamais vraiment fini, faisons un roll back". pendant la récupération. C'est aussi le rôle du journal de voir que vous avez bien terminé quelque chose et que votre application cliente a été informée que c'était terminé (même si elle ne s'était pas encore endurcie sur votre fichier de données) et de dire "Hey... ceci est vraiment arrivé, faisons-le rouler en avant, faisons-le comme les applications pensent que c'était". après un redémarrage. Maintenant il y a plus mais c'est le but principal.

  2. Récupération d'un point dans le temps
    L'autre but d'un fichier journal des transactions est de pouvoir nous donner la possibilité de récupérer à un... point dans le temps en raison d'un "oops" dans une base de données ou de garantir un point de récupération en cas de défaillance matérielle impliquant les fichiers de données et/ou les fichiers journaux d'une base de données. Si ce journal des transactions contient les enregistrements des transactions qui ont été lancées et terminées pour la récupération, SQL Server peut utiliser et utilise alors ces informations pour remettre une base de données à l'endroit où elle se trouvait avant qu'un problème ne survienne. Mais ce n'est pas toujours une option disponible pour nous. Pour que cela fonctionne, nous devons avoir notre base de données dans la bonne modèle de récupération et nous devons prendre des sauvegardes de journaux.

Modèles de récupération

Sur les modèles de récupération :

  • Modèle de récupération simple
    Ainsi, avec l'introduction ci-dessus, il est plus facile de parler de... Simple Recovery modèle d'abord. Dans ce modèle, vous dites à SQL Server : "Je suis d'accord pour que vous utilisiez votre fichier journal des transactions pour la récupération en cas de crash et de redémarrage...". (Vous n'avez vraiment pas le choix là. Regardez les propriétés ACID et cela devrait prendre sens rapidement). "...mais une fois que vous n'en avez plus besoin pour cet objectif de récupération de crash/redémarrage, allez-y et réutilisez le fichier journal."

    SQL Server écoute cette demande dans Simple Recovery et ne conserve que les informations dont il a besoin pour effectuer la récupération en cas de crash/redémarrage. Une fois que SQL Server est sûr de pouvoir récupérer parce que les données sont durcies dans le fichier de données (plus ou moins), les données qui ont été durcies ne sont plus nécessaires dans le journal et sont marquées pour être tronquées - ce qui signifie qu'elles sont réutilisées.

  • Modèle de récupération complète
    Avec Full Recovery, vous indiquez au serveur SQL que vous voulez pouvoir récupérer à un moment précis, tant que votre fichier journal est disponible ou à un moment précis couvert par une sauvegarde du journal. Dans ce cas, lorsque SQL Server atteint le point où il serait sûr de tronquer le fichier journal dans le modèle de récupération simple, il ne le fera pas. Au lieu de cela, Il laisse le fichier journal continuer à croître et le laissera continuer à croître, jusqu'à ce que vous preniez une sauvegarde du journal (ou que vous manquiez d'espace sur votre lecteur de fichiers journaux) dans des circonstances normales.

Le passage de Simple à Complet a un Gotcha.

Il y a des règles et des exceptions ici. Nous parlerons des transactions à long terme en profondeur ci-dessous.

Mais un avertissement à garder à l'esprit pour le mode de récupération complet est le suivant : Si vous passez simplement en Full Recovery mode, mais que vous ne prenez jamais de sauvegarde complète initiale, SQL Server va pas honorer votre demande d'être en Full Recovery modèle. Votre journal des transactions continuera à fonctionner comme il l'a fait dans le modèle Simplejusqu'à ce que vous passiez en modèle de récupération complète ET que vous preniez votre premier modèle Full Backup.

.Full Backup.

Le modèle de récupération complète sans sauvegarde du journal est mauvais.

Alors, quelle est la raison la plus courante de la croissance incontrôlée des journaux ?
Réponse : Le fait d'être en Mode de récupération complète sans avoir de sauvegarde des journaux.

Ceci arrive tout le temps aux gens.

Pourquoi est-ce une erreur si commune ?

Pourquoi cela arrive-t-il tout le temps ? Parce que chaque nouvelle base de données obtient son paramètre de modèle de récupération initial en regardant la base de données modèle.

Le paramètre de modèle de récupération initial du modèle est toujours Full Recovery Model - jusqu'à ce que et à moins que quelqu'un ne le change. Donc vous pourriez dire que le "modèle de récupération par défaut" est Full. De nombreuses personnes ne sont pas conscientes de cela et ont leurs bases de données fonctionnant en mode Full Recovery Model sans sauvegarde du journal, et donc avec un fichier journal des transactions beaucoup plus volumineux que nécessaire. C'est pourquoi il est important de modifier les valeurs par défaut lorsqu'elles ne fonctionnent pas pour votre organisation et ses besoins).

Le modèle de récupération complète avec trop peu de sauvegardes de journaux est mauvais.

Vous pouvez également vous mettre en difficulté ici en ne prenant pas de sauvegardes de journaux assez fréquemment.
Prendre une sauvegarde de journal par jour peut sembler bien, cela fait qu'une restauration nécessite moins de commandes de restauration, mais en gardant à l'esprit la discussion ci-dessus, ce fichier journal continuera à croître et à croître jusqu'à ce que vous preniez des sauvegardes de journal.

Comment puis-je savoir quelle fréquence de sauvegarde de journal j'ai besoin ?

Vous devez considérer votre fréquence de sauvegarde des journaux en gardant deux choses à l'esprit :

  1. Les besoins de récupération - Ceci devrait, espérons-le, être le premier. Dans le cas où le lecteur hébergeant votre journal des transactions se détériore ou si vous avez une corruption grave qui affecte votre sauvegarde du journal, combien de données peuvent être perdues ? Si ce nombre n'est pas supérieur à 10-15 minutes, alors vous devez prendre la sauvegarde du journal toutes les 10-15 minutes, fin de la discussion.
  2. Croissance du journal - Si votre organisation est d'accord pour perdre plus de données en raison de la possibilité de recréer facilement ce jour-là, vous pouvez être d'accord pour avoir une sauvegarde du journal beaucoup moins fréquemment que 15 minutes. Peut-être que votre organisation est bien avec toutes les 4 heures. Mais vous devez considérer le nombre de transactions que vous générez en 4 heures. En permettant au journal de continuer à croître pendant ces quatre heures, le fichier journal sera-t-il trop volumineux ? Est-ce que cela signifiera que vos sauvegardes du journal prennent trop de temps ?

Principale raison 2/2 : les transactions à long terme

("Mon modèle de récupération est très bien ! Le journal continue de croître !)

Cela peut également être une cause de croissance incontrôlée et incontrôlable du journal. Peu importe le modèle de récupération, mais il se présente souvent comme suit . "Mais je suis en modèle de récupération simple - pourquoi mon journal continue-t-il de croître ? !".

La raison ici est simple : si SQL utilise ce journal de transaction à des fins de récupération comme je l'ai décrit ci-dessus, alors il doit voir en arrière au début d'une transaction.

Si vous avez une transaction qui prend beaucoup de temps ou qui fait beaucoup de changements, le journal ne peut pas être tronqué lors du checkpoint pour tous les changements qui sont encore dans des transactions ouvertes ou qui ont commencé depuis le début de cette transaction.

Cela signifie qu'une grosse suppression, supprimant des millions de lignes dans une déclaration de suppression est une transaction et le journal ne peut pas faire de troncature jusqu'à ce que toute cette suppression soit faite. Dans Full Recovery ModelCette suppression est enregistrée dans un journal, ce qui peut représenter un grand nombre d'enregistrements. Même chose pour les travaux d'optimisation de l'index pendant les fenêtres de maintenance. Cela signifie également qu'une mauvaise gestion des transactions et le fait de ne pas surveiller et fermer les transactions ouvertes peuvent vraiment vous nuire et nuire à votre fichier journal.

Que puis-je faire à propos de ces longues transactions en cours ?

Vous pouvez vous sauver ici en :

  • Dimensionnant correctement votre fichier journal pour tenir compte du pire scénario - comme votre maintenance ou les grandes opérations connues. Et lorsque vous faites croître votre fichier journal, vous devriez regarder ces conseils (et les deux liens qu'elle vous envoie) par Kimberly Tripp. Le bon dimensionnement est super critique ici.
  • Surveillez votre utilisation des transactions. Ne lancez pas une transaction dans votre serveur d'application et commencez à avoir de longues conversations avec SQL Server et risquez d'en laisser une ouverte trop longtemps.
  • Surveiller les transactions implicites dans vos déclarations DML. Par exemple : UPDATE TableName Set Col1 = 'New Value' est une transaction. Je n'ai pas mis un BEGIN TRAN et je n'ai pas besoin de le faire, il s'agit toujours d'une transaction qui se commet automatiquement lorsqu'elle est terminée. Donc, si vous effectuez des opérations sur un grand nombre de lignes, pensez à regrouper ces opérations en morceaux plus faciles à gérer et à donner au journal le temps de récupérer. Ou bien réfléchissez à la bonne taille pour gérer cela. Ou peut-être envisager de changer les modèles de récupération pendant une fenêtre de chargement en vrac.

Ces deux raisons s'appliquent-elles également à l'expédition de journaux ?

Réponse courte : oui. Réponse plus longue ci-dessous.

Question : "J'utilise l'expédition de journaux, donc mes sauvegardes de journaux sont automatisées.... Pourquoi est-ce que je vois encore une croissance du journal des transactions ?"

Réponse : lisez la suite.

Qu'est-ce que le Log Shipping ?

L'expédition de journaux est exactement ce qu'elle semble être - vous expédiez vos sauvegardes de journaux de transactions vers un autre serveur à des fins de DR. Il y a une certaine initialisation, mais après cela, le processus est assez simple :

  • Un job pour sauvegarder le journal sur un serveur,
  • un job pour copier cette sauvegarde du journal et
  • un job pour le restaurer sans récupération (soit NORECOVERY ou STANDBY) sur le serveur de destination.

Il y a aussi quelques tâches pour surveiller et alerter si les choses ne se passent pas comme vous l'avez prévu.

Dans certains cas, vous pouvez ne vouloir faire la restauration de l'expédition des journaux qu'une fois par jour ou tous les trois jours ou une fois par semaine. C'est très bien. Mais si vous faites ce changement sur tous les travaux (y compris les travaux de sauvegarde et de copie de journal), cela signifie que vous attendez tout ce temps pour prendre une sauvegarde de journal. Cela signifie que vous aurez beaucoup de croissance du journal -- parce que vous êtes... en mode de récupération complète sans sauvegarde de journal. -- et cela signifie probablement aussi un gros fichier journal à copier à travers. Vous devriez seulement modifier la planification de la tâche de restauration et laisser les sauvegardes et les copies de journaux se produire sur une base plus fréquente, sinon vous souffrirez du premier problème décrit dans cette réponse.


Dépannage général via les codes d'état.

Il existe d'autres raisons que ces deux-là, mais ce sont les plus courantes. Indépendamment de la cause : il y a un moyen d'analyser votre raison pour cette croissance inexpliquée du journal/manque de troncature et de voir ce qu'ils sont.

En interrogeant le sys.databases catalog view, vous pouvez voir des informations décrivant la raison pour laquelle votre fichier journal peut être en attente de truncate/reuse.

Il existe une colonne appelée log_reuse_wait avec un ID de consultation du code de raison et une colonne appelée log_reuse_wait_desc avec une description de la raison de l'attente. La majorité des raisons (celles que vous êtes susceptibles de voir et celles pour lesquelles nous pouvons expliquer les raisons) sont tirées des livres en ligne référencés. Celles qui manquent sont soit hors d'usage, soit à usage interne) avec quelques notes sur l'attente dans la colonne italique:

  • 0 = Rien
    Ce à quoi ça ressemble... Il ne faut pas attendre

  • 1 = Point de contrôle
    Attendre qu'un point de contrôle se produise. Cela devrait se produire et vous devriez être bien - mais il y a quelques cas à regarder ici pour des réponses ou des éditions ultérieures.

  • 2 = Sauvegarde du journal
    Vous attendez qu'une sauvegarde du journal se produise. Soit vous les avez programmées et cela se produira bientôt, soit vous avez le premier problème décrit ici et vous savez maintenant comment le résoudre...

  • 3 = Sauvegarde ou restauration active
    Une opération de sauvegarde ou de restauration est en cours d'exécution sur la base de données.

  • 4 = Transaction active
    Il y a une transaction active qui doit se terminer (dans les deux sens - ROLLBACK ou COMMIT) avant que le journal puisse être sauvegardé. C'est la deuxième raison décrite dans cette réponse.

  • 5 = Mise en miroir de la base de données
    Soit un miroir prend du retard ou subit une certaine latence dans une situation de mise en miroir de haute performance, soit la mise en miroir est mise en pause pour une raison quelconque.

  • 6 = Réplication
    Des problèmes de réplication peuvent en être la cause, comme un agent de lecture de journaux qui ne fonctionne pas, une base de données qui pense être marquée pour la réplication alors qu'elle ne l'est plus, et bien d'autres raisons encore. Vous pouvez également voir cette raison et c'est parfaitement normal parce que vous regardez juste au bon moment, juste au moment où les transactions sont consommées par le lecteur de journaux....

  • 7 = Création d'un instantané de la base de données
    Vous êtes en train de créer un instantané de la base de données, vous le verrez si vous regardez juste au bon moment alors qu'un instantané est en train d'être créé

  • 8 = Analyse du journal
    Je n'ai pas encore rencontré de problème avec ce fonctionnement à l'infini. Si vous regardez assez longtemps et assez fréquemment, vous pouvez voir cela se produire, mais cela ne devrait pas être une cause de croissance excessive du journal des transactions, que j'ai vu.

  • 9 = Une réplique secondaire des groupes de disponibilité AlwaysOn applique les enregistrements du journal des transactions de cette base de données à une base de données secondaire correspondante.
    A peu près la description la plus claire à ce jour...

Puisque je ne suis pas vraiment satisfait de toutes les réponses sur Stack Overflow, y compris la suggestion la plus fortement votée, et parce qu'il y a quelques choses que je voudrais aborder que la réponse de Mike ne fait pas, j'ai pensé que je fournirais ma contribution ici aussi. J'ai placé une copie de cette réponse là aussi.

Rendre un fichier journal plus petit devrait vraiment être réservé aux scénarios où il a rencontré une croissance inattendue que vous ne vous attendez pas à voir se reproduire. Si le fichier journal va croître à nouveau à la même taille, on n'accomplit pas grand-chose en le réduisant temporairement. Maintenant, selon les objectifs de récupération de votre base de données, ce sont les actions que vous devriez prendre.

Premièrement, prenez une sauvegarde complète

Ne faites jamais de modifications à votre base de données sans vous assurer que vous pouvez la restaurer si quelque chose ne va pas.

Si vous vous souciez de la récupération ponctuelle

(Et par récupération ponctuelle, je veux dire que vous vous souciez de pouvoir restaurer à tout autre chose qu'une sauvegarde complète ou différentielle).

Vraisemblablement, votre base de données est dans FULL mode de récupération. Si ce n'est pas le cas, assurez-vous qu'elle l'est :

ALTER DATABASE yourdb SET RECOVERY FULL;

Même si vous effectuez des sauvegardes complètes régulières, le fichier journal va grossir et grossir encore jusqu'à ce que vous effectuiez un journal Ceci est pour votre protection, pas pour consommer inutilement votre espace disque. Vous devriez effectuer ces sauvegardes du journal assez fréquemment, en fonction de vos objectifs de récupération. Par exemple, si vous avez une règle de gestion qui stipule que vous pouvez vous permettre de perdre au moins 15 minutes de données en cas de sinistre, vous devriez avoir une tâche qui sauvegarde le journal toutes les 15 minutes. Voici un script qui générera des noms de fichiers horodatés en fonction de l'heure actuelle (mais vous pouvez également le faire avec des plans de maintenance, etc., il suffit de ne pas choisir l'une des options de psy dans les plans de maintenance, elles sont affreuses).

DECLARE @path NVARCHAR(255) = N'\backup_sharelogyourdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Notez que \backup_share devrait être sur une machine différente qui représente un périphérique de stockage sous-jacent différent. Les sauvegarder sur la même machine (ou sur une machine différente qui utilise les mêmes disques sous-jacents, ou une VM différente qui est sur le même hôte physique) ne vous aide pas vraiment, car si la machine explose, vous avez perdu votre base de données... et ses sauvegardes. En fonction de votre infrastructure réseau, il peut être plus judicieux de sauvegarder localement et de les transférer à un autre emplacement en coulisse ; dans tous les cas, vous voulez les retirer de la machine de base de données primaire aussi rapidement que possible.

Maintenant, une fois que vous avez des sauvegardes régulières de journaux en cours d'exécution, il devrait être raisonnable de réduire le fichier journal à quelque chose de plus raisonnable que ce à quoi il est gonflé maintenant. Ceci fait pas signifie exécuter SHRINKFILE encore et encore jusqu'à ce que le fichier journal fasse 1 Mo - même si vous sauvegardez fréquemment le journal, il doit toujours accueillir la somme de toutes les transactions simultanées qui peuvent se produire. Les événements de croissance automatique du fichier journal sont coûteux, car SQL Server doit mettre à zéro les fichiers (contrairement aux fichiers de données lorsque l'initialisation instantanée du fichier est activée), et les transactions utilisateur doivent attendre pendant que cela se produit. Vous voulez faire cette routine de croissance-rétrécissement-croissance-rétrécissement aussi peu que possible, et vous ne voulez certainement pas faire payer vos utilisateurs pour cela.

Notez que vous pouvez avoir besoin de sauvegarder le journal deux fois avant qu'un shrink soit possible (merci Robert).

Donc, vous devez trouver une taille pratique pour votre fichier journal. Personne ici ne peut vous dire ce que c'est sans en savoir beaucoup plus sur votre système, mais si vous avez fréquemment rétréci le fichier journal et qu'il a recommencé à croître, un bon filigrane est probablement 10-50% plus élevé que le plus grand qu'il a été. Disons que cela arrive à 200 Mo, et que vous voulez que tous les événements ultérieurs de croissance automatique soient de 50 Mo, alors vous pouvez ajuster la taille du fichier journal de cette façon :

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Notez que si le fichier journal est actuellement > 200 Mo, vous devrez peut-être exécuter ceci d'abord :

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

Si vous ne vous souciez pas de la récupération à un moment donné

S'il s'agit d'une base de données de test, et que vous ne vous souciez pas de la récupération ponctuelle, alors vous devez vous assurer que votre base de données se trouve dans le répertoire SIMPLE mode de récupération.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Mettre la base de données en SIMPLE recovery mode permettra de s'assurer que SQL Server réutilise des portions du fichier journal (essentiellement en éliminant progressivement les transactions inactives) au lieu de croître pour conserver un enregistrement de... toutes les transactions (comme FULL récupération fait jusqu'à ce que vous sauvegardiez le journal). CHECKPOINT événements aideront à contrôler le journal et à s'assurer qu'il n'a pas besoin de croître, à moins que vous ne génériez beaucoup d'activité de t-log entre les événements de CHECKPOINTs.

Ensuite, vous devez vous assurer absolument que cette croissance du journal était vraiment due à un événement anormal (disons, un nettoyage de printemps annuel ou la reconstruction de vos plus grands index), et non à une utilisation normale et quotidienne. Si vous réduisez le fichier journal à une taille ridiculement petite, et que SQL Server doit le faire croître à nouveau pour répondre à votre activité normale, qu'avez-vous gagné ? Avez-vous été en mesure d'utiliser l'espace disque que vous avez libéré temporairement ? Si vous avez besoin d'une solution immédiate, alors vous pouvez exécuter ce qui suit :

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

Sinon, définissez une taille et un taux de croissance appropriés. Comme dans l'exemple du cas de récupération ponctuelle, vous pouvez utiliser le même code et la même logique pour déterminer quelle taille de fichier est appropriée et définir des paramètres de croissance automatique raisonnables.

Certaines choses que vous ne voulez pas faire

  • Sauvegarder le journal avec TRUNCATE_ONLY et ensuite SHRINKFILE. Pour l'un, ceci TRUNCATE_ONLY a été dépréciée et n'est plus disponible dans les versions actuelles de SQL Server. Deuxièmement, si vous êtes dans FULL modèle de récupération, cela détruira votre chaîne de journaux et nécessitera une nouvelle sauvegarde complète.

  • Détachez la base de données, supprimez le fichier journal et attachez de nouveau.. Je ne peux pas insister sur le fait que cela peut être dangereux. Votre base de données peut ne pas revenir, elle peut apparaître comme suspecte, vous pouvez avoir à revenir à une sauvegarde (si vous en avez une), etc. etc.

  • Utilisez l'option "réduire la base de données".. DBCC SHRINKDATABASE et l'option de plan de maintenance pour faire la même chose sont de mauvaises idées, surtout si vous avez vraiment seulement besoin de résoudre un problème de journal. Ciblez le fichier que vous voulez ajuster et ajustez-le indépendamment, en utilisant l'option DBCC SHRINKFILE ou ALTER DATABASE ... MODIFY FILE (exemples ci-dessus).

  • Réduit le fichier journal à 1 Mo. Cela semble tentant parce que, hé, SQL Server me permettra de le faire dans certains scénarios, et regardez tout l'espace que cela libère ! À moins que votre base de données ne soit en lecture seule (et c'est le cas, vous devriez la marquer comme telle en utilisant la commande ALTER DATABASE), cela va absolument juste conduire à de nombreux événements de croissance inutiles, car le journal doit accueillir les transactions en cours, quel que soit le modèle de récupération. Quel est l'intérêt de libérer cet espace temporairement, juste pour que SQL Server puisse le reprendre lentement et douloureusement ?

  • Créer un deuxième fichier journal. Cela permettra de soulager temporairement le lecteur qui a rempli votre disque, mais c'est comme essayer de réparer un poumon perforé avec un pansement. Vous devriez traiter directement le fichier journal problématique au lieu de simplement ajouter un autre problème potentiel. À part la redirection de certaines activités du journal des transactions vers un autre lecteur, un deuxième fichier journal ne vous apporte rien (contrairement à un deuxième fichier de données), car un seul de ces fichiers peut être utilisé à la fois. Paul Randal explique également pourquoi les fichiers journaux multiples peuvent vous mordre plus tard.

Soyez proactif

Au lieu de réduire votre fichier journal à une petite quantité et de le laisser constamment s'autograndir à un petit taux par lui-même, définissez-le à une certaine taille raisonnablement grande (celle qui s'adaptera à la somme de votre plus grand ensemble de transactions simultanées) et définissez un paramètre d'autograndissement raisonnable comme solution de repli, de sorte qu'il n'ait pas à s'agrandir plusieurs fois pour satisfaire des transactions uniques et qu'il sera relativement rare qu'il doive jamais s'agrandir pendant les opérations commerciales normales.

Les pires paramètres possibles ici sont une croissance de 1 Mo ou une croissance de 10 %. Il est assez drôle de constater que ce sont les valeurs par défaut de SQL Server (dont je me suis plaint et dont j'ai demandé la modification en vain) - 1 Mo pour les fichiers de données, et 10% pour les fichiers journaux. Le premier est beaucoup trop petit à notre époque, et le second conduit à des événements de plus en plus longs à chaque fois (disons que votre fichier journal est de 500 Mo, la première croissance est de 50 Mo, la croissance suivante est de 55 Mo, la croissance suivante est de 60,5 Mo, etc. etc. - et sur des E/S lentes, croyez-moi, vous remarquerez vraiment cette courbe).

Lecture complémentaire

S'il vous plaît, ne l'arrêtez pase; alors qu'une grande partie des conseils que vous voyez sur la réduction des fichiers journaux sont intrinsèquement mauvais et même potentiellement désastreux, il y a des gens qui se soucient plus de l'intégrité des données que de libérer de l'espace disque.

  • Un billet de blog que j'ai écrit en 2009, lorsque j'ai vu surgir quelques billets "voici comment réduire le fichier journal".

  • Un billet de blog que Brent Ozar a écrit il y a quatre ans, en pointant vers de multiples ressources, en réponse à un article de SQL Server Magazine qui devrait... pas ont été publiés.

  • Un billet de blog de Paul Randal expliquant pourquoi la maintenance des t-logs est importante et pourquoi vous ne devriez pas non plus réduire vos fichiers de données.

  • Mike Walsh a une excellente réponse ci-dessus, bien sûr, couvrant certains de ces aspects aussi, y compris les raisons pour lesquelles vous pourriez ne pas être en mesure de rétrécir votre fichier journal immédiatement.

Vous pouvez également voir le contenu de votre fichier journal. Pour ce faire, vous pouvez utiliser la méthode non documentée suivante . fn_dblog, ou un lecteur de journal des transactions, tel que ApexSQL Log.

Il ne montre pas la réorganisation de l'index, mais il montre tous les événements DML et divers événements DDL : ALTER, CREATE, DROP, activation/désactivation de déclencheurs, octroi/révocation de permissions, renommage d'objets.

ApexSQLLogProject.temp - ApexSQL.log

Avertissement : je travaille pour ApexSQL en tant qu'ingénieur de support.

Si vous avez une quelconque indécision et comment tirer parti de notre publication, vous pouvez faire une dissertation et nous la regarderons avec plaisir.



Utilisez notre moteur de recherche

Ricerca
Generic filters

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.