SQLShack

Cet article explore l’utilisation de plusieurs fichiers journaux de transactions SQL Server et le processus de suppression du fichier journal de transactions secondaire.

Introduction

Par défaut, une base de données SQL Server contient un fichier de données principal et un fichier journal des transactions. C’est une bonne pratique de configurer plusieurs fichiers de données et de diviser les données entre plusieurs fichiers de données. Nous pouvons avoir ces fichiers de données dans des disques de stockage séparés pour avoir plusieurs E/S. Il aide à la gestion des données, améliore les performances, planifie les approches de sauvegarde en fonction des groupes de fichiers.

Nous pouvons également avoir plusieurs fichiers journaux de transactions pour une base de données.

SQL Server utilise les fichiers journaux de transactions multiples dans un ordre séquentiel. Supposons que nous ayons deux fichiers journaux de transactions. SQL Server utilise un fichier journal à la fois et une fois qu’il est plein, SQL Server utilise un autre fichier journal. Puisque SQL Server ne l’utilise pas en parallèle, nous n’en retirons aucun avantage en termes de performances. Idéalement, nous ne devrions avoir qu’un seul fichier journal des transactions par base de données.

Supposons que nous ayons une base de données de production et que nous recevons une alerte critique indiquant que le disque manque d’espace. Après enquête, nous avons découvert que ce lecteur contient un fichier journal des transactions. En raison de transactions actives, ce fichier journal des transactions SQL Server est plein. Nous avons essayé de réduire le fichier journal, mais cela n’a pas fonctionné. Nous sauvegardons également le journal des transactions, mais en raison de transactions actives, il n’a pas pu libérer l’espace nécessaire. L’ajout d’un autre fichier journal des transactions sur un disque séparé avec de l’espace libre résoudra ce problème.

Comme SQL Server utilise le mode série pour écrire des données dans un fichier journal des transactions, nous devrions supprimer le fichier journal supplémentaire plus tard. Explorons le processus de suppression d’un fichier journal supplémentaire.

Créez une base de données avec plusieurs fichiers Journaux de transactions SQL Server

Connectez-vous à une instance SQL dans SQL Server Management Studio. Cliquez avec le bouton droit sur le nœud Bases de données dans le volet Explorateur d’objets et cliquez sur la commande Nouvelle base de données:

Il ouvre la nouvelle fenêtre de base de données. Spécifiez un nom de base de données et ajoutez un autre fichier journal des transactions. À des fins de test, nous avons désactivé la croissance automatique des fichiers journaux de transactions. La taille initiale du fichier journal des transactions est de 8 Mo:

Une fois que tout est défini, cliquez sur le bouton OK pour créer une base de données dans un répertoire de fichiers de données/journaux par défaut. Créer une table et insérer des données dans:

Afficher l’état du fichier journal virtuel dans les fichiers journaux de transactions SQL Server

Dans l’article, Architecture du journal des transactions SQL Server, nous avons exploré l’interne du fichier journal des transactions. Chaque fichier journal des transactions se compose de plusieurs fichiers journaux virtuels. Un fichier journal des transactions est une combinaison de plusieurs fichiers journaux virtuels (VLF). La capture d’écran suivante montre l’architecture physique et logique du fichier journal:

SQL Server stats une base de données avec VLF minimum en fonction de la taille initiale du journal et du fichier de croissance automatique (basé sur la configuration de croissance automatique). Dans l’image suivante, nous obtenons un aperçu du fichier journal des transactions SQL Server:

Dans le cas d’un fichier journal de transaction unique, SQL Server utilise un chemin de fichier journal virtuel circulaire. Nous pouvons vérifier le nombre de fichiers journaux virtuels et leur état en utilisant les éléments suivants:

1. DBCC LOGINFO(‘Database’) – C’est une ancienne instruction et fonctionne avec toutes les versions de SQL Server
2. Vue de gestion dynamique sys.dm_db_log_info(DBID). Il est disponible à partir de SQL Server 2016 SP2 ou version ultérieure

Toutes les commandes peuvent être utilisées pour la vérification VLF. Pour cet article, nous utiliserons la vue de gestion dynamique (DMV):

Dans la capture d’écran ci-dessus, nous avons vérifié que notre exemple de base de données contient deux fichiers journaux de transactions (file_id 2 et file_id 3).

• File_id 2 a un VLF actif (vlf_active=1 et vlf_status=2)
• File_id 3 n’a pas de VLF actif (vlf_active=0 et vlf_status=2)

Insérons quelques enregistrements supplémentaires dans la table afin que le VLF actif change:

La surveillance de l’utilisation de l’espace du journal des transactions et l’état du VLF seront effectués avec DMV (sys.dm_db_log_space_usage):

• Statut VLF

  1 2	Dans ce cas, il est POSSIBLE de sélectionner DB_name() comme NOM de base de données, File_ID comme NOM de fichier transaction_log_file_ID, vlf_active, vlf_status À PARTIR DE sys.dm_db_log_info(DB_ID(‘MultileLogFiles’));

• Utilisation du journal des transactions (espace utilisé et libre)

  1 2 3 4 5

  SÉLECTIONNEZ total_log_size_in_bytes *1.0/1024/1024 total_log_size_in_MB, utilisé_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes – used_log_space_in_bytes)*1.0/1024/1024 EN TANT QUE free_log_space_in_MB DEPUIS sys.dm_db_log_space_usage;

Nous obtenons la sortie suivante des requêtes ci-dessus:

