DBCC SHRINKFILE (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Réduit la taille de fichier journal ou de données de la base de données active. Vous pouvez l’utiliser pour déplacer des données entre fichiers du même groupe de fichiers, ce qui a pour effet de supprimer le fichier d’origine et de permettre sa suppression de la base de données. Il est possible de réduire un fichier à une taille inférieure à celle qu’il avait à sa création, réinitialisant ainsi la taille de fichier minimale sur la nouvelle valeur.

Conventions de la syntaxe Transact-SQL

Syntaxe

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH 
  {     
      [ WAIT_AT_LOW_PRIORITY 
        [ ( 
            <wait_at_low_priority_option_list>
        )] 
      ] 
      [ , NO_INFOMSGS]
  }
]
       
< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>
 
< wait_at_low_priority_option > ::=
    ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Arguments

file_name

Nom logique du fichier à réduire.

file_id

Numéro d'identification (ID) du fichier à réduire. Pour récupérer 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 active.

target_size

Nombre entier représentant la nouvelle taille du fichier en mégaoctets. Si cette valeur n’est pas spécifiée ou si elle est égale à 0, DBCC SHRINKFILE réduit le fichier à la taille qu’il avait à sa création.

Vous pouvez réduire la taille par défaut d’un fichier vide avec 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.

Cette option n'est pas prise en charge pour les conteneurs de groupe de fichiers FILESTREAM.

Si elle est spécifiée, DBCC SHRINKFILE tente de réduire le fichier à la taille définie par target_size. Les pages utilisées dans la zone du fichier à libérer sont déplacées dans l’espace libre des zones conservées du fichier. Par exemple, dans un fichier de données de 10 Mo, une opération DBCC SHRINKFILE avec une valeur target_size égale à 8 déplace toutes les pages utilisées dans les 2 derniers mégaoctets du fichier vers les pages non allouées dans les 8 premiers mégaoctets du fichier. DBCC SHRINKFILE ne réduit pas un fichier au-delà de la taille des données stockées nécessaire. Par exemple, dans un fichier de 10 Mo où 7 Mo sont utilisés, une instruction DBCC SHRINKFILE avec une valeur target_size de 6 réduit la taille du fichier à 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. En d’autres termes, EMPTYFILE migre les données du fichier spécifié vers d’autres fichiers dans le même groupe de fichiers. EMPTYFILE permet de garantir qu’aucune nouvelle donnée ne peut être ajoutée au fichier, qui n’est pourtant pas en lecture seule. Vous pouvez utiliser l’instruction ALTER DATABASE pour supprimer un fichier. Si l’instruction ALTER DATABASE est employée pour modifier la taille de fichier, l’indicateur de lecture seule est réinitialisé et il devient possible d’ajouter des données.

Pour les conteneurs de groupes de fichiers FILESTREAM, il n’est pas possible de supprimer un fichier avec ALTER DATABASE tant que le récupérateur de mémoire FILESTREAM n'a pas été exécuté pour supprimer tous les fichiers inutiles d’un conteneur de groupes de fichiers que EMPTYFILE a copiés dans un autre conteneur. Pour plus d’informations, consultez sp_filestream_force_garbage_collection. Pour plus d’informations sur la suppression d’un conteneur FILESTREAM, consultez la section correspondante dans Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL)

NOTRUNCATE

Déplace des pages allouées de la fin d’un fichier de données vers les pages non allouées du début d’un fichier, que target_percent soit spécifié ou non. 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. Par conséquent, si NOTRUNCATE est spécifié, le fichier ne semble pas se réduire.

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

Cette option n'est pas prise en charge pour les conteneurs de groupe de fichiers FILESTREAM.

TRUNCATEONLY

Libère tout l'espace libre à la fin du fichier pour le système d'exploitation, 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.

target_size est ignoré s’il est spécifié avec TRUNCATEONLY.

L'option TRUNCATEONLY ne déplace pas d'informations dans le journal, mais supprime les fichiers journaux virtuels inactifs à la fin du fichier journal. Cette option n'est pas prise en charge pour les conteneurs de groupe de fichiers FILESTREAM.

WITH NO_INFOMSGS

Supprime tous les messages d'information.

WAIT_AT_LOW_PRIORITY avec opérations de réduction

