DBCC SHRINKDATABASE (Transact-…SQL)

• 07/17/2017
• 8 minutes de lecture
• • p
  • M

  .

  • j
  • i
  • K
  • +12

S’applique à : SQL Server (toutes les versions prises en charge) Azure SQL Database Azure Synapse Analytics

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

Conventions de la syntaxe Transact-SQL

Syntaxe

DBCC SHRINKDATABASE ( database_name | database_id | 0 ) 

-- Azure Synapse AnalyticsDBCC SHRINKDATABASE ( database_name ) 

Arguments

nom_de_la_base |id_de_la_base | 0
Est le nom ou l’ID de la base de données à réduire. 0 spécifie que la base de données actuelle est utilisée.

target_percent
Est le pourcentage d’espace libre que vous voulez laisser dans le fichier de base de données après que la base de données ait été réduite.

NOTRUNCATE
Déplace les pages assignées de la fin du fichier vers les pages non assignées à l’avant du fichier. Cette action compacte les données à l’intérieur du 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 renvoyé au système d’exploitation, et la taille physique du fichier ne change pas. Ainsi, la base de données semble ne pas rétrécir lorsque vous spécifiez NOTRUNCATE.

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

TRUNCATEONLY
Laisse tout l’espace libre à la fin du fichier au système d’exploitation. Ne déplace aucune page à l’intérieur du fichier. Le fichier de données est réduit uniquement à la dernière extension assignée. Ignore target_percent s’il est spécifié avec TRUNCATEONLY. Azure Synapse Analytics ne prend pas en charge cette option.

TRUNCATEONLY affecte le fichier journal. Pour tronquer uniquement le fichier de données, utilisez DBCC SHRINKFILE.

WITH NO_INFOMSGS
Supprime tous les messages d’information qui ont des niveaux de gravité de 0 à 10.

Result Sets

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

Nom de la colonne	Description
DbId	Numéro d’identification de la base de données du fichier que le moteur de base de données a essayé de réduire.
FileId	Numéro d’identification du fichier que le moteur de base de données a essayé 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. Cette valeur correspond à la taille minimale ou à la taille créée à l’origine d’un fichier.
UsedPages	Nombre de pages de 8 Ko actuellement utilisées par le fichier.
EstimatedPages	Nombre de pages de 8 Ko auxquelles le Database Engine estime que le fichier pourrait être réduit.

Remarques

Pour réduire tous les fichiers de données et de journaux pour une base de données spécifique, exécutez la commande DBCC SHRINKDATABASE. Pour réduire un fichier de données ou un fichier journal à la fois pour une base de données spécifique, exécutez la commande DBCC SHRINKFILE.

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

Les opérations de DBCC SHRINKDATABASE peuvent être arrêtées à tout moment du processus, et tout travail terminé est conservé.

La base de données ne peut pas être plus petite que la taille minimale configurée de la base de données. Vous spécifiez la taille minimale lors de la création initiale de la base de données. Ou, la taille minimale peut être la dernière taille explicitement définie en utilisant 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.

Disons qu’une base de données est créée à l’origine avec une taille de 10 Mo. Puis, elle passe à 100 Mo. La plus petite taille à laquelle la base de données peut être réduite est de 10 Mo, même si toutes les données de la base de données ont été supprimées.

Spécifiez l’option NOTRUNCATE ou l’option TRUNCATEONLY lorsque vous exécutez DBCC SHRINKDATABASE. Si vous ne le faites pas, le résultat est le même que si vous exécutez une opération DBCC SHRINKDATABASE avec NOTRUNCATE suivie de l’exécution d’une opération DBCC SHRINKDATABASE avec TRUNCATEONLY.

La base de données réduite n’a pas besoin d’être en mode utilisateur unique. D’autres utilisateurs peuvent travailler dans la base de données lorsqu’elle est réduite, y compris les bases de données système.

Vous ne pouvez pas réduire une base de données pendant que la base de données est sauvegardée. Inversement, vous ne pouvez pas sauvegarder une base de données pendant qu’une opération de réduction de la base de données est en cours.

Comment fonctionne DBCC SHRINKDATABASE

DBCC SHRINKDATABASE réduit les fichiers de données sur une base par fichier, mais réduit les fichiers journaux comme si tous les fichiers journaux existaient dans un pool de journaux contigu. Les fichiers sont toujours rétrécis à partir de la fin.

Supposons que vous avez quelques fichiers journaux, un fichier de données et une base de données nommée mydb. Les fichiers de données et de journal font 10 Mo chacun et le fichier de données contient 6 Mo de données. Le moteur de base de données calcule une taille cible pour chaque fichier. Cette valeur est la taille à laquelle le fichier doit être réduit. Lorsque DBCC SHRINKDATABASE est spécifié avec target_percent, le moteur de base de données calcule la taille cible comme étant la quantité target_percent d’espace libre dans le fichier après le rétrécissement.

Par exemple, si vous spécifiez un target_percent de 25 pour le rétrécissement de mydb, le moteur de base de données calcule que la taille cible du fichier de données est de 8 Mo (6 Mo de données plus 2 Mo d’espace libre). Ainsi, 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étrécit le fichier.

Supposons que le fichier de données de mydb contient 7 Mo de données. La spécification d’un pourcentage_cible de 30 permet de réduire ce fichier de données au pourcentage libre de 30. Cependant, la spécification d’un pourcentage_cible de 40 ne réduit pas le fichier de données car le moteur de base de données ne réduira pas un fichier à une taille inférieure à celle que les données occupent actuellement.

Vous pouvez également envisager cette question d’une autre manière : 40 pour cent d’espace libre voulu + 70 pour cent de fichier de données plein (7 Mo sur 10 Mo), cela fait plus de 100 pour cent. Toute taille cible supérieure à 30 ne réduira pas le fichier de données. Il ne rétrécira pas parce que le pourcentage libre que vous voulez plus le pourcentage actuel que le fichier de données occupe est supérieur à 100 pour cent.

Pour les fichiers journaux, le moteur de base de données utilise target_percent pour calculer la taille cible pour l’ensemble du journal. C’est pourquoi target_percent est la quantité d’espace libre dans le journal après l’opération de rétrécissement. La taille cible pour le journal entier est ensuite traduite en une taille cible pour chaque fichier de journal.

DBCC SHRINKDATABASE tente de rétrécir chaque fichier de journal physique à sa taille cible immédiatement. Disons qu’aucune partie du journal logique ne reste dans les journaux virtuels au-delà de la taille cible du fichier journal. Alors le fichier est tronqué avec succès et DBCC SHRINKDATABASE se termine sans aucun message. Cependant, 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 émet un message d’information. Ce message décrit les actions nécessaires pour déplacer le journal logique hors des journaux virtuels à la fin du fichier. Après l’exécution des actions, DBCC SHRINKDATABASE peut être utilisé pour libérer l’espace restant.

Un fichier journal ne peut être rétréci qu’à une limite de fichier journal virtuel. C’est pourquoi le rétrécissement d’un fichier journal à une taille inférieure à la taille d’un fichier journal virtuel pourrait ne pas être possible. Cela peut être impossible 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 lorsque les fichiers journaux sont créés ou étendus.

Bonnes pratiques

Considérez les informations suivantes lorsque vous prévoyez de réduire une base de données :

• Une opération de réduction est plus efficace après une opération qui crée de l’espace inutilisé, telle qu’une opération de troncature de la table ou de suppression de la table.
• La plupart des bases de données nécessitent un certain espace libre pour être disponibles pour les opérations quotidiennes régulières. Vous pouvez réduire une base de données à plusieurs reprises et remarquer que la taille de la base de données augmente à nouveau. Cette croissance indique que l’espace rétréci est nécessaire pour les opérations régulières. Dans ces cas, le rétrécissement répété de la base de données est une opération gaspillée.
• Une opération de rétrécissement ne préserve pas l’état de fragmentation des index de la base de données et augmente généralement la fragmentation dans une certaine mesure. Ce résultat est une autre raison de ne pas rétrécir la base de données de façon répétée.
• Sauf si vous avez une exigence spécifique, ne définissez pas l’option de base de données AUTO_SHRINK sur ON.

Dépannage

Il est possible de bloquer les opérations de rétrécissement par une transaction qui s’exécute sous un niveau d’isolation basé sur le versioning des rangées. Par exemple, une opération de suppression importante s’exécutant sous un niveau d’isolation basé sur le versionnement des lignes est en cours d’exécution lorsqu’une opération DBCC SHRINK DATABASE est exécutée. Dans ce cas, l’opération de réduction attendra que l’opération de suppression soit terminée avant de réduire les fichiers. Lorsque l’opération de réduction attend, les opérations DBCC SHRINKFILE et DBCC SHRINKDATABASE affichent un message d’information (5202 pour SHRINKDATABASE et 5203 pour SHRINKFILE). Ce message s’imprime dans le journal des erreurs du serveur SQL 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 suivant :

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 d’instantanés dont l’horodatage est antérieur à 109 bloquent l’opération de réduction. Cette transaction est la dernière transaction que l’opération de rétraction a achevée. Elle indique également que les colonnes transaction_sequence_num ou first_snapshot_sequence_num de la vue de gestion dynamique sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contiennent une valeur de 15. La colonne transaction_sequence_num ou first_snapshot_sequence_num de la vue peut contenir un nombre inférieur à la dernière transaction terminée par une opération de réduction (109). Si tel est le cas, l’opération de rétraction attendra que ces transactions se terminent.

Pour résoudre le problème, vous pouvez effectuer l’une des tâches suivantes :

• Finir la transaction qui bloque l’opération de rétraction.
• Finir l’opération de rétraction. Tout travail terminé est conservé.
• Ne rien faire et permettre à l’opération de réduction de taille d’attendre que la transaction bloquante se termine.

Permissions

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

Exemples

A. Réduire une base de données et spécifier un pourcentage d’espace libre

L’exemple suivant réduit la taille des fichiers de données et de journal dans la base de données utilisateur UserDB pour permettre un espace libre de 10 % dans la base de données.

DBCC SHRINKDATABASE (UserDB, 10); GO 

B. Tronquer une base de données

L’exemple suivant réduit les fichiers de données et de journal de la base de données d’échantillon AdventureWorks à la dernière étendue attribuée.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY); 

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

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

Voir aussi

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

.