DetailPage-MSS-KB

Base de connaissances

Numéro d'article: 307487 - Dernière mise à jour: vendredi 12 juillet 2013 - Version: 7.1

Sommaire

Résumé

Cet article décrit trois méthodes que vous pouvez utiliser pour réduire la base de données tempdb à une taille plus petite que sa dernière taille configurée. La première méthode vous donne le contrôle complet de la taille des fichiers de tempdb mais elle implique que vous redémarriez SQL Server. La deuxième méthode réduit tempdb dans son ensemble, avec certaines limitations, comme le redémarrage de SQL Server. La troisième méthode vous permet de réduire des fichiers individuels dans tempdb. Les deux dernières méthodes impliquent qu'il n'y ait aucune activité dans la base de données tempdb pendant l'opération de réduction.

Remarque Si vous utilisez SQL Server 2005, vous pouvez appliquer également ces méthodes. Toutefois, vous devez utiliser SQL Server Management Studio au lieu de Enterprise Manager et Query Analyzer pour effectuer ces opérations.


Informations concernant Tempdb

La base de données tempdb est un espace de travail temporaire. SQL Server utilise tempdb entre autres choses pour :
  • le stockage de tables temporaires créées de manière explicite ;
  • les tables de travail qui comportent les résultats intermédiaires créés pendant le traitement et le tri des requêtes ;
  • les curseurs statiques matérialisés.
SQL Server enregistre seulement suffisamment d'informations dans le tempdb journal des transactions pour annuler une transaction mais ne répète pas les transactions pendant la récupération de base de données. Les performances des instructions INSERT sont ainsi optimisées dans tempdb. En outre, il n'est pas nécessaire d'enregistrer les informations pour répéter des transactions car tempdb est recréée à chaque redémarrage de SQL Server ; par conséquent, elle ne comporte pas de transactions à restaurer par progression ou à annuler. Lors du démarrage de SQL Server, la base de données tempdb est recréée à partir d'une copie de base de données modèle et elle reprend sa dernière taille configurée.

Par défaut, la base de données tempdb est configurée pour s'étendre automatiquement au besoin ; par conséquent, elle peut s'agrandir à temps jusqu'à atteindre une taille non souhaitée. Un simple redémarrage de SQL Server restaure la dernière taille configurée de tempdb. La taille configurée est la dernière taille explicite définie grâce à une opération de modification de taille de fichier telle que ALTER DATABASE avec l'option MODIFY FILE ou l'instruction DBCC SHRINKFILE. Cet article présente les trois méthodes que vous pouvez utiliser pour réduire la base de données tempdb à une taille plus petite que sa dernière taille configurée.


Méthode 1 pour réduire Tempdb

Cette méthode implique que vous redémarriez SQL Server.

  1. Arrêtez SQL Server. Ouvrez une invite de commandes, puis démarrez SQL Server en tapant la commande suivante :

    sqlservr -c -f

    Les paramètres -c et -f permettent à SQL Server de démarrer en mode de configuration minimum avec une taille tempdb de 1 Mo pour le fichier de données et de 0,5 Mo pour le fichier journal.

    REMARQUE : Si vous utilisez une instance nommée SQL Server, vous devez accéder au dossier approprié (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) et utiliser le commutateur -s (-s%nom_instance%).
  2. Connectez-vous à SQL Server avec Query Analyzer, puis exécutez les commandes Transact-SQL suivantes :
       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'tempdev', SIZE = target_size_in_MB) 
       --Desired target size for the data file
    
       ALTER DATABASE tempdb MODIFY FILE
       (NAME = 'templog', SIZE = target_size_in_MB)
       --Desired target size for the log file
    					
  3. Arrêtez SQL Server en appuyant sur Ctrl-C à partir de la fenêtre d'invite de commandes, redémarrez SQL Server en tant que service, puis vérifiez la taille des fichiers Tempdb.mdf et Templog.ldf.