• Sur le côté gauche, nous voyons qu’une fois que le fichier journal principal (file_id 2) devient plein, il passe au fichier journal suivant (file_id 3). À ce stade, nous avons VLF actif dans les fichiers journaux primaires et secondaires. Dans une base de données avec un modèle de récupération complet, nous avons besoin d’une sauvegarde du journal des transactions afin qu’il marque VLF comme inactif
• La sortie de droite (en utilisant DMV) affiche le fichier de journal des transactions espace utilisé 8,51 Mo. Comme vous vous en souvenez, nous avons défini une taille de 8 Mo pour chaque fichier journal des transactions et nous avons désactivé la croissance automatique des journaux. Lorsque le fichier journal principal est plein (à 8 Mo), SQL Server passe au fichier journal des transactions secondaire

Supprimer le fichier journal des transactions SQL Server secondaire

Pour supprimer le fichier journal des transactions secondaire (id de fichier 3), nous utiliserons l’instruction Alter database modifiée.

Nous allons ajouter la clause REMOVE FILE et spécifier le fichier que nous voulons supprimer:

L’exécution de cette instruction entraînera le message d’erreur suivant:

Remarque : Le fichier journal des transactions actif ne peut pas être supprimé.

Auparavant, nous avons vu qu’une fois le fichier journal principal rempli, SQL Server utilise le fichier journal secondaire. Nous devons vider un journal de transactions secondaire afin de pouvoir le supprimer.

Dans la base de données SQL avec un modèle de récupération complet, nous utilisons des sauvegardes du journal des transactions afin que SQL Server puisse tronquer les journaux. Nous pourrions avoir besoin de plusieurs sauvegardes de journaux pour cela en fonction de la taille du journal des transactions, du VLF actif et de la transaction active.

Effectuons une sauvegarde complète de la base de données et du journal des transactions:

• Sauvegarde complète de la base de données

  1	sauvegarde de la base de données MultipleLogFiles sur le disque = ‘C:\Temp\MultipleLogFiles .bak’

• Sauvegarde du journal des transactions

  1	journal de sauvegarde MultipleLogFiles sur le disque = ‘C:\Temp\MultipleLogFiles_log .trn’

Une fois la sauvegarde terminée, vérifiez l’état du VLF. Le VLF actif doit être dans le premier fichier journal (primaire) afin que nous puissions supprimer le fichier journal secondaire. Nous avons vérifié que seul le fichier journal principal (file_id 2) est actif (statut VLF 2):

Maintenant, le fichier journal des transactions secondaire doit être supprimé sans aucun problème. Exécutons à nouveau l’instruction Alter database modifiée. Le fichier journal des transactions secondaire sera supprimé:

Une fois le fichier journal de transaction secondaire supprimé, vérifiez-le à l’aide de l’interface graphique ainsi que de T-SQL avec system view sys.fichiers de données:

Le fichier journal des transactions secondaire supprimé est toujours présent selon la capture d’écran suivante:

Maintenant, faites un clic droit sur la base de données et affichez les fichiers existants. Nous voyons également le fichier journal des transactions supprimé dans l’interface graphique. Mais pourquoi?

Exécutons à nouveau l’instruction Alter database modifiée et voyons si SQL Server supprime à nouveau le fichier journal des transactions.

Nous avons reçu le message que SQL Server n’a pas pu trouver le fichier journal spécifié:

SQL Server supprime le fichier journal des transactions après une sauvegarde ultérieure du journal. Prenons une autre sauvegarde du journal et vérifions que le fichier journal des transactions existe toujours:

Vérifiez le fichier journal des transactions dans les méthodes GUI et T-SQL. Nous voyons que le fichier journal des transactions supprimé n’apparaît pas maintenant.

Conclusion

Dans cet article, nous avons exploré l’utilisation du journal des transactions SQL Server secondaire et le processus de suppression. Vous devez éviter d’utiliser plusieurs fichiers journaux de transactions, en particulier sur la base de données de production. Vous devez effectuer une sauvegarde de base de données avant de planifier une activité et le faire en heures non productives.

• Auteur
• Messages récents

En tant que formateur certifié MCSA et certifié Microsoft à Gurgaon, en Inde, avec 13 ans d’expérience, Rajendra travaille pour diverses grandes entreprises se concentrant sur l’optimisation des performances, la surveillance, la haute disponibilité et les stratégies et la mise en œuvre de reprise après sinistre. Il est l’auteur de centaines d’articles faisant autorité sur SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS / Amazon RDS, Git et les technologies connexes qui ont été consultés par plus de 10 millions de lecteurs à ce jour.
Il est le créateur de l’une des plus grandes collections en ligne gratuites d’articles sur un seul sujet, avec sa série de 50 parties sur SQL Server Always On Availability Groups. Sur la base de sa contribution à la communauté SQL Server, il a été récompensé par divers prix dont le prestigieux « Meilleur auteur de l’année » en 2020 et 2021 à SQLShack.
Raj est toujours intéressé par les nouveaux défis, donc si vous avez besoin d’aide pour consulter un sujet traité dans ses écrits, il peut être joint à rajendra.gupta 16 @ gmail.com
Voir tous les messages de Rajendra Gupta

Derniers articles de Rajendra Gupta (tout voir)

• Utiliser des modèles ARM pour déployer des instances de conteneur Azure avec des images Linux SQL Server – 21 décembre 2021
• Accès bureau à distance pour AWS RDS SQL Server avec Amazon RDS Custom – 14 décembre 2021
• Stocker des fichiers SQL Server dans un stockage Persistant pour les instances de conteneur Azure – Décembre 10, 2021