Question Comment effacez-vous le journal des transactions SQL Server?


Je ne suis pas un expert SQL, et je me rappelle le fait chaque fois que j'ai besoin de faire quelque chose au-delà des bases. J'ai une base de données de test qui n'est pas de grande taille, mais le journal des transactions l'est certainement. Comment effacer le journal des transactions?


496
2017-09-11 14:08


origine


Réponses:


Faire un fichier journal plus petit devrait vraiment être réservé pour les scénarios où il a rencontré une croissance inattendue dont vous ne vous attendez pas à se reproduire. Si le fichier journal atteint la même taille, peu de choses sont accomplies en le réduisant temporairement. Maintenant, en fonction des objectifs de récupération de votre base de données, voici les actions à entreprendre.

D'abord, prenez une sauvegarde complète

Ne modifiez jamais votre base de données sans vous assurer de pouvoir la restaurer en cas de problème.

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 autre chose qu'une sauvegarde complète ou différentielle.)

Vraisemblablement votre base de données est en FULL mode de récupération. Si non, alors assurez-vous que c'est:

ALTER DATABASE testdb SET RECOVERY FULL;

Même si vous effectuez des sauvegardes complètes régulières, le fichier journal grandira et grandira jusqu'à ce que vous effectuiez une sauvegarde complète. bûche sauvegarde - c'est pour votre protection, de ne pas ronger inutilement votre espace disque. Vous devez effectuer ces sauvegardes de journaux assez fréquemment, en fonction de vos objectifs de récupération. Par exemple, si vous avez une règle métier qui stipule que vous pouvez vous permettre de ne pas perdre plus de 15 minutes de données en cas de sinistre, vous devez avoir un travail 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 aussi le faire avec des plans de maintenance etc., mais ne choisissez aucune des options de réduction dans les plans de maintenance, ils sont terribles).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_' 
  + 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\ doit ê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 autre machine qui utilise les mêmes disques sous-jacents ou une machine virtuelle différente 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 ensuite de les transférer vers un autre emplacement en coulisse; Dans les deux cas, vous souhaitez les retirer de la machine principale de la base de données aussi rapidement que possible.

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

Notez que vous devrez peut-être sauvegarder le journal deux fois avant qu'un retrait 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 réduisez fréquemment le fichier journal et que celui-ci a de nouveau augmenté, un bon filigrane est probablement 10 à 50% plus élevé que le précédent . Supposons que cela arrive à 200 Mo et que vous souhaitiez que les événements de croissance automatique ultérieurs soient de 50 Mo, 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 l'exécuter en premier:

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

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

S'il s'agit d'une base de données de test, et que vous ne vous souciez pas de la récupération à un point dans le temps, vous devez vous assurer que votre base de données est en SIMPLE mode de récupération.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

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

Ensuite, vous devez vous assurer que la croissance de ce journal est vraiment due à un événement anormal (disons un nettoyage annuel du printemps ou à la reconstruction de vos plus gros index), et non à un usage quotidien normal. Si vous réduisez le fichier journal à une taille ridiculement petite et que SQL Server doit simplement le développer pour s'adapter à votre activité normale, qu'avez-vous gagné? Avez-vous pu utiliser cet espace disque que vous avez libéré seulement temporairement? Si vous avez besoin d'un correctif immédiat, vous pouvez exécuter les opérations suivantes:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO

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