L'une des limitations de cette méthode est qu'elle fonctionne uniquement sur les fichiers logiques tempdb par défaut, tempdev et templog. Si des fichiers supplémentaires ont été ajoutés à tempdb, vous pouvez les réduire après le redémarrage de SQL Server en tant que service. Tous les fichiers tempdb sont recréés au cours du démarrage ; par conséquent, ils sont vides et peuvent être supprimés. Pour supprimer des fichiers supplémentaires dans tempdb, utilisez la commande ALTER DATABASE avec l'option REMOVE FILE.

Méthode 2 pour réduire Tempdb

Utilisez la commande DBCC SHRINKDATABASE pour réduire la base de données tempdb dans son ensemble. DBCC SHRINKDATABASE reçoit le paramètre target_percent, lequel représente le pourcentage d'espace libre que vous souhaitez conserver dans la fichier de base de données après réduction de la base de données. Si vous utilisez DBCC SHRINKDATABASE, vous devrez peut-être redémarrer SQL Server.

IMPORTANT : Si vous exécutez DBCC SHRINKDATABASE, aucune autre activité ne peut avoir lieu au niveau de la base de données tempdb. Pour vous assurer que les autres processus ne peuvent pas utiliser tempdb alors que DBCC SHRINKDATABASE s'exécute, vous devez démarrer SQL Server en mode mono-utilisateur. Pour plus d'informations, reportez-vous à la section Effets de l'exécution de DBCC SHRINKDATABASE ou DBCCSHRINKFILE alors que Tempdb est en cours d'utilisation de cet article.
  1. Déterminez l'espace en cours d'utilisation dans tempdb à l'aide de la procédure stockée sp_spaceused. Calculez ensuite le pourcentage d'espace libre pour utilisation en tant que paramètre pour DBCC SHRINKDATABASE ; ce calcul est basé sur la taille de base de données souhaitée.

    REMARQUE : Dans certains cas, vous devrez peut-être exécuter sp_spaceused @updateusage=true afin de recalculer l'espace utilisé et pour obtenir un rapport mis à jour. Pour plus d'informations sur la procédure stockée sp_spaceused, consultez la Documentation en ligne de SQL Server.

    Prenons l'exemple suivant :
    Supposons que tempdb comporte deux fichiers, le fichier de données principal (Tempdb.mdf), d'une taille de 100 Mo et le fichier journal (Tempdb.ldf), de 30 Mo. Supposons que sp_spaceused indique que le fichier de données principal contient 60 Mo de données. Supposons également que vous voulez réduire le fichier de données principal à 80 Mo. Calculez le pourcentage d'espace libre restant souhaité après la réduction, 80 Mo – 60 Mo = 20 Mo. À présent, divisez 20 Mo par 80 Mo = 25% et vous obtenez votre pourcentage_cible. Le fichier journal des transactions est réduit en conséquence, ce qui laisse 25% ou 20 Mo d'espace libre une fois la base de données réduite.
  2. Connectez-vous à SQL Server avec Query Analyzer, puis exécutez les commandes Transact-SQL suivantes :
       dbcc shrinkdatabase (tempdb, 'target percent') 
       -- This command shrinks the tempdb database as a whole
    					
Il existe certaines limitations lors de l'utilisation de la commande DBCC SHRINKDATABASE sur la base de données tempdb. La taille cible pour les fichiers de données et les fichiers journaux ne peut pas être inférieure à la taille spécifiée lorsque la base de données à été créée ou à la dernière taille définie de manière explicite à l'aide d'une opération de modification de taille de fichier telle que ALTER DATABASE avec l'option MODIFY FILE ou la commande DBCC SHRINKFILE. Une autre limitation de DBCC SHRINKDATABASE est le calcul du paramètre target_percentage et sa dépendance sur l'espace en cours utilisé.



Méthode 3 pour réduire Tempdb

Utilisez la commande DBCC SHRINKFILE pour réduire les fichiers tempdb individuels. DBCC SHRINKFILE offre davantage de souplesse que DBCC SHRINKDATABASE car vous pouvez l'utiliser sur un seul fichier de base de données sans affecter les autres fichiers qui appartiennent à la même base de données. DBCC SHRINKFILE reçoit le paramètre target size, lequel représente la taille finale souhaitée pour le fichier de base de données.

