Réduire un fichier

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance

Cet article explique comment réduire un fichier de données ou journal dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL.

La réduction des fichiers de données permet de récupérer de l'espace en déplaçant des pages de données de la fin du fichier vers un espace inoccupé plus proche de l'avant du fichier. Quand une quantité d'espace libre suffisante est créée à la fin du fichier, des pages de données à la fin du fichier peuvent être désallouées et retournées au système de fichiers.

Limites

• La taille du fichier de données primaire ne peut pas être inférieure à celle du fichier primaire dans la base de données model.

Recommandations

• Une opération de réduction est plus efficace après une opération qui crée une grande quantité d’espace de stockage inutilisé, par exemple une instruction DELETE ou une opération TRUNCATE TABLE ou DROP TABLE.

• Un certain espace libre doit exister pour les opérations quotidiennes courantes pour la plupart des bases de données. Si vous réduisez plusieurs fois la taille d’un fichier de bases de données et que vous constatez que la taille augmente de nouveau, cela indique que l’espace disponible est nécessaire pour les opérations courantes. Dans ce cas, la réduction de la taille du fichier de bases de données ne sert à rien. Les événements de croissance automatique nécessaires pour augmenter la taille du fichier de bases de données entravent le niveau de performance.

• Les données qui sont déplacées pour réduire un fichier peuvent être dispersées à n'importe quel emplacement disponible dans le fichier. Cela provoque la fragmentation de l'index et peut ralentir les performances des requêtes qui recherchent une plage de l'index. Pour éliminer la fragmentation, reconstruisez les index dans le fichier après réduction.

• Sauf en cas de besoin précis, n’attribuez pas la valeur ON à l’option de base de données AUTO_SHRINK.

Notes

Les opérations de réduction en cours peuvent bloquer d’autres requêtes sur la base de données et être bloquées par des requêtes déjà en cours. Introduites dans SQL Server 2022 (16.x), les opérations de réduction de fichier ont une option WAIT_AT_LOW_PRIORITY. Cette fonctionnalité est une nouvelle option supplémentaire pour DBCC SHRINKDATABASE et DBCC SHRINKFILE. Si une nouvelle opération de rétrécissement en mode WAIT_AT_LOW_PRIORITY ne peut pas obtenir les verrous nécessaires en raison d’une requête de longue durée déjà en cours, l’opération de rétrécissement finira par expirer après une minute et se fermer silencieusement, empêchant ainsi le blocage d’autres requêtes. WAIT_AT_LOW_PRIORITY s’applique aux fichiers de données (.mdf &ndf). Il ne s’applique pas aux fichiers journaux des transactions. Pour plus d’informations, consultez DBCC SHRINKFILE.

autorisations

Nécessite l’appartenance au rôle de serveur fixe sysadmin ou au rôle de base de données fixe db_owner .

Utiliser SQL Server Management Studio (SSMS)

Réduire un fichier de données ou un fichier journal en utilisant SSMS

1. Dans l’ Explorateur d’objets, connectez-vous à une instance du moteur de base de données SQL Server et développez-la.

2. Développez Bases de données , puis cliquez avec le bouton droit sur la base de données à réduire.

