Comment effacer le journal des transactions du serveur SQL ?

Question

Je ne suis pas un expert en SQL, et je m'en rends compte chaque fois que j'ai besoin de faire quelque chose qui dépasse les bases. J'ai une base de données de test qui n'est pas très volumineuse, mais le journal des transactions l'est. Comment puis-je effacer le journal des transactions ?

Answer 1

La réduction de la taille d'un fichier journal doit être réservée aux cas où il a connu une croissance inattendue qui ne devrait pas se reproduire. Si le fichier journal atteint à nouveau la même taille, il ne sert pas à grand-chose de le réduire temporairement. Maintenant, en fonction des objectifs de récupération de votre base de données, voici les actions que vous devez entreprendre.

D'abord, faites une sauvegarde complète

N'apportez jamais de modifications à votre base de données sans vous assurer que vous pouvez 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 à tout autre chose qu'une sauvegarde complète ou différentielle).

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

ALTER DATABASE testdb SET RECOVERY FULL;

Même si vous effectuez régulièrement des sauvegardes complètes, le fichier journal ne cessera de grossir jusqu'à ce que vous effectuiez une journal sauvegarde - il s'agit de vous protéger, et non de consommer 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 de gestion qui stipule que vous pouvez vous permettre de ne pas perdre plus de 15 minutes de données en cas de catastrophe, 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_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\ 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 se trouve 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 l'infrastructure de votre réseau, il peut être plus judicieux d'effectuer des sauvegardes locales, puis de les transférer vers un autre emplacement en coulisse. Dans tous les cas, vous devez les retirer de la machine principale de la base de données aussi rapidement que possible.

Maintenant, une fois que vous avez des sauvegardes régulières du journal, il devrait être raisonnable de réduire le fichier journal à quelque chose de plus raisonnable que ce qu'il est gonflé à l'heure actuelle. Cela fait pas fonctionnement moyen SHRINKFILE jusqu'à ce que le fichier journal atteigne 1 Mo. Même si vous sauvegardez fréquemment le journal, il doit toujours contenir 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 des utilisateurs 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 devrez peut-être sauvegarder le journal deux fois avant qu'un rétrécissement soit possible (merci Robert).

Vous devez donc 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éduit 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é. Supposons que la taille du fichier journal soit de 200 Mo et que vous souhaitiez que tous les événements ultérieurs de croissance automatique soient de 50 Mo, vous pouvez alors ajuster la taille du fichier journal de cette manière :

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 cette opération en premier :

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 est en mode SIMPLE mode de récupération.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

Mise en place de la base de données SIMPLE Le mode de récupération s'assurera que SQL Server réutilise des parties du fichier journal (essentiellement en éliminant progressivement les transactions inactives) au lieu de continuer à conserver un enregistrement des données suivantes tous les transactions (comme FULL jusqu'à ce que vous sauvegardiez le journal). CHECKPOINT permettront de contrôler le journal et de 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 CHECKPOINT s.

Ensuite, vous devez vous assurer que cette croissance du journal est vraiment due à un événement anormal (par exemple, un nettoyage de printemps annuel ou la reconstruction de vos principaux 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, vous pouvez exécuter ce qui suit :

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

Sinon, définissez une taille et un taux de croissance appropriés. Comme dans l'exemple de la récupération ponctuelle, 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

• Sauvegarder le journal avec TRUNCATE_ONLY et ensuite SHRINKFILE . D'une part, cette 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 ce qui 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-la à nouveau. . Je ne peux pas insister sur le danger que cela peut représenter. Votre base de données peut ne pas être rétablie, elle peut être considérée comme suspecte, vous pouvez être obligé de 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 du plan de maintenance pour faire de même sont de mauvaises idées, surtout si vous n'avez vraiment besoin que de résoudre un problème de journal. Ciblez le fichier que vous voulez ajuster et ajustez-le indépendamment, en utilisant DBCC SHRINKFILE ou ALTER DATABASE ... MODIFY FILE (exemples ci-dessus).

• Réduire le fichier journal à 1 MB . Cela semble tentant parce que, hé, SQL Server me permet 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 devez la marquer comme telle en utilisant la commande ALTER DATABASE ), cela ne fera qu'entraîner 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 temporairement cet espace, juste pour que SQL Server puisse le reprendre lentement et péniblement ?

