DBCC SHRINKFILE (Transact-SQL)

Réduit la taille du fichier journal ou de données spécifié pour la base de données actuelle, ou vide un fichier en déplaçant les données depuis le fichier spécifié vers d'autres fichiers du même groupe de fichiers, permettant ainsi de supprimer le fichier de la base de données. Vous pouvez réduire un fichier à une taille inférieure à la taille spécifiée lors de sa création. Dans ce cas, la nouvelle valeur correspond à la taille de fichier minimale.

Conventions de la syntaxe de Transact-SQL

Syntaxe

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

Arguments

• file_name
  Nom logique du fichier dont la taille doit être réduite.

• file_id
  Numéro d'identification (ID) du fichier à réduire. Pour obtenir l'ID d'un fichier, utilisez la fonction système FILE_IDEX ou interrogez l'affichage catalogue sys.database_files dans la base de données actuelle.

• target_size
  Taille en octets du fichier, exprimée sous la forme d'un entier. Si ce paramètre n'est pas spécifié, DBCC SHRINKFILE réduit la taille à la taille de fichier par défaut. La taille par défaut est la taille spécifiée lors de la création du fichier.

  Notes

  Vous pouvez réduire la taille par défaut d'un fichier vide en utilisant DBCC SHRINKFILE target_size. Par exemple, si vous créez un fichier de 5 Mo, puis que vous le réduisez à 3 Mo pendant que le fichier est encore vide, la taille de fichier par défaut est fixée à 3 Mo. Cela s'applique uniquement aux fichiers vides qui n'ont jamais contenu des données.

  Si target_size est spécifié, DBCC SHRINKFILE tente de réduire la taille du fichier à la taille spécifiée. Les pages utilisées dans la partie du fichier à libérer sont transférées vers un espace libre disponible dans la partie du fichier conservée. Par exemple, s'il s'agit d'un fichier de données de 10 Mo et que vous affectez la valeur 8 à target_size pour les opérations DBCC SHRINKFILE, toutes les pages utilisées dans les deux derniers Mo du fichier sont transférées dans les pages non allouées des 8 premiers Mo du fichier. DBCC SHRINKFILE ne réduit pas un fichier au-delà de la taille nécessaire pour stocker les données dans le fichier. De fait, s'il s'agit d'un fichier de 10 Mo et que 7 Mo sont utilisés, l'instruction DBCC SHRINKFILE avec une valeur de 6 pour target_size réduit la taille à 7 Mo et non pas à 6 Mo.

• EMPTYFILE
  Permet la migration de toutes les données du fichier spécifié vers d'autres fichiers dans le même groupe de fichiers. Étant donné que le moteur de base de données SQL Server 2005 ne permet plus de placer des données dans le fichier vide, celui-ci peut être supprimé à l'aide de l'instruction ALTER DATABASE.

• NOTRUNCATE
  Déplace des pages allouées depuis la fin d'un fichier de données vers les pages non allouées du début du fichier, avec ou sans spécification de target_percent. L'espace libre à la fin du fichier n'est pas restitué au système d'exploitation et la taille physique du fichier ne change pas. Ainsi, lorsque l'option NOTRUNCATE est spécifiée, le fichier ne paraît pas être réduit.

  NOTRUNCATE n'est applicable qu'aux fichiers de données. Les fichiers journaux ne sont pas affectés.

• TRUNCATEONLY
  Libère pour le système d'exploitation tout l'espace libre à la fin du fichier, mais n'effectue aucun déplacement de page au sein du fichier. Le fichier de données est réduit seulement jusqu'à la dernière extension allouée.

  L'option target_size est ignorée si elle est spécifiée avec TRUNCATEONLY.

  TRUNCATEONLY n'est applicable qu'aux fichiers de données.

• WITH NO_INFOMSGS
  Supprime tous les messages d'information.

Ensembles de résultats

Le tableau suivant décrit les colonnes de l'ensemble de résultats.

