Dans cet article, nous allons aborder la propriété de base de données SQL Server Auto Shrink, qui permet à SQL Server de réduire automatiquement les fichiers de la base de données lorsque la valeur est configurée comme True dans l’option de la base de données. L’opération de rétrécissement automatique est effectuée par la fonctionnalité de rétrécissement automatique de la base de données du serveur, qui est le cours principal de cet article.
Pour éviter l’activité de rétrécissement des fichiers de données/log chaque fois que la taille du fichier dépasse l’espace libre dans le fichier de données, le DBA (administrateur de base de données) doit sauvegarder les fichiers log à un intervalle régulier. Sauvegarder l’ensemble de la base de données n’est pas une bonne idée ; il faut également construire ou configurer le journal des transactions. Les journaux des transactions vont augmenter en nombre jusqu’à occuper tout l’espace disque disponible si vous ne les sauvegardez pas. Si vous sauvegardez la base de données, cela libère l’espace libre pour être réutilisé à nouveau. L’administrateur de la base de données doit planifier une sauvegarde du journal des transactions pour réduire la taille des fichiers journaux à une taille équitable.
La fonction de rétrécissement automatique du serveur SQL est désactivée par défaut sur les bases de données des instances du serveur SQL. Dans le scénario où vous avez un certain nombre de bases de données relativement petites qui augmentent à une taille plus grande soit par l’insertion de nouveaux tuples ou par un grand nombre d’espaces vides créés par la suppression de plusieurs tuples, SQL Server Auto Shrink devient assez pratique dans cette situation. De plus, vous ne devez pas vous inquiéter de la fragmentation de la taille des fichiers de la base de données.
Lors du rétrécissement de la base de données, vous devez avoir à considérer les opérations de rétrécissement sur les bases de données critiques ainsi que sur les bases de données relativement plus grandes dans le cadre de la maintenance. En outre, il faut éviter d’exécuter la fonction de rétrécissement manuel ; les conséquences de cette opération sont que l’on ne sera jamais au courant des problèmes concernant la requête nouvelle ou existante. Cependant, le rétrécissement des fichiers de transaction est meilleur que le rétrécissement des fichiers de données.
Moyens d’activer et de désactiver le rétrécissement automatique de SQL Server pour la base de données ?
Les utilisateurs peuvent activer et désactiver l’option de rétrécissement automatique de la base de données en utilisant SSMS et T-SQL des deux manières.
Activer le rétrécissement automatique de la base de données en utilisant SSMS :
Les utilisateurs peuvent activer ou désactiver cette option à partir des propriétés de la base de données, avec une balise Auto Shrink. Ici, True dans la liste déroulante activera cette option pour votre base de données.
Activer la réduction automatique de la base de données en utilisant T-SQL:
Les utilisateurs peuvent exécuter les instructions T-SQL ci-dessous pour activer ou désactiver la base de données de réduction. Nous avons utilisé la base de données AdventureWorks pour l’instruction T-SQL ci-dessus.
|
1
2
3
4
5
6
|
–Activer le rétrécissement automatique pour la base de données AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
—Désactiver le rétrécissement automatique pour la base de données AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Effets du rétrécissement de la base de données sur les performances des requêtes
De mauvais effets peuvent être soulevés du côté des performances des requêtes si vous activez l’option de rétrécissement automatique de la base de données et les paramètres de croissance automatique ensemble pour la base de données. Nous pouvons garder un peu de place pour que les fichiers de données se développent et aussi éviter certains événements fréquents de croissance automatique en configurant la taille de la base de données à une valeur optimale ou la plupart du temps, chaque base de données a certains paramètres avec des fonctionnalités de croissance automatique activées.
Pour ces bases de données, les fonctionnalités de rétrécissement automatique doivent être activées par nous lorsque la base de données est plus petite, une et plus aucune opération CRUD, de sorte qu’il peut rendre le rétrécissement des fichiers de données possible et récupérer l’espace libre que nous avons provisionné à dessein pour garder nos bases de données loin des événements de croissance automatique. L’espace libre sera libéré automatiquement dans les fichiers de données et les fichiers journaux périodiquement en priorité séquentielle round-robin par la fonction Auto shrink si plusieurs bases de données ont activé cette fonction.
Pour les bases de données de grande taille, l’auto-growth, et par la suite, l’auto shrink sera exécuté qui entraînera des problèmes de performance conduits par la fragmentation au niveau du système. Pour résumer tout cela, pour toute base de données, l’auto shrink ne devrait pas être activé en fonction des raisons suivantes :
- L’exécution des algorithmes de rétraction automatique du serveur SQL sans aucun but va certainement gaspiller les ressources en quantité massive
- Que vous exécutiez la rétraction automatique du serveur SQL ou la rétraction manuelle, cela va évidemment provoquer la fragmentation de l’index et cela exécute finalement les fichiers de données se rétrécissant également
- Si le serveur est également en train de forcer les limites du sous-système IO, l’exécution de la rétraction peut le pousser au-delà, ce qui entraîne de longues files d’attente sur le disque et éventuellement des timeouts d’E/S, cela consommera massivement les ressources d’E/S et de CPU du serveur
- Les performances du système seront retardées par la fragmentation au niveau du disque du système de fichiers, ce qui est à nouveau l’effet direct de l’exécution fréquente de fichiers de données en contraction et en croissance
Pour en savoir plus sur les événements de croissance et de contraction dans SQL Server, lisez cet article, Obtenir des détails sur les événements de croissance et de contraction des bases de données de SQL Server.
Pour la base de données critique, l’opération de rétraction manuelle pourrait être exécutée au niveau du fichier de la base de données par l’expert. L’activité de rétraction manuelle du fichier peut être effectuée lorsque l’opération de suppression est exécutée, et par la suite, l’espace a été récupéré. Nous devons reconstruire les index fragmentés lors de l’exécution de l’opération de réduction car celle-ci peut entraîner une fragmentation des index. Le pourcentage de fragmentation des index peut être vérifié par l’utilisateur à l’aide d’instructions T-SQL ou de DMV. Cependant, le rétrécissement du fichier journal devrait être fait manuellement lorsque cela est nécessaire et ne devrait pas faire partie de l’activité de maintenance régulière.
Pour surveiller l’utilisation du disque régulièrement, le rapport d’utilisation du disque pourrait être analysé par l’utilisateur avant d’effectuer l’opération de rétrécissement manuel en utilisant SSMS, qui vous donne un aperçu des informations sur les données et l’espace du journal pour une base de données dans l’affichage. Si l’utilisateur souhaite obtenir un tableau de bord pour le calcul de l’espace réservé et de l’espace libre pour les fichiers de la base de données, le rapport sur les disques devient très utile. Cependant, le rapport de disque habite l’information en utilisant les DMV de SQL Server. Le rapport de disque est disponible sur le répertoire ci-dessous en utilisant SSMS.
Database >> Rapports >> Rapports standard >> Utilisation du disque
Ici, nous avons un rapport de disque pour la base de données. Les statistiques en temps réel pour les fichiers de données et les fichiers journaux pourraient être vues par l’utilisateur. Ce rapport intègre les informations primaires de l’espace total réservé, de l’espace réservé aux fichiers de données, de l’espace réservé au journal des transactions et de l’espace réservé à l’OLTP en mémoire.
Réduire automatiquement la base de données à l’aide de Jobs SQL Server
De tels scripts pourraient être réalisés en planifiant un job par l’utilisateur pour exécuter l’opération de réduction de la base de données avec l’activité du planificateur. La première étape du script consiste à trouver l’espace libre dans le fichier de la base de données, puis à réduire ce fichier si les critères définis pour l’espace libre correspondent. L’espace libre doit être calculé à l’aide des DMV du serveur SQL.
Fondamentalement, la surveillance du fichier de base de données est effectuée par l’administrateur de la base de données, qui à son tour intervient sur la base de données pour surveiller la taille du fichier de base de données. Si la limite prédéfinie est franchie par un fichier quelconque, alors ils doivent effectuer de telles étapes. Ainsi, cette activité peut être automatisée à l’aide d’un travail SQL Server à exécuter sur une base quotidienne, hebdomadaire ou mensuelle à un moment prédéfini.
La meilleure pratique est de planifier ce travail sur le fichier journal de la base de données uniquement et de surveiller l’espace libre du fichier de données manuellement. Car cela peut également affecter les performances des requêtes. SQL Server DMV retournera le fichier de données et le fichier journal dans le même ensemble de résultats, donc le type de fichier doit être séparé dans l’instruction T-SQL de SQL Server DMV. Avant de réduire le fichier, l’utilisateur peut appliquer une logique de requête sur l’espace total et l’espace libre du fichier journal. Si les critères de calcul correspondent à la propriété du fichier, alors le fichier sera rétréci avec la taille de fichier cible.
Vérifier l’espace libre pour les fichiers de base de données:
Si de tels critères correspondent aux fichiers de base de données, par exemple, si l’espace libre est supérieur à (n) MB/GB, si n(%) d’espace libre compare à l’espace total, et bien d’autres. Lorsque l’espace libre n’est pas disponible pour le fichier journal et que la taille du fichier est proche de la valeur du paramètre de taille maximale du fichier, alors l’administrateur de la base de données doit faire une recherche dans le journal des transactions.
Réduire le fichier de base de données :
|
1
|
DBCC SHRINKFILE(nom_fichier, 5120) ;
|
Ici, 5120 est une taille de fichier cible en Mo. Ainsi, le fichier aura une taille de 5120 Mo. Configurez la requête T-SQL avec les critères requis dans l’étape de travail du serveur SQL et programmez-la sur les heures creuses.
L’option de réduction automatique du serveur SQL ne peut pas être activée pour toutes les bases de données. Plus précisément, elle aide pour la base de données plus petite, qui effectue moins d’opérations CRUD comparativement.
Conclusion
Dans cet article, nous avons discuté de la propriété de base de données Auto Shrink dans SQL Server pour réduire les fichiers de données et de journaux et supprimer l’espace inutilisé. Le rétrécissement d’une base de données est une opération coûteuse et doit être utilisé avec précaution.
- Auteur
- Messages récents
Voir tous les messages de Jignesh Raiyani
- Page Life Expectancy (PLE) dans SQL Server – 17 juillet, 2020
- Comment automatiser le partitionnement des tables dans SQL Server – 7 juillet 2020
- Configuration des groupes de disponibilité Always On de SQL Server sur AWS EC2 – 6 juillet 2020
.