Certaines choses que vous ne voulez pas faire

  • Sauvegardez le journal avec TRUNCATE_ONLY option et ensuite SHRINKFILE. Pour un, ceci TRUNCATE_ONLY L'option est obsolète et n'est plus disponible dans les versions actuelles de SQL Server. Deuxièmement, si vous êtes en 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 réattachez. Je ne peux pas souligner à quel point cela peut être dangereux. Votre base de données peut ne pas remonter, elle peut apparaître comme suspecte, vous devrez peut-être revenir à une sauvegarde (si vous en avez une), etc.

  • Utilisez l'option "shrink database". DBCC SHRINKDATABASE L'option de plan de maintenance pour faire la même chose est une mauvaise idée, surtout si vous devez uniquement résoudre un problème de journal. Ciblez le fichier que vous souhaitez ajuster et ajustez-le indépendamment, en utilisant DBCC SHRINKFILE ou ALTER DATABASE ... MODIFY FILE (exemples ci-dessus).

  • Réduire le fichier journal à 1 Mo. Cela semble tentant parce que, bon, SQL Server me laissera le faire dans certains scénarios, et regarde tout l'espace qu'il libère! Sauf si votre base de données est en lecture seule (et c'est le cas, vous devriez le marquer comme tel en utilisant ALTER DATABASE), cela conduira tout simplement à de nombreux événements de croissance inutiles, car le journal doit tenir compte des 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, pour que SQL Server puisse le récupérer lentement et péniblement?

  • Créer un deuxième fichier journal. Cela fournira temporairement un soulagement pour le lecteur qui a rempli votre disque, mais c'est comme essayer de réparer un poumon perforé avec un pansement. Vous devez traiter directement le fichier journal problématique au lieu de simplement ajouter un autre problème potentiel. En dehors de la redirection de certaines activités du journal des transactions vers un lecteur différent, un deuxième fichier journal ne fait vraiment rien pour vous (contrairement à un second fichier de données), car un seul fichier peut être utilisé à la fois. Paul Randal explique également pourquoi plusieurs fichiers journaux peuvent vous mordre plus tard.

Etre pro-actif

Au lieu de réduire votre fichier journal à un petit montant et de le laisser progresser automatiquement à une petite vitesse, définissez-le à une taille raisonnablement grande (une qui s'adaptera à la somme de votre plus grand nombre de transactions simultanées) et définissez une croissance automatique raisonnable. la mise en place en tant que solution de repli, afin qu'elle n'ait pas à croître plusieurs fois pour satisfaire des transactions uniques et qu'il soit relativement rare qu'elle ait à croître au cours d'opérations commerciales normales.

Les pires paramètres possibles sont une croissance de 1 Mo ou une croissance de 10%. Assez drôle, ce sont les valeurs par défaut pour SQL Server (dont je me suis plaint et demandé des changements en vain) - 1 Mo pour les fichiers de données et 10% pour les fichiers journaux. Le premier est beaucoup trop petit de nos jours, et le second mène à des événements de plus en plus longs (disons, votre fichier journal est de 500 Mo, la première croissance est de 50 Mo, la prochaine croissance est de 55 Mo, la prochaine croissance est de 60,5 Mo , etc. etc. - et sur les E / S lentes, croyez-moi, vous remarquerez vraiment cette courbe).

En lire plus

S'il vous plaît ne vous arrêtez pas ici; Alors que la plupart 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 article de blog que j'ai écrit il y a quatre ans, quand j'ai vu quelques "ici, comment réduire le fichier journal".

Un article de blog écrit par Brent Ozar il y a quatre ans, indiquant plusieurs ressources, en réponse à un article de SQL Server Magazine qui devrait ne pas ont été publiés.

Un article de Paul Randal expliquant pourquoi la maintenance de t-log est importante et pourquoi vous ne devriez pas réduire vos fichiers de données, soit.

Mike Walsh a une excellente réponse à certains de ces aspects, y compris les raisons pour lesquelles vous pourriez ne pas être en mesure de réduire votre fichier journal immédiatement.


630
2017-08-17 18:38



AVERTISSEMENT: Veuillez lire attentivement les commentaires ci-dessous, et je présume que vous avez déjà lu la réponse acceptée. Comme je l'ai dit il y a près de 5 ans:

si quelqu'un a des commentaires à ajouter pour des situations où ce n'est pas un   solution adéquate ou optimale alors s'il vous plaît commenter ci-dessous


  • Cliquez avec le bouton droit sur le nom de la base de données.

  • Sélectionnez Tâches → Réduire → Base de données

  • Puis clique D'accord!

J'ouvre généralement le répertoire Windows Explorer contenant les fichiers de base de données, donc je peux voir immédiatement l'effet.

En fait, j'ai été très surpris que cela fonctionne! Normalement, j'ai déjà utilisé DBCC, mais je l'ai juste essayé et ça n'a rien réduit, donc j'ai essayé l'interface graphique (2005) et ça a bien fonctionné - libérant 17 Go en 10 secondes

En mode de récupération complète, cela peut ne pas fonctionner, vous devez donc d'abord sauvegarder le journal, ou passer en récupération simple, puis réduire le fichier. [merci @onupdatecascade pour cela]

-

PS: J'apprécie ce que certains ont commenté sur les dangers de cela, mais dans mon environnement je n'ai eu aucun problème à le faire moi-même d'autant plus que je fais toujours une sauvegarde complète en premier. Tenez donc compte de votre environnement et de son impact sur votre stratégie de sauvegarde et votre sécurité d'emploi avant de continuer. Tout ce que je faisais était de pointer les gens vers une fonctionnalité fournie par Microsoft!


177
2018-01-19 20:31



USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

De: DBCC SHRINKFILE (Transact-SQL)

Vous voudrez peut-être effectuer une sauvegarde en premier.


164
2017-10-31 10:51



Voici un script pour réduire le journal des transactions, mais je recommande vivement de sauvegarder le journal des transactions avant de le réduire.

Si vous ne faites que réduire le fichier, vous allez perdre une tonne de données qui pourraient vous sauver la vie en cas de sinistre. Le journal des transactions contient beaucoup de données utiles qui peuvent être lues à l'aide d'un lecteur de journal de transactions tiers (il peut être lu manuellement mais avec un effort extrême).

Le journal des transactions est également un must quand il s'agit de la récupération dans le temps, alors ne le jetez pas simplement, mais assurez-vous de le sauvegarder au préalable.

Voici plusieurs messages où les personnes ont utilisé les données stockées dans le journal des transactions pour effectuer la récupération:

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

Vous pouvez obtenir une erreur qui ressemble à ceci lors de l'exécution des commandes ci-dessus

"Impossible de réduire le fichier journal (nom du fichier journal) car la logique       fichier journal situé à la fin du fichier est en cours d'utilisation "

Cela signifie que TLOG est utilisé. Dans ce cas, essayez d'exécuter ceci plusieurs fois de suite ou trouvez un moyen de réduire les activités de base de données.


51
2018-03-18 12:16



Si vous n'utilisez pas les journaux de transactions pour les restaurations (c'est-à-dire que vous ne faites que des sauvegardes complètes), vous pouvez définir le mode de récupération sur "Simple" et le journal des transactions va très rapidement se rétrécir.

Si vous utilisez SQL 7 ou 2000, vous pouvez activer "tronquer le point de contrôle" dans l'onglet Options de la base de données. Cela a le même effet.

Cela n'est évidemment pas recommandé dans les environnements de production, car vous ne pourrez pas restaurer à un moment donné.


28
2017-09-15 14:31



Voici un simple et très inélégant & Potentiellement dangereux  façon.

  1. DB de sauvegarde
  2. Détacher la base de données
  3. Renommer le fichier journal
  4. Joindre DB
  5. Un nouveau fichier journal sera recréé
  6. Supprimer le fichier journal renommé.

Je suppose que vous ne faites pas de sauvegardes de journaux. (Qui tronque le journal). Mon conseil est de changer le modèle de récupération de plein à simple. Cela empêchera le gonflement du journal.


25
2017-09-11 14:16



La plupart des réponses ici supposent que vous n’avez pas besoin du fichier journal des transactions, mais si votre base de données utilise FULL modèle de récupération, et vous souhaitez conserver vos sauvegardes au cas où vous avez besoin de restaurer la base de données, puis ne pas tronquer ou supprimer le fichier journal de la façon dont plusieurs de ces réponses suggèrent.

Éliminer le fichier journal (en le tronquant, en le supprimant, en l'effaçant, etc.) viendra briser votre chaîne de sauvegarde et vous empêchera de restaurer à tout moment depuis votre dernière sauvegarde complète, différentielle ou du journal des transactions, jusqu'au prochain ou une sauvegarde différentielle est effectuée.

Du Article de Microsoft surBACKUP

Nous vous recommandons de ne jamais utiliser NO_LOG ou TRUNCATE_ONLY pour manuellement   tronquez le journal des transactions, car cela casse la chaîne du journal. Jusqu'à   la prochaine sauvegarde de base de données complète ou différentielle, la base de données n'est pas   protégé contre les pannes de support. Utilisez la troncature manuelle des journaux uniquement   circonstances spéciales, et créer des sauvegardes des données immédiatement.

Pour éviter cela, sauvegardez votre fichier journal sur le disque avant de le rétrécir. La syntaxe ressemblerait à ceci:

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

9
2018-01-31 15:02



Le journal des transactions SQL Server doit être correctement géré afin d'empêcher sa croissance indésirable. Cela signifie que l'exécution des sauvegardes du journal des transactions est souvent suffisante. En ne faisant pas cela, vous risquez que le journal des transactions soit plein et commence à se développer.

Outre les réponses à cette question, je recommande de lire et de comprendre les mythes communs de journal des transactions. Ces lectures peuvent aider à comprendre le journal des transactions et à décider quelles techniques utiliser pour le "nettoyer":

De 10 mythes les plus importants du journal de transactions SQL Server:

Mythe: mon serveur SQL est trop occupé. Je ne veux pas effectuer de sauvegardes du journal des transactions SQL Server

L'une des opérations les plus gourmandes en performances de SQL Server est un événement de croissance automatique du fichier journal des transactions en ligne. En ne générant pas assez de sauvegardes des journaux de transactions, le journal des transactions en ligne deviendra complet et devra se développer. La taille de croissance par défaut est de 10%. Plus la base de données est chargée, plus le journal des transactions en ligne se développe rapidement si les sauvegardes du journal des transactions ne sont pas créées.   La création d'une sauvegarde du journal des transactions SQL Server ne bloque pas le journal des transactions en ligne, mais un événement de croissance automatique. Il peut bloquer toute activité dans le journal des transactions en ligne

De Les mythes du journal des transactions:

Mythe: La réduction régulière du nombre de billes est une bonne pratique d'entretien

FAUX. La croissance du journal est très coûteuse car le nouveau segment doit être remis à zéro. Toutes les activités d'écriture s'arrêtent sur cette base de données jusqu'à ce que la réduction à zéro soit terminée, et si votre écriture sur disque est lente ou si la taille de la croissance automatique est importante, cette pause peut être énorme et les utilisateurs le remarqueront. C'est l'une des raisons pour lesquelles vous voulez éviter la croissance. Si vous réduisez le journal, il augmentera à nouveau et vous ne ferez que gaspiller le fonctionnement du disque sur un jeu inutile de contraction et de croissance.


9
2018-06-10 11:41



Cette technique recommandée par John n'est pas recommandée car il n'y a aucune garantie que la base de données sera attachée sans le fichier journal. Changez la base de données de complète à simple, forcez un point de contrôle et attendez quelques minutes. SQL Server effacera le journal, que vous pouvez ensuite réduire à l'aide de DBCC SHRINKFILE.


8
2018-02-06 22:26



Commencez par vérifier le modèle de récupération de base de données. Par défaut, SQL Server Express Edition crée une base de données pour la récupération simple modèle (si je ne me trompe pas).

Journal de sauvegarde DatabaseName Avec Truncate_Only:

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile vous donnera le nom du fichier journal logique.

Faire référence à:

Récupérer à partir d'un journal de transactions complet dans une base de données SQL Server

Si votre base de données est en mode de récupération complète et si vous ne prenez pas de sauvegarde TL, modifiez-la en SIMPLE.


5
2018-06-12 04:39