IMPORTANT : Vous devez exécuter DBCC SHRINKFILE alors qu'aucune autre activité n'est en cours dans la base de données tempdb. Pour vous assurer que les autres processus ne peuvent pas utiliser tempdb alors que DBCC SHRINKFILE s'exécute, vous devez redémarrer SQL Server en mode mono-utilisateur. Pour plus d'informations sur DBCC SHRINKFILE, reportez-vous à la section Effets de l'exécution de DBCC SHRINKDATABASE ou DBCCSHRINKFILE alors que Tempdb est en cours d'utilisation de cet article.
  1. Déterminez la taille souhaitée pour le fichier de données principal (tempdb.mdf), le fichier journal (templog.ldf), et/ou les fichiers supplémentaires ajoutés à tempdb. Assurez-vous que l'espace utilisé dans les fichiers est inférieur ou égal à la taille cible souhaitée.
  2. Connectez-vous à SQL Server avec Query Analyzer, puis exécutez les commandes Transact-SQL suivantes pour les fichiers de base de données spécifiques que vous devez réduire :
       use tempdb
       go
    
       dbcc shrinkfile (tempdev, 'target size in MB')
       go
       -- this command shrinks the primary data file
    
       dbcc shrinkfile (templog, 'target size in MB')
       go
       -- this command shrinks the log file, look at the last paragraph.
    						
L'un des avantages de DBCC SHRINKFILE est qu'elle peut réduire la taille d'un fichier à une taille inférieure à sa taille d'origine. Vous pouvez exécuter DBCC SHRINKFILE sur les fichiers de données ou les fichiers journaux. L'une des limitations de DBCC SHRINKFILE est que vous ne pouvez pas réduire la base de données à une taille inférieure à la taille de la base de données modèle.

Dans SQL Server 7.0, une réduction du journal des transactions est une opération différée et vous devez effectuer une troncation et une sauvegarde de journal pour permettre l'opération de réduction dans une base de données. Toutefois, par défaut, dans tempdb, l'option trunc log on chkpt est définie sur ON ; par conséquent, vous n'avez pas à effectuer de troncation de journal pour cette base de données. Pour plus d'informations sur la façon de réduire un journal des transactions d'une base de données dans SQL Server 7.0, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
256650  (http://support.microsoft.com/kb/256650/ ) INF : Comment faire pour réduire le journal des transactions de SQL Server 7.0

Effets de l'exécution de DBCC SHRINKDATABASE ou DBCCSHRINKFILE alors que Tempdb est en cours d'utilisation

Si tempdb est en cours d'utilisation et que vous essayez de la réduire à l'aide des commandes DBCC SHRINKDATABASE ou DBCC SHRINKFILE, plusieurs erreurs de cohérence semblables aux suivantes peuvent s'afficher et l'opération de réduction peut échouer :
Serveur : Msg 2501, Niveau 16, État 1, Ligne 1 Impossible de trouver la table nommée '1525580473'. Vérifiez sysobjects.
- ou -
Serveur : Msg 8909, Niveau 16, État 1, Ligne 0 Table endommagée : ID objet 1, index ID 0, page ID %S_PGID. PageId dans l'en-tête de page = %S_PGID.
Même si l'erreur 2501 n'indique pas une altération dans tempdb, elle entraîne l'échec de l'opération de réduction. En revanche, l'erreur 8909 peut indiquer une altération dans la base de données tempdb. Redémarrez SQL Server pour recréer tempdb et corrigez les erreurs de cohérence. Toutefois, gardez à l'esprit qu'il peut y avoir d'autres raisons à l'altération des données physiques comme l'erreur 8909, par exemple des problèmes de sous-système d'E/S.

RÉFÉRENCES

Documentation Microsoft SQL Server en ligne ; rubriques : "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"


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 2000 Édition 64 bits
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Mots-clés : 
kbsqlserverengine kbhowtomaster KB307487
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.
Partager
Options de support supplémentaire
Forums du support Microsoft Community
Nous contacter directement
Trouver un partenaire Microsoft Certified Partner
Microsoft Store