Nom de la colonne	Description
DbId	Numéro d'identification de base de données du fichier que le moteur de base de données tente de réduire.
FileId	Numéro d'identification du fichier que le moteur de base de données a tenté de réduire.
CurrentSize	Nombre de pages de 8 Ko que le fichier occupe actuellement.
MinimumSize	Nombre de pages de 8 Ko que le fichier pourrait occuper au minimum. Ceci correspond à la taille minimale ou à la taille de création d'un fichier.
UsedPages	Nombre de pages de 8 Ko que le fichier utilise actuellement.
EstimatedPages	Nombre de pages de 8 Ko estimé par le moteur de base de données auquel la taille du fichier peut être ramenée.

Notes

DBCC SHRINKFILE s'applique aux fichiers de la base de données active. Pour plus d'informations sur le changement de base de données active, consultez USE (Transact-SQL).

Les opérations DBCC SHRINKFILE peuvent être arrêtées à n'importe quel stade du processus, chaque travail terminé étant conservé.

Une erreur est générée lorsque la fonction DBCC SHRINKFILE échoue.

Il n'est pas nécessaire que la base de données à réduire soit en mode mono-utilisateur ; d'autres utilisateurs peuvent travailler dans la base de données lorsque la taille du fichier est réduite. Il n'est pas nécessaire d'exécuter l'instance de SQL Server en mode mono-utilisateur pour réduire les bases de données système.

Réduction d'un fichier journal

Pour les fichiers journaux, le Moteur de base de données utilise target_size pour calculer la taille cible du fichier journal complet. La valeur en pourcentage de target_size correspond donc à l'espace libre dans le journal après l'opération de réduction. La taille cible de l'ensemble du journal est ensuite convertie dans la taille cible de chaque fichier journal. DBCC SHRINKFILE tente de ramener immédiatement la taille de chaque fichier journal physique à la taille cible. Cependant, si une partie du journal logique se trouve dans les journaux virtuels au-delà de la taille cible, le moteur de base de données libère autant d'espace que possible, puis émet un message d'information. Le message décrit les actions à effectuer pour déplacer le journal logique à partir des journaux virtuels à la fin du fichier. Lorsque les actions sont effectuées, DBCC SHRINKFILE peut être utilisé pour libérer l'espace restant. Pour plus d'informations, consultez Réduction du journal des transactions.

Comme un fichier journal ne peut être réduit que jusqu'à une limite virtuelle, il arrive qu'il ne soit pas possible de réduire un fichier journal à une taille inférieure à celle d'un fichier journal virtuel, même s'il n'est pas utilisé. La taille du fichier journal virtuel est choisie dynamiquement par le moteur de base de données au moment de la création ou de l'extension des fichiers journaux. Pour plus d'informations sur les fichiers du journal virtuel, consultez Architecture physique du journal des transactions.

Meilleures pratiques

Prenez en compte les informations suivantes lorsque vous envisagez de réduire un fichier :

• Une opération de réduction de taille de fichier est plus efficace après l'exécution d'une opération qui crée une grande quantité d'espace inutilisé, telle qu'une troncature de table ou une suppression de 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'une base de données et que vous constatez que la taille augmente de nouveau, cela indique que l'espace qui a été réduit est nécessaire pour les opérations courantes. Dans ce cas, la réduction de la taille de la base de données ne sert à rien.

• Une opération de réduction ne conserve pas l'état de fragmentation des index de la base de données, et augmente généralement la fragmentation. Il s'agit là d'une raison supplémentaire pour ne pas réduire la taille de la base de données de manière répétitive.

Dépannage

Cette section décrit comment diagnostiquer et corriger les problèmes qui peuvent se produire lors de l'exécution de la commande DBCC SHRINKFILE.

La taille du fichier n'est pas réduite

Si l'opération de réduction s'exécute sans erreur, mais que la taille du fichier ne semble pas avoir changé, vérifiez que le fichier dispose d'un espace adéquat à supprimer, en effectuant l'une des opérations suivantes :

• Exécutez la requête suivante.

  SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
  FROM sys.database_files;
  

• Exécutez la commande DBCC SQLPERF pour restituer l'espace utilisé dans le journal des transactions.

Si l'espace disponible est insuffisant, l'opération de réduction ne peut pas réduire davantage la taille du fichier.

