Impossible de réduire le journal des transactions

Symptôme
 Je n'étais pas en mesure de réduire le journal des transactions de ma base de données. Je recevais le message d'erreur suivant lorsque j'exécutais un DBCC SHRINKFILE (N'LogicalName' , NOTRUNCATE)

Impossible de réduire le fichier journal 2 (XXLogicalNameXX), car tous les fichiers journaux logiques sont utilisés.
Impossible de réduire le fichier journal 2 (XXLogicalNameXX), car le fichier journal logique situé à la fin du fichier est en cours d'utilisation.

Environnement : ma base de données était l'éditeur de ma réplication transactionnelle et le modèle de récupération était en mode simple.

Etape de dépannage
Etape 1 : Si votre modèle de récupération est en mode FULL, assurez-vous que vous faites des sauvegardes du journal de transaction. BACKUP LOG databaseName TO DISK='C:\fileName.TRN'

Etape2 : Vérifiez l'espace du journal utilisé avec la commande DBCC SQLPERF (logspace). Avez-vous de l'espace libre ?

Etape3 : Utiliser l'instruction DBCC OPENTRAN  pour vérifier si une transaction est toujours active. Si oui, tuez là.

Etape4 : Vérifier la valeur de la colonne log_reuse_wait_desc

select name, database_id,recovery_model_desc,log_reuse_wait_desc from sys.databases where name LIKE 'yourDatabaseName'

Cause
Dans mon cas, la colonne  log_reuse_wait_desc renvoyait REPLICATION (voir la documentation Facteurs pouvant retarder la troncation du journal). Le journal n'a pas été tronqué car des enregistrements dans le journal sont en attente de réplication.

Assurez-vous de l'Agent Log Reader fonctionne ou utilisez sp_repldone pour marquer transactions distribuées. Typiquement l'agent Log Reader va analyser le journal de log, puis marquer chaque enregistrement du journal comme répliqué en exécutant sp_repldone.
Résolution
Quand j'ai essayé de faire la même chose manuellement, mon problème a été corrigé:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

Avec sp_repldone, toutes les transactions répliquées dans le journal sont marquées comme distribuées. Ceci est utile quand il ya des transactions répliquées dans le journal des transactions qui ne sont plus valides et que vous voulez tronquer le journal.

Si vous exécutez sp_repldone manuellement, vous pouvez invalider l'ordre et la cohérence des transactions. Donc, si vous n'êtes pas conscient de son impact, je vous recommande de supprimer votre publication, vos souscriptions. Puis, exécutez la commande réduire le fichier de transaction et recréer la réplication.

Reference
- Le journal des transactions augmente de manière inattendue ou est saturé sur un ordinateur SQL Server
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server
- Troncation du journal des transactions
- Réduction du journal des transactions
- How to use the DBCC SHRINKFILE statement to shrink the transaction log file

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |