DBCC SHRINKDATABASE (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBase de données SQL dans Microsoft Fabric

Réduit la taille des fichiers de données et journaux dans la base de données spécifiée.

Ne considérez pas les opérations de réduction comme une maintenance classique. Les fichiers de données et de journaux qui augmentent en raison d’opérations métier régulières et récurrentes ne nécessitent pas d’opérations de réduction.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server :

DBCC SHRINKDATABASE
( database_name | database_id | 0
     [ , target_percent ]
     [ , { 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 }

Syntaxe pour Azure Synapse Analytics :

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Les arguments

{ database_name | database_id | 0 }

Le nom ou l’identifiant de la base de données à réduire. Une valeur de 0 spécifie la base de données actuelle.

target_percent

Le pourcentage d’espace libre à laisser dans le fichier de la base de données après la fin de l’opération de réduction.

Si vous spécifiez target_percent avec TRUNCATEONLY, l’opération de réduction peut ne pas libérer d’espace libre à la fin du fichier.

NOTRUNCATE

Déplace les pages affectées de la fin du fichier vers les pages non affectées du début du fichier. Cette action compacte les données dans le fichier. target_percent est facultatif. Azure Synapse Analytics ne prend pas en charge cette option.

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, la base de données ne paraît pas être réduite quand vous spécifiez l’option NOTRUNCATE.

NOTRUNCATE S’applique uniquement aux fichiers de données. NOTRUNCATE n’affecte pas le fichier journal.

TRUNCATEONLY

Libère tout l’espace libre à la fin du fichier pour le système d’exploitation. 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 affectée. Azure Synapse Analytics ne prend pas en charge cette option.

Si vous spécifiez target_percent avec TRUNCATEONLY, l’opération de réduction peut ne pas libérer d’espace libre à la fin du fichier.

AVEC NO_INFOMSGS

Supprime tous les messages d'information dont les niveaux de gravité sont compris entre 0 et 10.

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, base de données SQL dans Microsoft Fabric

La fonction d’attente à faible priorité réduit la contention de verrouillage lors de l’opération de rétrécition. 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 ABORT_AFTER_WAIT l’option NONE.
• Tu ne peux pas définir cette MAX_DURATION option. Le délai de verrouillage à faible priorité pour une opération de réduction est toujours d’une minute.

WAIT_AT_LOW_PRIORITY

Lorsqu’une commande de réduction est exécutée en WAIT_AT_LOW_PRIORITY mode, les requêtes nécessitant des verrous de stabilité de schéma (Sch-S) sur les pages de la carte d’allocation d’index (IAM) ne sont pas bloquées par l’opération de réduction. Cependant, l’opération de réduction peut être bloquée par un Sch-S verrou sur une page IAM. Le réduction continue de s’exécuter uniquement lorsqu’il est capable d’obtenir un verrou de modification de schéma (Sch-M) sur une page IAM requise.

Si une opération de réduction en WAIT_AT_LOW_PRIORITY mode ne peut pas obtenir ce verrou en raison d’une requête longue qui détient un Sch-S verrou, l’opération de réduction expire avec l’erreur 49516, 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.

{ ABORT_AFTER_WAIT = [ SELF | BLOQUEURS ] }

• SELF

  SELF est l’option par défaut. Quittez l’opération de réduction de la base de données actuellement en cours sans prendre d’autres mesures.

• BLOCKERS

  Tuez toutes les transactions utilisateur qui bloquent l'opération de réduction des fichiers afin que l'opération puisse continuer. L’option BLOCKERS exige que la connexion ait la ALTER ANY CONNECTION permission de Ou KILL DATABASE CONNECTION .

Jeu de résultats

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

Notes

Pour réduire tous les fichiers de données et fichiers journaux d'une base de données particulière, exécutez la commande DBCC SHRINKDATABASE. Pour réduire un fichier de données ou un fichier journal d'une base de données particulière, exécutez la commande DBCC SHRINKFILE.

Pour afficher la quantité d'espace actuellement libre (non allouée) dans la base de données, exécutez sp_spaceused.

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

La base de données ne peut pas être réduite à une taille inférieure à la taille minimale configurée de la base de données. Vous indiquez la taille minimale lors de la création de la base de données. Il peut s’agir aussi de la dernière taille explicitement définie à l’aide d’une opération de changement de taille de fichier. Des opérations comme DBCC SHRINKFILE ou ALTER DATABASE sont des exemples d’opérations de changement de taille de fichier.

Supposons qu’une base de données est créée avec une taille de 10 Mo. Elle atteint ensuite une taille de 100 Mo. La base de données ne peut pas être réduite à moins de 10 Mo, même si toutes les données de la base de données sont supprimées.

Vous pouvez spécifier l’option NOTRUNCATE ou l’option TRUNCATEONLY lorsque vous exécutez DBCC SHRINKDATABASE. Si vous ne spécifiez aucune des deux options, le résultat est le même que si vous exécutez une DBCC SHRINKDATABASE opération avec NOTRUNCATE suivie d’une DBCC SHRINKDATABASE opération avec TRUNCATEONLY.

Il n’est pas nécessaire que la base de données réduite soit en mode mono-utilisateur. D’autres utilisateurs peuvent travailler dans la base de données quand elle est réduite, notamment les bases de données système.

Vous ne pouvez pas réduire la taille d’une base de données en cours de sauvegarde. Inversement, vous ne pouvez pas sauvegarder une base de données alors qu’elle fait l’objet d’une opération de réduction.

Dans les pools SQL Azure Synapse, évitez d'exécuter une commande de réduction car c'est une opération intensive en E/S qui peut mettre hors ligne votre pool SQL dédié (anciennement SQL DW). Cette commande affecte également le coût de vos instantanés d’entrepôt de données.

Problèmes connus

S’applique à : SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics dédié SQL pool

• Dans SQL Server 2022 (16.x) et versions antérieures, les pages utilisées par les types de colonnes LOB (varbinary(max), varchar(max) et nvarchar(max)) dans les segments compressés de colonne store ne peuvent pas être déplacées par DBCC SHRINKDATABASE et DBCC SHRINKFILE. Pour plus d’informations, consultez les nouveautés concernant les index columnstore.

Fonctionnement de DBCC SHRINKDATABASE

DBCC SHRINKDATABASE réduit les fichiers de données, fichier par fichier, mais réduit les fichiers journaux comme si tous les fichiers journaux existaient dans un groupe de journaux contigus. Les fichiers sont toujours réduits à partir de la fin.

Supposons que vous ayez deux fichiers journaux et un fichier de données dans une base de données nommée mydb. La taille de chacun des fichiers est de 10 Mo et le fichier de données contient 6 Mo de données. Pour chaque fichier, le Moteur de base de données calcule une taille cible, Cette valeur correspond à la taille cible du fichier après rétrécissement. Lorsque vous spécifiez DBCC SHRINKDATABASE avec target_percent, le Moteur de base de données calcule la taille cible comme étant la target_percent quantité d’espace libre dans le fichier après rétrécissement.

Par exemple, si vous spécifiez un target_percent de 25 pour réduire mydb, le Moteur de base de données calcule une taille cible de 8 Mo pour le fichier de données (6 Mo de données plus 2 Mo d’espace libre). Par conséquent, le Moteur de base de données déplace toutes les données des 2 derniers Mo du fichier de données vers tout espace libre dans les 8 premiers Mo du fichier de données, puis réduit le fichier.

Supposons que le fichier de données de mydb contient 7 Mo de données. Si vous spécifiez un target_percent de 30, le fichier de données peut être réduit à 30 % d'espace libre. Toutefois, la spécification d’une valeur target_percent de 40 ne réduit pas le fichier de données, car il n'est pas possible de créer suffisamment d'espace libre dans la taille totale actuelle du fichier de données.

En d’autres termes : si vous ajoutez 40 % d’espace libre souhaité aux 70 % d’espace occupé dans le fichier de données (7 Mo sur un total de 10 Mo), vous obtenez plus de 100 %. Une valeur target_percent supérieure à 30 n’entraîne pas la réduction du fichier de données. En effet, la somme du pourcentage d’espace libre souhaité et du pourcentage actuel occupé par le fichier de données est supérieure à 100 %.

Dans le cas des fichiers journaux, le Moteur de base de données utilise target_percent pour calculer la taille cible de l’ensemble du journal. C’est pour cette raison que target_percent correspond à la quantité d’espace libre dans le journal après l’opération de réduction. La taille cible pour le journal complet est alors convertie en taille cible pour chaque fichier journal.

DBCC SHRINKDATABASE tente immédiatement de réduire la taille de chaque fichier journal physique à sa taille cible. Si aucune partie du journal logique ne reste dans les journaux virtuels au-delà de la taille cible du fichier journal, DBCC SHRINKDATABASE le fichier tronque avec succès et se termine sans aucun message. Toutefois, si une partie du journal logique reste 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 visant à déplacer le journal logique hors des journaux virtuels à la fin du fichier. Après avoir effectué les actions, utilisez DBCC SHRINKDATABASE pour libérer l’espace restant.

Vous ne pouvez réduire un fichier journal qu’à une limite virtuelle de fichier journal. C’est pourquoi il n’est pas possible de réduire un fichier journal à une taille inférieure à celle d’un fichier journal virtuel. Le Moteur de base de données choisit dynamiquement la taille du fichier journal virtuel lors de la création ou de l’extension des fichiers journals.

Comprendre les problèmes de concurrence avec DBCC SHRINKDATABASE

Les commandes de réduction de base de données et de fichiers de réduction peuvent entraîner des problèmes de concurrence, notamment lors de la maintenance active comme la reconstruction d’index, ou dans des environnements OLTP très fréquentés.

Par exemple, une requête utilisateur peut acquérir un verrou de stabilité de schéma (Sch-S) sur une page d’allocation d’index (IAM) et le maintenir jusqu’à sa fin. Lors de tentatives de récupération d’espace lors d’une utilisation régulière, les opérations de réduction de base de données et de réduction de fichiers nécessitent un verrou de modification de schéma (Sch-M) lors du déplacement ou de la suppression de pages IAM, bloquant ainsi les Sch-S verrous nécessaires aux requêtes utilisateur. En conséquence, les requêtes de longue durée peuvent bloquer une opération de réduction. Ce comportement signifie également que toute nouvelle requête nécessitant un Sch-S verrouillage sur une page IAM peut se placer derrière l’opération de réduction, aggravant ce problème de concurrence.

Introduite dans SQL Server 2022 (16.x), la fonctionnalité d’attente à faible priorité pour les opérations de réduction répond à ce problème en prenant le verrou de modification de schéma sur les pages IAM dans ce WAIT_AT_LOW_PRIORITY mode. Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY avec des opérations de réduction.

Pour plus d’informations sur Sch-S les verrous et Sch-M les serrures, consultez le guide de verrouillage des transactions et de versionnement des lignes.

Bonnes pratiques

Prenez en compte les informations suivantes lorsque vous envisagez de réduire une base de données :

• 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 de l'espace inutilisé, comme une troncature de table ou une suppression de table.

• La plupart des bases de données nécessitent un espace libre pour les opérations quotidiennes régulières. Si vous réduisez un fichier de base de données à plusieurs reprises et remarquez que la taille de la base de données augmente à nouveau, cette croissance indique que les opérations régulières nécessitent cet espace libre. Dans ces cas, réduire à plusieurs reprises le fichier de base de données est contre-productif. La croissance du fichier nécessaire pour allouer un nouvel espace après le retrait peut nuire aux performances.

• Une opération de réduction ne préserve pas l’état de fragmentation des index dans la base de données, et peut augmenter la fragmentation des indices, ce qui pourrait réduire le débit d’E/S de lecture pour les requêtes utilisant de grands scans.

• Sauf si vous avez une exigence spécifique, ne réglez pas l’option de base de données AUTO_SHRINK sur ON.

• Si vous devez réduire les fichiers de données d’une grande base de données, envisagez d’utiliser le script PowerShell ShrinkDriver . Le script automatise et simplifie le processus de réduction, le transformant en une opération unique, observable et reprenable. Le script rétrécit plusieurs fichiers en parallèle, réessaie lorsqu’il est interrompu, et génère des rapports d’état détaillés au fur et à mesure.

Dépanner

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, vous exécutez DBCC SHRINKDATABASE pendant qu’une grande opération de suppression fonctionnant sous un niveau d’isolation basé sur la gestion de versions de ligne est en cours. Dans ce cas, l’opération de réduction attend que l’opération de suppression soit terminée avant de réduire les fichiers. Quand l’opération de réduction est en attente, les opérations DBCC SHRINKFILE et DBCC SHRINKDATABASE envoient un message d’information (5202 pour SHRINKDATABASE et 5203 pour SHRINKFILE). Ce message s’imprime dans le journal d’erreurs de SQL Server toutes les cinq minutes pendant la première heure, puis toutes les heures suivantes. Par exemple, si le journal des erreurs contient le message d'erreur :

DBCC SHRINKDATABASE for database ID 9 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.

Cette erreur signifie que les transactions instantanées avec des horodatages antérieurs à 109 bloquent l’opération de rétrécissement. Cette transaction est la dernière transaction que l’opération de réduction a effectuée. Cela indique également que les transaction_sequence_num colonnes ou first_snapshot_sequence_num dans la vue sys.dm_tran_active_snapshot_database_transactions gestion dynamique contiennent une valeur de 15. La colonne transaction_sequence_num ou first_snapshot_sequence_num dans la vue peut contenir un numéro inférieur à la dernière transaction effectuée par une opération de réduction (109). Dans ce cas, l’opération de réduction attend que ces transactions se terminent.

Pour résoudre le problème, vous pouvez faire l’une des étapes suivantes :

• Mettez fin à la transaction qui bloque l'opération de réduction.
• Mettez fin à l'opération de réduction. Tout travail achevé 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

Les exemples de code de cet article utilisent les bases de données d'exemple AdventureWorks2025 ou AdventureWorksDW2025, que vous pouvez télécharger à partir de la page d'accueil Microsoft SQL Server Samples and Community Projects.

R. Réduire une base de données et spécification d'un pourcentage d'espace libre

L’exemple suivant diminue la taille des fichiers de données et journaux de la base de données utilisateur UserDB pour obtenir 10 % d’espace libre dans la base de données.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Tronquer une base de données

L’exemple suivant réduit la taille des fichiers de données et des fichiers journaux de l’exemple de base de données AdventureWorks2025 jusqu’à la dernière extension affectée.

DBCC SHRINKDATABASE (AdventureWorks2025, TRUNCATEONLY);

Chapitre C. Réduire une base de données Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. Réduire une base de données avec WAIT_AT_LOW_PRIORITY

L’exemple suivant tente de diminuer la taille des fichiers de données et journaux de la base de données AdventureWorks2025 pour obtenir 20 % d’espace libre dans la base de données. Si un verrou ne peut pas être obtenu dans un délai d’une minute, l’opération de réduction abandonne.

DBCC SHRINKDATABASE ([AdventureWorks2025], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Contenu connexe

• Réduire une base de données
• Réduire un fichier
• FICHIER DE RÉDUCTION DBCC (Transact-SQL)
• Considérations relatives aux paramètres de croissance automatique et de réduction automatique dans SQL Server
• Fichiers et groupes de fichiers de base de données
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• ALTER DATABASE (Transact-SQL)
• Gérer l’espace de fichier des bases de données dans Azure SQL Database