En règle générale, c'est la taille du fichier journal qui semble impossible à réduire. Ceci est généralement dû à un fichier journal qui n'a pas été tronqué. Vous pouvez tronquer le journal en attribuant la valeur SIMPLE au mode de récupération de base de données, ou en sauvegardant le journal et en réexécutant l'opération DBCC SHRINKFILE. Pour plus d'informations, consultez Troncation du journal des transactions et Réduction du journal des transactions.

L'opération de réduction est bloquée

Il est possible que les opérations de réduction soient bloquées par une transaction en cours d'exécution sous un niveau d'isolation basé sur les versions de ligne. Par exemple, si une importante opération de suppression exécutée sous un niveau d'isolation basé sur les versions de ligne se déroule parallèlement à une opération DBCC SHRINK DATABASE, l'opération de réduction attendra la fin de l'opération de suppression pour réduire la taille des fichiers. Dans ce cas, les opérations DBCC SHRINKFILE et DBCC SHRINKDATABASE envoient un message d'information (5202 pour SHRINKDATABASE et 5203 pour SHRINKFILE) dans le journal des erreurs de SQL Server toutes les cinq minutes au cours de la première heure, puis toutes les heures. Par exemple si le journal des erreurs contient le message d'erreur :

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot 
transaction with timestamp 15 and other snapshot transactions linked to 
timestamp 15 or with timestamps older than 109 to finish.

cela signifie que l'opération de réduction est bloquée par des transactions d'instantané ayant des valeurs d'horodateur plus anciennes que 109, qui est le numéro de la dernière transaction que l'opération de réduction a effectuée. Cela indique également que les colonnes transaction_sequence_num ou first_snapshot_sequence_num dans la vue de gestion dynamique sys.dm_tran_active_snapshot_database_transactions contiennent une valeur de 15. Si les colonnes transaction_sequence_num ou first_snapshot_sequence_num dans la vue contiennent un numéro inférieur à la dernière transaction effectuée par une opération de réduction (109), l'opération de réduction attendra que ces transactions soient terminées.

Pour résoudre ce problème, vous pouvez effectuer l'une des opérations suivantes :

• Achevez la transaction qui bloque l'opération de réduction.

• Achevez l'opération de réduction. Si vous achevez l'opération de réduction, tout travail accompli est conservé.

• Laissez simplement l'opération de réduction attendre que la transaction bloquante s'achève.

Pour plus d'informations sur le journal des erreurs SQL Server, consultez Consultation du journal des erreurs de SQL Server.

Autorisations

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

Exemple

A. Réduction d'un fichier de données à une taille cible spécifiée

L'exemple suivant ramène la taille du fichier de données nommé DataFile1 de la base de données utilisateur UserDB à 7 Mo.

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

B. Réduction d'un fichier journal à une taille cible spécifiée

L'exemple suivant ramène la taille du fichier journal de la base de données AdventureWorks2008R2 à 1 Mo. Pour que la commande DBCC SHRINKFILE puisse réduire la taille du fichier, le système tronque d'abord celui-ci en attribuant la valeur SIMPLE au mode de récupération de base de données.

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

C. Troncation d'un fichier de données

L'exemple suivant tronque le fichier de données primaire dans la base de données AdventureWorks2008R2. Le système interroge l'affichage catalogue sys.database_files afin d'obtenir la valeur file_id du fichier de données.

USE AdventureWorks2008R2;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Vidage d'un fichier

L'exemple suivant illustre la procédure qui permet de vider un fichier de manière à ce qu'il puisse être supprimé de la base de données. Pour que cet exemple fonctionne, un fichier de données est d'abord créé et celui-ci est censé contenir des données.

USE AdventureWorks2008R2;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2 
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE Test1data;
GO

Voir aussi

Tâches

Procédure : réduire un fichier (SQL Server Management Studio)

Référence

ALTER DATABASE (Transact-SQL)

DBCC (Transact-SQL)

DBCC SHRINKDATABASE (Transact-SQL)

FILE_ID (Transact-SQL)

sys.database_files (Transact-SQL)

Concepts

Architecture des fichiers et des groupes de fichiers

Fonctionnement des pages et étendues