3. Pointez sur Tâches, puis sur Réduire, puis sélectionnez Fichiers.

   Sauvegarde de la base de données
   Affiche le nom de la base de données des sélectionnée.

   Type de fichier
   Sélectionnez le type du fichier. Les choix possibles sont Données et Journal . La sélection par défaut est Données. La sélection d'un autre type de groupe de fichiers modifie en conséquence les sélections des autres champs.

   Groupe de fichiers
   Sélectionnez un groupe de fichiers dans la liste des groupes de fichiers associés au Type de fichier sélectionné auparavant. La sélection d'un autre groupe de fichiers modifie en conséquence les sélections des autres champs.

   Nom de fichier
   Sélectionnez un fichier dans la liste des fichiers disponibles pour le groupe de fichiers et le type de fichier choisis.

   Lieu
   Affiche le chemin d'accès complet au fichier actuellement sélectionné. Le chemin d'accès n'est pas modifiable, mais il peut être copié dans le presse-papiers.

   Espace actuellement alloué
   Pour les fichiers de données, affiche l'espace actuellement alloué. Pour les fichiers journaux, ce champ affiche l'espace actuellement alloué qui est calculé à partir de la sortie de DBCC SQLPERF(LOGSPACE).

   Espace libre disponible
   Pour les fichiers de données, ce champ affiche l'espace libre actuellement disponible qui est calculé à partir de la sortie de DBCC SHOWFILESTATS(fileid). Pour les fichiers journaux, ce champ affiche l'espace libre actuellement disponible qui est calculé à partir de la sortie de DBCC SQLPERF(LOGSPACE).

   Libérer l'espace inutilisé
   Tout espace inutilisé dans les fichiers est libéré pour le système d'exploitation et le fichier est réduit à la dernière extension allouée, ce qui en réduit la taille sans toucher aux données. Aucune tentative de réaffectation des lignes aux pages non allouées n'est entreprise.

   Réorganiser les pages avant de libérer l'espace inutilisé
   Équivaut à exécuter DBCC SHRINKFILE en spécifiant la taille du fichier cible. Lorsque cette option est activée, l'utilisateur doit spécifier la taille du fichier cible dans la zone Réduire le fichier à .

   Réduire le fichier à
   Spécifie la taille du fichier cible pour l'opération de réduction. La taille ne peut pas être inférieure à l'espace actuellement alloué ou supérieure à l'extension totale qui est allouée au fichier. Lorsqu'une valeur supérieure à la valeur minimale ou maximale est entrée, la valeur min ou max est rétablie lorsqu'un autre élément est sélectionné ou que vous sélectionnez un bouton de la barre d'outils.

   Vider le fichier en effectuant une migration des données vers d'autres fichiers dans le même groupe de fichiers
   Migre toutes les données du fichier spécifié. Cette option permet au fichier d'être supprimé à l'aide de l'instruction ALTER DATABASE. Elle équivaut à exécuter DBCC SHRINKFILE avec l'option EMPTYFILE. EMPTYFILE n’est pas pris en charge dans la base de données Azure SQL ou Azure SQL Database Hyperscale.

4. Sélectionnez le type et le nom du fichier.

5. Éventuellement, activez la case à cocher Libérer l'espace inutilisé .

   En activant cette case d'option, tout espace inutilisé dans les fichiers de données est libéré vers le système d'exploitation et le fichier est réduit à la dernière étendue allouée, sans déplacement de données.

6. Éventuellement, activez la case d'option Réorganiser les pages avant de libérer de l'espace inutilisé . Si cette case d'option est activée, la valeur Réduire le fichier à doit être spécifiée. Cette option est désactivée par défaut.

   En activant cette case d'option, tout espace inutilisé dans les fichiers de données est libéré vers le système d'exploitation et, dans la mesure du possible, les lignes sont déplacées dans des pages non allouées.

7. Éventuellement, entrez le pourcentage maximal d'espace libre à laisser dans le fichier de base de données après la réduction de la base de données. Les valeurs autorisées sont comprises entre 0 et 99. Cette option est disponible uniquement lorsque Réorganiser les pages avant de libérer de l'espace inutilisé est activée.

8. Éventuellement, activez la case d'option Vider le fichier en effectuant une migration des données vers d'autres fichiers dans le même groupe de fichiers .

   L'activation de cette case d'option déplace toutes les données du fichier spécifié dans d'autres fichiers du groupe de fichiers. Le fichier vide peut alors être supprimé. Cette case d'option revient à exécuter DBCC SHRINKFILE avec l'option EMPTYFILE.

9. Cliquez sur OK.

Utiliser Transact-SQL

Réduire un fichier de données ou un fichier journal en utilisant Transact-SQL

1. Connectez-vous au Moteur de base de données.

2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple utilise DBCC SHRINKFILE pour réduire la taille d'un fichier de données nommé DataFile1 dans la base de données UserDB à 7 Mo.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Contenu connexe

• Considérations relatives aux paramètres de croissance automatique et de réduction automatique dans SQL Server
• Groupes de fichiers et fichiers de base de données
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• FILE_ID (Transact-SQL)
• DBCC SHRINKDATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• Supprimer des données ou des fichiers journaux d’une base de données
• Réduire une base de données