• Créer un deuxième fichier journal . Cela soulagera 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 d'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), puisqu'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 croître automatiquement à un faible taux, définissez-le à une taille raisonnablement grande (une taille qui s'adaptera à la somme de votre plus grand ensemble de transactions simultanées) et définissez un paramètre de croissance automatique raisonnable comme solution de repli, de sorte qu'il n'ait pas à croître plusieurs fois pour satisfaire des transactions uniques et qu'il soit relativement rare qu'il doive croître 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 amusant de constater que ce sont les paramètres par défaut de SQL Server (dont je me suis plaint et que je n'ai pas réussi à modifier). a 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 à notre époque, et le second conduit à des événements de plus en plus longs à chaque fois (disons que votre fichier journal fait 500 Mo, que la première croissance est de 50 Mo, que la croissance suivante est de 55 Mo, que la croissance suivante est de 60,5 Mo, etc. etc. - et sur des E/S lentes, croyez-moi, vous remarquerez vraiment cette courbe).

Autres lectures

Ne vous arrêtez pas là ; si la plupart des conseils que vous recevez sur la réduction des fichiers journaux sont intrinsèquement mauvais, voire potentiellement désastreux, certaines personnes se soucient davantage de l'intégrité des données que de la libération d'espace disque.

Un billet de blog que j'ai écrit il y a quatre ans, lorsque j'ai vu fleurir quelques billets du type "voici comment réduire le fichier journal .

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

Un article 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 rédigé une excellente réponse qui couvre également certains de ces aspects, y compris les raisons pour lesquelles vous ne serez peut-être pas en mesure de réduire immédiatement votre fichier journal. .

Answer 2

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 : http://msdn.microsoft.com/en-us/library/ms189493.aspx

vous devriez d'abord faire une sauvegarde

Answer 3

CLAUSE DE NON-RESPONSABILITÉ : Veuillez lire attentivement les commentaires ci-dessous, et je suppose 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 les situations où cela n'est PAS une solution adéquate ou optimale, veuillez commenter ci-dessous

---

• Faites un clic droit sur le nom de la base de données.

• Sélectionnez Tâches -> Réduire -> Base de données.

• Cliquez ensuite sur OK !

J'ouvre généralement le répertoire de l'Explorateur Windows contenant les fichiers de la base de données afin de pouvoir constater immédiatement l'effet.

J'étais en fait assez surpris que cela fonctionne ! Normalement, j'ai déjà utilisé DBCC, mais je viens d'essayer et cela n'a rien réduit, alors j'ai essayé l'interface graphique (2005) et cela a bien fonctionné - libérant 17 Go en 10 secondes.

Edit : En mode de récupération complète, cela peut ne pas fonctionner, vous devez donc soit sauvegarder le journal en premier, soit passer en mode de récupération simple, puis réduire le fichier. Merci à @onupdatecascade pour cela].

--

ps : Je comprends ce que certains ont commenté concernant les dangers de ceci, 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. Donc, s'il vous plaît, prenez en considération ce qu'est votre environnement, et comment cela affecte votre stratégie de sauvegarde et la sécurité de votre travail avant de continuer. Je n'ai fait qu'indiquer aux gens une fonction fournie par Microsoft !

Answer 4

[la réponse ajoute plus de détails que demandé mais j'espère que c'est utile].

Vous trouverez ci-dessous un script pour réduire le journal des transactions, mais je vous recommande vivement de sauvegarder le journal des transactions avant de le réduire.

Si vous vous contentez de réduire les fichiers, vous allez perdre une tonne de données qui pourraient vous sauver la vie en cas de catastrophe. Le journal des transactions contient beaucoup de données utiles qui peuvent être lues à l'aide d'un lecteur de journal des transactions tiers (il peut être lu manuellement mais avec un effort extrême). Le journal des transactions est également indispensable en cas de récupération ponctuelle. Ne le jetez pas, mais assurez-vous de le sauvegarder au préalable.

Voici plusieurs messages dans lesquels des personnes ont utilisé les données stockées dans le journal des transactions pour effectuer une récupération.

Comment visualiser les journaux des transactions dans sql server 2008 ?

Lire le fichier journal (*.LDF) dans sql server 2008

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 en exécutant les commandes ci-dessus

“Cannot shrink log file (log file name) because the logical
log file located at the end of the file is in use “

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

Answer 5

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

Si vous utilisez SQL 7 ou 2000, vous pouvez activer "truncate log on checkpoint" dans l'onglet des 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 point dans le temps.