Éléments à prendre en compte pour les paramètres «croissance automatique» et «réduction automatique» dans SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 315512 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Résumé

Les paramètres de croissance automatique et réduction automatique par défaut fonctionnent pour vous avec aucun réglage sur SQL Server de nombreux systèmes. Toutefois, il existe des environnements où vous n'avez pas activer les paramètres ou dans lequel il se peut que vous deviez ajuster les paramètres d'étendue automatique et réduction automatique. Cet article vous donne des informations d'arrière-plan à vous guider lorsque vous sélectionnez les paramètres pour votre environnement.

Plus d'informations

Voici quelques éléments à prendre en compte si vous décidez de régler vos paramètres de croissance automatique et réduction automatique.

Comment configurer les paramètres ?

  1. Vous pouvez configurer les paramètres d'étendue automatique et réduction automatique à l'aide de l'une des opérations suivantes :
    • Une instruction ALTER DATABASE (non disponible dans SQL Server 7.0)
    • SQL Server Management Studio ou de SQL Enterprise Manager
    • Sp_dboption stockées procédure (déconseillée dans SQL Server 2005)
    Remarque Si vous utilisez SQL Server 2005, vous pouvez utiliser SQL Server Management Studio au lieu de SQL Enterprise Manager. Pour plus d'informations sur la façon de définir ces paramètres dans SQL Server 2005, reportez-vous aux sites Web MSDN (Microsoft Developer Network) aux adresses suivantes :
    Comment : ajouter des données ou des fichiers journaux à une base de données (SQL Server Management Studio)
    http://msdn2.microsoft.com/en-us/library/ms189253.aspx
    Propriétés de base de données (page de fichiers)
    http://msdn2.microsoft.com/en-us/library/ms180254.aspx
    Vous pouvez également configurer l'option étendue automatique lorsque vous créez une base de données.

    Vous pouvez afficher les paramètres en cours via les propriétés de base de données dans SQL Enterprise Manager (SEM). Vous pouvez également exécuter la commande Transact-SQL suivante :
    sp_helpdb [ [ @dbname= ] 'name' ]
  2. Gardez à l'esprit que les paramètres d'étendue automatique sont par fichier. Par conséquent, vous devez les définir au moins deux fois pour chaque base de données (une pour le fichier de données principal) et un pour le fichier journal principal. Si vous disposez de plusieurs fichiers de données et/ou le journal, vous devez définir les options de chaque fichier. En fonction de votre environnement, vous pourriez vous retrouver avec des paramètres différents pour chaque fichier de base de données.

Quelles sont les implications de performances ?

  • Si vous exécutez une transaction qui nécessite plus d'espace de journal qu'est disponible et vous avez activé l'option d'étendue automatique pour le journal des transactions de cette base de données, le temps que nécessaire à la transaction inclut le temps que nécessaire à la croissance du montant configuré du journal des transactions. Si l'incrément de croissance est volumineux ou s'il existe certains autre facteur qui le force à prendre un certain temps, la requête dans lequel vous ouvrez la transaction peut échouer en raison d'une erreur d'expiration du délai d'attente. Le même type de problème peut être dû à une croissance automatique de la partie données de votre base de données. Pour modifier votre configuration d'étendue automatique, consultez la rubrique «ALTER DATABASE» dans la documentation de SQL Server en ligne.
  • Si vous exécutez une transaction de grande exige que le journal agrandissement, autres transactions nécessitant une écriture sur le journal de transactions devra également attendre jusqu'à la fin de l'opération d'agrandissement.
  • Si vous combinez les options d'étendue automatique et réduction automatique, vous pouvez créer inutilement une surcharge. Assurez-vous que les seuils qui déclenchent l'agrandissement et réduction des opérations n'entraîne pas fréquents changements de taille vers le haut et vers le bas. Par exemple, vous pouvez exécuter une transaction qui provoque le journal des transactions augmentent par 100 Mo au moment où qu'il valide. Certains de temps après que réduction automatique démarre et réduit le journal des transactions par 100 Mo. Ensuite, vous exécutez la même transaction et entraîne le journal des transactions augmenter de 100 Mo à nouveau. Dans cet exemple, vous êtes en train de créer inutile une surcharge et potentiellement création fragmentation du fichier journal, une des qui peut nuire aux performances.
  • La fragmentation physique de modifier la taille des fichiers de données ou journaux peut avoir une incidence grave sur vos performances. C'est le cas si vous utilisez les paramètres automatiques ou si vous croître manuellement et réduire la taille des fichiers fréquemment.
  • Si la croissance, votre base de données en petits incréments, ou si vous croître il et puis de le réduire, vous pouvez vous retrouver avec la fragmentation du disque. Disque la fragmentation peut entraîner des problèmes de performances dans certaines circonstances. Un scénario d'incréments de croissance petit peut également réduire les performances sur votre système.
  • Dans SQL Server 2005 ou dans les versions ultérieures, vous pouvez activer l'initialisation instantanée des fichiers. L'initialisation instantanée des fichiers accélère les allocations de fichier uniquement pour les fichiers de données. L'initialisation instantanée des fichiers ne s'applique pas aux fichiers journaux.
  • Si vous avez de nombreux Croissances de fichier dans vos fichiers journaux, vous devez peut-être un trop grand nombre de fichiers journaux virtuels (fichier journal virtuel). Cela peut entraîner des problèmes de performances avec les opérations de démarrage/en ligne de base de données, réplication, la mise en miroir et modifiez capture des données (CDC). En outre, cela peut parfois causer des problèmes de performances avec les modifications de données.