S’applique à :SQL Server 2022 (16.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance

La fonctionnalité d’attente à basse priorité réduit la contention de verrouillage. Pour plus d’informations, consultez Compréhension des problèmes de concurrence avec DBCC SHRINKDATABASE.

Cette fonctionnalité est similaire à WAIT_AT_LOW_PRIORITY avec des opérations d’indexation en ligne, à quelques différences près.

• Vous ne pouvez pas spécifier l’option NONE de ABORT_AFTER_WAIT.

WAIT_AT_LOW_PRIORITY

S’applique à : SQL Server (SQL Server 2022 (16.x) et versions ultérieures) et Azure SQL Database.

Lorsqu’une commande de réduction est exécutée en mode WAIT_AT_LOW_PRIORITY, les nouvelles requêtes nécessitant des verrous de stabilité de schéma (Sch-S) ne sont pas bloquées par l’opération de réduction en attente (jusqu’au moment où l’opération de réduction cesse d’attendre et commence à s’exécuter). L’opération de réduction s’exécute lorsqu’elle peut obtenir un verrou de modification de schéma (Sch-M). Si une nouvelle opération de réduction en mode WAIT_AT_LOW_PRIORITY ne peut pas obtenir de verrou en raison d’une requête de longue durée, l’opération de réduction finit par expirer après 1 minute (par défaut) et se termine silencieusement.

Si une nouvelle opération de réduction en mode WAIT_AT_LOW_PRIORITY ne peut pas obtenir de verrou en raison d’une requête de longue durée, l’opération de réduction finit par expirer après 1 minute (par défaut) et se termine silencieusement. Cela se produit si l’opération de réduction ne peut pas obtenir le verrou Sch-M en raison d’une requête simultanée ou de requêtes contenant des verrous Sch-S. En cas de dépassement du délai d’attente, un message d’erreur 49516 est envoyé au journal des erreurs SQL Server. Par exemple : Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. À ce stade, vous pouvez simplement réessayer l’opération de réduction en mode WAIT_AT_LOW_PRIORITY en sachant qu’il n’y aura aucun impact sur l’application.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

S’applique à : SQL Server (SQL Server 2022 (16.x) et versions ultérieures) et Azure SQL Database.

• SELF

  Quittez l’opération de réduction des fichiers actuellement exécutée, sans effectuer aucune action.

• BLOCKERS

  Tuez toutes les transactions utilisateur qui bloquent l'opération de réduction des fichiers afin que l'opération puisse continuer. Avec l’option BLOCKERS, la connexion doit avoir l’autorisation ALTER ANY CONNECTION.

Jeux de résultats

Le tableau suivant décrit les colonnes du jeu de résultats.

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é. Si vous utilisez le paramètre EMPTYFILE et annulez l’opération, le fichier n’est pas marqué pour empêcher l’ajout de données supplémentaires.

Une erreur est générée en cas d’échec d’une opération DBCC SHRINKFILE.

D’autres utilisateurs peuvent travailler dans la base de données pendant la réduction des fichiers, même si la base de données n’est pas en mode mono-utilisateur. 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.

Lorsqu’elle est spécifiée avec WAIT_AT_LOW_PRIORITY, la demande de verrouillage Sch-M de l’opération de réduction attend avec une faible priorité lors de l’exécution de la commande pendant 1 minute. Si l’opération reste bloquée pendant cette durée, l’action ABORT_AFTER_WAIT spécifiée est exécutée.

Comprendre les problèmes de concurrence avec DBCC SHRINKFILE

Les commandes de réduction de bases de données et de fichiers peuvent entraîner des problèmes de concurrence, en particulier avec une maintenance active comme la reconstruction d’index ou sur des environnements OLTP occupés. Lorsque votre application exécute des requêtes sur des tables de bases de données, ces requêtes acquièrent et conservent un verrou de stabilité de schéma (Sch-S) jusqu’à ce que les requêtes terminent leurs opérations. Lorsque vous tentez de récupérer de l’espace durant une utilisation régulière, les opérations de réduction de bases de données et de fichiers nécessitent actuellement un verrou de modification de schéma (Sch-M) lors du déplacement ou de la suppression de pages IAM (Index Allocation Map), bloquant les verrous Sch-S nécessaires aux requêtes des utilisateurs. Par conséquent, les requêtes de longue durée bloquent une opération de réduction jusqu’à ce que ces requêtes se terminent. Cela signifie que toutes les nouvelles requêtes nécessitant des verrous Sch-S sont également mises en file d’attente derrière l’opération de réduction en attente et sont également bloquées, aggravant encore plus ce problème de concurrence. Cela peut impacter significativement le niveau de performance des requêtes d’application et compliquer la maintenance nécessaire pour réduire les fichiers de bases de données. Introduite dans SQL Server 2022 (16.x), la fonctionnalité d’attente à basse priorité pour les opérations de réduction résout ce problème en prenant un verrou de modification de schéma en mode WAIT_AT_LOW_PRIORITY. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY avec des opérations de réduction.

Pour plus d’informations sur les verrous Sch-S et Sch-M, consultez le guide de verrouillage des transactions et du contrôle de version de ligne.

Réduire un fichier journal

Dans le cas des fichiers journaux, le Moteur de base de données utilise target_size pour calculer la taille cible de l’ensemble du journal. Par conséquent, target_size correspond à l’espace libre du journal après l’opération de réduction. La taille cible de l'ensemble du journal est ensuite convertie en taille cible de chaque fichier journal. DBCC SHRINKFILE tente immédiatement de réduire la taille de chaque fichier journal physique à sa taille cible. Toutefois, 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 envoie 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. Une fois les actions exécutées, DBCC SHRINKFILE peut être utilisé pour libérer l’espace restant.

Comme un fichier journal ne peut être réduit que jusqu'à une limite de fichier journal virtuel, il n’est pas toujours possible de descendre au-dessous de cette taille limite, même si le fichier n'est pas utilisé. Le Moteur de base de données sélectionne dynamiquement la taille du fichier journal virtuel lorsque les fichiers journaux sont créés ou étendus.

Meilleures pratiques

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

• Une opération de réduction est plus efficace après une opération qui crée une grande quantité d'espace inutilisé, par exemple 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.

• Une opération de réduction ne conserve pas l'état de fragmentation des index de la base de données ; en général, elle augmente la fragmentation dans une certaine mesure. Il s'agit là d'une raison supplémentaire de ne pas réduire trop souvent la base de données.

• Réduisez plusieurs fichiers dans la même base de données de manière séquentielle plutôt que simultanément. La contention sur les tables système peut entraîner des blocages et des délais.

Dépanner

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.

Le fichier ne se réduit pas

Si la taille du fichier ne change pas après une opération de réduction sans erreur, essayez la procédure suivante pour vérifier que le fichier dispose de suffisamment d’espace libre :

• 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.

L'opération de réduction ne peut pas réduire la taille du fichier si l'espace libre est insuffisant.

C'est en général le fichier journal qui ne semble pas se réduire. La raison en est souvent qu’il n'a pas été tronqué. Pour tronquer le journal, vous pouvez définir le mode de récupération de la base de données sur SIMPLE, ou sauvegarder le journal avant de réexécuter l'opération DBCC SHRINKFILE.

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

Une transaction qui s’exécute sous un niveau d’isolement basé sur le contrôle de version de ligne peut bloquer les opérations de réduction. Par exemple, si une opération de suppression de grande envergure sous un niveau d'isolation basé sur le contrôle de version de ligne s’exécute en parallèle d’une opération DBCC SHRINKDATABASE, l'opération de réduction attend la fin de l'opération de suppression pour continuer. Quand ce blocage se produit, les opérations DBCC SHRINKFILE et DBCC SHRINKDATABASE consignent un message d’information (5202 pour SHRINKDATABASE et 5203 pour SHRINKFILE) dans le journal des erreurs SQL Server. Ce message est consigné toutes les cinq minutes pendant la première heure, puis toutes les heures. Par exemple, si le journal des erreurs contient le message d'erreur suivant, l'erreur suivante se produit :

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.

Ce message signifie que des transactions de capture instantanée présentant un timestamp antérieur à 109 (dernière transaction effectuée par l'opération de réduction) bloquent l'opération de réduction. Il indique également que la colonne transaction_sequence_num ou la colonne first_snapshot_sequence_num de la vue de gestion dynamique sys.dm_tran_active_snapshot_database_transactions contient la valeur 15. Si la colonne transaction_sequence_num ou la colonne first_snapshot_sequence_num contient 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 attend la fin de ces transactions.

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

• Mettez fin à la transaction qui bloque l'opération de réduction.
• Mettez fin à l'opération de réduction. Le travail accompli sera conservé.
• Laissez simplement l'opération de réduction attendre que la transaction bloquante s'achève.

Autorisations

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

Exemples

R. Réduire un fichier de données à une taille cible spécifiée

L’exemple suivant ramène la taille d’un 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éduire 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 AdventureWorks2022 à 1 Mo. Pour que la commande DBCC SHRINKFILE puisse réduire le fichier, le fichier est d’abord tronqué en définissant le mode de récupération de la base de données sur SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Tronquer les fichiers de données

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

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

D. Vider un fichier

L'exemple suivant montre comment vider un fichier de manière à ce qu'il puisse être supprimé de la base de données. Dans ce cadre, un fichier de données est d’abord créé ; il contient des données.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
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 AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Réduire un fichier de base de données avec WAIT_AT_LOW_PRIORITY

L’exemple suivant tente de réduire la taille d’un fichier de données dans la base de données utilisateur actuelle à 1 Mo. La vue catalogue sys.database_files est interrogée pour obtenir le file_id du fichier de données (dans cet exemple, file_id 5). Si un verrou ne peut pas être obtenu dans un délai d’une minute, l’opération de réduction est abandonnée.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Voir aussi

• 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.database_files (Transact-SQL)
• sys.databases (Transact-SQL)
• FILE_ID (Transact-SQL)

Étapes suivantes

• ALTER DATABASE (Transact-SQL)
• DBCC SHRINKDATABASE (Transact-SQL)
• Réduire une base de données
• Réduire un fichier