DBCC SHRINKDATABASE (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

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

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 ]

Arguments

database_name | database_id | 0

Nom ou ID de la base de données à réduire. 0 indique que la base de données active est utilisée.

target_percent

Pourcentage d'espace que vous voulez laisser disponible dans le fichier de base de données après la réduction de la base de données.

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 n'est applicable qu'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. Ignore target_percent s’il est spécifié avec TRUNCATEONLY. Azure Synapse Analytics ne prend pas en charge cette option.

DBCC SHRINKDATABASE avec l’option TRUNCATEONLY affecte uniquement le fichier journal des transactions de base de données. Pour tronquer le fichier de données, utilisez plutôt DBCC SHRINKFILE. Pour plus d’informations, consultez DBCC SHRINKFILE.

WITH 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

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

Quand 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 sans erreur.

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 sans erreur. 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 ]

• SELF

  SELF est l’option par défaut. Quittez l’opération de réduction de la base de données actuellement exécutée, sans effectuer aucune action.

• BLOCKERS

  Tuez toutes les transactions utilisateur qui bloquent l'opération de réduction de la base de données afin que l'opération puisse continuer. L'option BLOCKERS requiert que la connexion ait une autorisation ALTER ANY CONNECTION.

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

Spécifiez l’option NOTRUNCATE ou TRUNCATEONLY quand vous exécutez DBCC SHRINKDATABASE. Sans cette option, le résultat est le même si vous exécutez une opération DBCC SHRINKDATABASE avec NOTRUNCATE, puis une opération DBCC SHRINKDATABASE 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.

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.

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 disposez de deux fichiers journaux, d’un fichier de données et d’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, qui est la taille à laquelle le fichier doit être réduit. Quand DBCC SHRINKDATABASE est spécifié avec target_percent, le Moteur de base de données calcule la taille cible pour qu'elle corresponde à la quantité target_percent d'espace disponible dans le fichier après la réduction.

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. Supposons qu’aucune partie du journal logique ne reste dans les journaux virtuels au-delà de la taille cible du fichier journal. Le fichier est alors tronqué avec succès et DBCC SHRINKDATABASE s’exécute sans envoyer de messages. 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 à effectuer pour déplacer le journal logique à partir des journaux virtuels à la fin du fichier. Une fois les actions exécutées, DBCC SHRINKDATABASE peut être utilisé pour libérer l’espace restant.

Un fichier journal ne peut être réduit que jusqu’à une limite virtuelle. C’est pourquoi 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.

Comprendre les problèmes de concurrence avec DBCC SHRINKDATABASE

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é (WLP) 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.

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.
• 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 base de données entravent les performances.
• 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. Ce résultat représente une raison supplémentaire de ne pas réduire la taille de la base de données de manière répétitive.
• Sauf en cas de besoin particulier, ne définissez pas l’option de base de données AUTO_SHRINK sur ON (activé).

Dépanner

Les opérations de réduction peuvent être bloquées par une transaction en cours d’exécution sous un niveau d’isolation basé sur le contrôle de version de ligne. 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 réduire la taille des 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’affiche dans le journal des erreurs 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 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 l’opération de réduction est bloquée par des transactions d’instantanés ayant des valeurs d’horodatage plus anciennes que 109. Cette transaction est la dernière transaction que l’opération de réduction a effectuée. Cela indique également que la colonne transaction_sequence_num ou first_snapshot_sequence_num dans la vue de gestion dynamique sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contient 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 soient terminées.

Pour résoudre ce problème, vous pouvez effectuer l'une des tâches 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

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 AdventureWorks2022 jusqu’à la dernière extension affectée.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

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 AdventureWorks2022 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 est abandonnée.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) 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.databases (Transact-SQL)
• sys.database_files (Transact-SQL)

Étapes suivantes

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