Méthodes conseillées

  • Pour un système de production géré, vous devez prendre en compte croissance automatique pour être simplement un plan d'urgence pour la croissance inattendue. Ne gèrent pas la croissance de vos données et de journal au quotidien avec la croissance automatique.
  • Vous pouvez utiliser des alertes ou les programmes de surveillance pour surveiller la taille des fichiers et autorise une croissance des fichiers proactive. Cela vous permet d'éviter la fragmentation et vous permet de déplacer ces activités de maintenance en dehors des heures de pointe.
  • AutoShrink et la croissance automatique doivent être évaluées avec soin par un administrateur de base de données formés (DBA); ils ne doivent pas être laissés non managé.
  • Votre incrément de croissance automatique doit être assez grand pour éviter toute altération des performances répertoriées dans la section précédente. La valeur exacte à utiliser dans votre paramètre de configuration et le choix entre un pourcentage de croissance et une augmentation de la taille Mo spécifique dépend de nombreux facteurs dans votre environnement. Une règle générale vous pouvez utiliser pour test est pour définir votre paramètre d'étendue automatique à environ un huit la taille du fichier.
  • Activer le paramètre <maxsize>pour chaque fichier pour empêcher tout un seul fichier de grossir inutilement à un point où il consomme tout l'espace disque disponible.
  • Pour que conserve une taille de vos transactions plus petit possible éviter la croissance de fichier non planifié.

Pourquoi dois-je me soucier d'espace disque si les paramètres de taille sont contrôlés automatiquement ?

  • Le paramètre d'étendue automatique ne peut pas augmenter la taille de la base de données au-delà des limites de l'espace disque disponible sur les lecteurs pour lesquels les fichiers sont définis. Par conséquent, si vous vous appuyez sur la fonctionnalité d'étendue automatique pour dimensionner vos bases de données, vous devez vérifier encore indépendamment de votre espace disque disponible. Le paramètre d'étendue automatique est également limité par le paramètre MAXSIZE que vous avez sélectionné pour chaque fichier. Afin de réduire le risque de manquer d'espace, vous pouvez surveiller le compteur SQLServer : bases de données objet : taille de fichier (s) de données (Ko) et configurer une alerte lorsque la base de données atteint une certaine taille.
  • Croissance non planifié de fichiers de données ou journaux peut prendre l'espace que les autres applications pensez être disponible et peuvent ceux provoquer des autres applications de rencontrer des problèmes.
  • L'incrément de croissance de votre journal des transactions doit être suffisamment grand pour maîtriser des besoins de vos unités de transaction. Même avec l' étendue automatique activée, vous pouvez recevoir un message indiquant que le journal des transactions est plein, si elle ne s'agrandit pas assez rapidement pour répondre aux besoins de votre requête.
  • SQL Server ne teste pas en permanence pour les bases de données qui ont atteint le seuil configuré pour réduction automatique. Au lieu de cela, il examine les bases de données disponibles et trouve la première qui est configuré pour la réduction automatique. Il vérifie cette base de données et réduit cette base de données si nécessaire. Ensuite, il attend quelques minutes avant la vérification de la base de données suivant est configuré pour la réduction automatique. En d'autres termes, SQL Server ne pas vérifier toutes les bases de données en une seule fois et les réduire en une seule fois. Il fonctionnera dans les bases de données dans un tourniquet échelonner la charge hors sur une période donnée. Par conséquent, en fonction de nombre de bases de données sur une instance de SQL Server particulier, vous avez configuré pour la réduction automatique, il peut prendre plusieurs heures à partir du moment où que la base de données atteint le seuil jusqu'à ce qu'il est réduit en fait.

Références

Pour plus d'informations sur la façon de développer et réduire vos fichiers journaux et de base de données, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la base de connaissances Microsoft :
256650Comment faire pour réduire la taille du journal des transactions SQL Server 7.0
272318Réduction du journal de transactions dans SQL Server 2000 avec DBCC SHRINKFILE
317375Un journal de transactions augmente de manière inattendue ou est saturé sur un ordinateur qui exécute SQL Server
247751BOGUE : Plan de maintenance de base de données ne réduit pas la base de données
305635Un délai d'expiration se produit lorsqu'une base de données est automatiquement en cours d'extension
949523La latence d'une réplication transactionnelle est élevée dans SQL Server 2005 lorsque la valeur de la propriété "Taille initiale" et la valeur de la propriété de croissance automatique sont petites
Pour plus d'informations sur l'initialisation des fichiers de base de données, reportez-vous au site Web de MSDN (Microsoft Developer Network) à l'adresse suivante :
Database file initialization
Pour plus d'informations sur l'activation de l'initialisation instantanée des fichiers, reportez-vous au site Web de Microsoft à l'adresse suivante :
How and why to enable instant file initialization
Pour plus d'informations sur l'architecture physique du journal des transactions, visitez ce site MSDN Web :
Transaction log physical architecture

Documentation en ligne ; rubriques: «Architecture physique du journal des transactions»; «Réduction du journal des transactions»

Propriétés

Numéro d'article: 315512 - Dernière mise à jour: vendredi 7 mars 2008 - Version: 7.4
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Mots-clés : 
kbmt kbinfo KB315512 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 315512
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com