Réduire un fichier

Article
09/27/2023

S’applique à :SQL Server Azure SQL Database 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.

Limitations et restrictions

Le fichier de données primaire ne peut pas être réduit à la taille du fichier principal dans la base de données de modèle.

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 fichier de réduction 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éduction 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éduction expirera finalement après une minute et se ferme en mode silencieux, empêchant les autres requêtes d’être bloquées. WAIT_AT_LOW_PRIORITY s’applique aux fichiers de données (.mdf &.ndf). Elle 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 .

Avec SQL Server Management Studio (SSMS)

Pour réduire un fichier de données ou de journal à l’aide de SSMS

Dans l’ Explorateur d’objets, connectez-vous à une instance du moteur de base de données SQL Server et développez-la.
Développez Bases de données , puis cliquez avec le bouton droit sur la base de données à réduire.
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 alloué actuel ou plus que les étendues totales allouées 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 cliquez sur 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.
Sélectionnez le type et le nom du fichier.
É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.
É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.
É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.
É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.
Cliquez sur OK.

Utilisation de Transact-SQL

Pour réduire un fichier de données ou de journal à l’aide de Transact-SQL

Connectez-vous au Moteur de base de données.
Dans la barre d’outils standard, sélectionnez Nouvelle requête.
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