Réduire la base de données tempdb

S’applique à :SQL ServerAzure SQL Managed Instance

Cet article décrit différentes méthodes que vous pouvez utiliser pour réduire la base de données tempdb dans SQL Server.

Vous pouvez utiliser l’une des méthodes suivantes pour modifier la taille de tempdb. Les trois premières options sont décrites dans cet article. Si vous souhaitez utiliser SQL Server Management Studio (SSMS), suivez les instructions dans Réduire une base de données.

Méthode	Nécessite un redémarrage ?	Plus d’informations
ALTER DATABASE	Oui	Donne un contrôle complet sur la taille des fichiers tempdb (tempdev et templog) par défaut.
DBCC SHRINKDATABASE	Non	Fonctionne au niveau de la base de données.
DBCC SHRINKFILE	Non	Vous permet de réduire les fichiers individuels.
SQL Server Management Studio	Non	Réduire les fichiers de base de données via une interface utilisateur graphique.

Notes

Par défaut, la base de données tempdb est configurée pour la croissance automatique en fonction des besoins. Par conséquent, cette base de données peut s’étendre de manière inattendue au fil du temps à une taille supérieure à la taille souhaitée. Les tailles de base de données tempdb plus volumineuses n’affectent pas les performances de SQL Server.

Au démarrage de SQL Server, il recrée tempdb à l’aide d’une copie de la model base de données et réinitialise tempdb sa dernière taille configurée. La taille configurée est la dernière taille explicite que vous avez définie à l’aide d’une opération de modification de taille de fichier telle que ALTER DATABASE l’option MODIFY FILE ou les DBCC SHRINKFILEDBCC SHRINKDATABASE instructions. Par conséquent, sauf si vous devez utiliser différentes valeurs ou que vous souhaitez immédiatement résoudre une base de données volumineuse tempdb , vous pouvez attendre le prochain redémarrage du service SQL Server pour que la taille diminue.

Vous pouvez réduire tempdb pendant que l’activité tempdb est en cours. Toutefois, vous pouvez rencontrer d’autres erreurs telles que le blocage, les impasse, etc. qui peuvent empêcher la réduction de la fin. Pour vous assurer qu'une réduction de tempdb réussit, effectuez cette opération pendant que le serveur est en mode mono-utilisateur ou lorsque vous arrêtez toute activité de tempdb.

SQL Server enregistre seulement assez d’informations dans le journal des transactions tempdb pour annuler une transaction, mais pas pour refaire des transactions pendant la récupération de la base de données. Cette fonctionnalité augmente les performances des instructions INSERT dans tempdb. En outre, vous n’avez pas besoin de journaliser les informations pour rétablir les transactions, car tempdb est recréé chaque fois que vous redémarrez SQL Server. Par conséquent, il n’a pas de transactions à reporter ou à annuler.

Pour plus d’informations sur la gestion et la surveillance tempdb, consultez Planification de la capacité et Surveillance de l’utilisation de tempdb.

Utilisez la commande ALTER DATABASE

Remarque

Cette commande fonctionne uniquement sur les fichiers logiques tempdb par défaut tempdev et templog. Si vous ajoutez d’autres fichiers à 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 démarrage. Toutefois, ces fichiers 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.

Cette méthode vous oblige à redémarrer SQL Server.

Remarque

Vous pouvez vous connecter à une instance de SQL Server à l’aide de n’importe quel outil client SQL Server familier, tel que sqlcmd, SQL Server Management Studio (SSMS) ou l’extension MSSQL pour Visual Studio Code.

1. Arrêtez SQL Server.

2. À l’invite de commande, démarrez l’instance en mode de configuration minimale. Pour ce faire, procédez comme suit :

   1. À l’invite de commande, passer au dossier où SQL Server est installé (remplacer <VersionNumber> et <InstanceName> dans l’exemple suivant) :

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn
      

   2. Si l’instance est une instance nommée de SQL Server, exécutez la commande suivante (remplacez <InstanceName> dans l’exemple suivant) :

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD
      

   3. Si l’instance est l’instance par défaut de SQL Server, exécutez la commande suivante :

      sqlservr -c -f -mSQLCMD
      

      Remarque

      Les paramètres -c et -f entraînent le démarrage de SQL Server en mode de configuration minimal dont la taille tempdb est de 1 Mo pour le fichier de données et de 0,5 Mo pour le fichier journal. Le paramètre -mSQLCMD empêche toute autre application que sqlcmd de reprendre la connexion mono-utilisateur.

3. Connectez-vous à SQL Server avec sqlcmd, puis exécutez les commandes Transact-SQL suivantes. Remplacez <target_size_in_MB> par la taille souhaitée :

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = <target_size_in_MB>);
   
   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'templog', SIZE = <target_size_in_MB>);
   

4. Arrêtez SQL Server. Pour ce faire, appuyez sur Ctrl+C à l’invite de commande, redémarrez SQL Server en service, puis vérifiez la taille des fichiers tempdb.mdf et templog.ldf.

Utiliser la commande DBCC SHRINKDATABASE

DBCC SHRINKDATABASE prend le target_percent paramètre. Ce paramètre définit le pourcentage d’espace libre que vous souhaitez laisser dans le fichier de base de données après avoir réduit la base de données. Si vous utilisez DBCC SHRINKDATABASE, vous devrez peut-être redémarrer SQL Server.

1. Utilisez la sp_spaceused procédure stockée pour vérifier l’espace actuellement utilisé par tempdb. Ensuite, calculez le pourcentage d’espace libre à utiliser comme 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 pour recalculer l’espace utilisé et obtenir un rapport mis à jour. Pour plus d’informations, consultez sp_spaceused.

   Prenons l’exemple suivant :

   Supposons que tempdb comporte deux fichiers : le fichier de données principal (tempdb.mdf) de 1 024 Mo et le fichier journal (tempdb.ldf) de 360 Mo. Supposons que sp_spaceused déclare que le fichier de données principal contient 600 Mo de données. Supposons également que vous souhaitez réduire le fichier de données principal à 800 Mo. Calculez le pourcentage souhaité d’espace libre laissé après la réduction : 800 Mo - 600 Mo = 200 Mo. Maintenant, divisez 200 Mo par 800 Mo = 25 % et cette valeur est votre target_percent. Le fichier journal de transactions est réduit en conséquence, laissant 25 % ou 200 Mo d’espace libre une fois la base de données réduite.

2. Exécutez la commande Transact-SQL suivante. Remplacer <target_percent> par le pourcentage souhaité :

   DBCC SHRINKDATABASE (tempdb, '<target_percent>');
   

La DBCC SHRINKDATABASE commande présente des limitations lorsqu’elle est utilisée sur tempdb. Vous ne pouvez pas définir la taille cible des fichiers de données et journaux à une valeur inférieure à celle spécifiée lors de la création de la base de données. Vous ne pouvez pas également la définir plus petite que la dernière taille que vous définissez explicitement à l’aide d’une opération de modification de taille de fichier, comme ALTER DATABASE avec l’option MODIFY FILE . Une autre limitation de DBCC SHRINKDATABASE est le calcul du paramètre target_percentage et sa dépendance à l'espace utilisé actuellement.

Utiliser la commande DBCC SHRINKFILE

Utilisez la DBCC SHRINKFILE commande pour réduire les fichiers individuels tempdb . DBCC SHRINKFILE offre plus de flexibilité que DBCC SHRINKDATABASE parce que vous pouvez l’utiliser sur un fichier de base de données unique sans affecter d’autres fichiers appartenant à la même base de données. DBCC SHRINKFILE prend le target_size paramètre. Ce paramètre définit la taille finale souhaitée pour le fichier de base de données.

1. Déterminez la taille souhaitée pour le fichier de données principal (tempdb.mdf), le fichier journal (templog.ldf) et les fichiers supplémentaires ajoutés à tempdb. Vérifiez que l’espace utilisé dans les fichiers est inférieur ou égal à la taille cible souhaitée.

2. Connectez-vous à SQL Server avec SSMS, Visual Studio Code ou sqlcmd. Exécutez ensuite les commandes Transact-SQL suivantes pour les fichiers de base de données spécifiques que vous souhaitez réduire. Remplacez <target_size_in_MB> par la taille souhaitée :

   USE tempdb;
   GO
   
   -- This command shrinks the primary data file
   DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
   GO
   
   -- This command shrinks the log file, examine the last paragraph.
   DBCC SHRINKFILE (templog, '<target_size_in_MB>');
   GO
   

L’avantage DBCC SHRINKFILE est qu’il peut réduire la taille d’un fichier à une taille inférieure à sa taille d’origine. Vous pouvez exécuter DBCC SHRINKFILE sur n'importe lequel des fichiers de données ou de journal. Vous ne pouvez pas rendre la base de données plus petite que la taille de la base de données model.

Erreur 8909 lorsque vous exécutez des opérations de réduction

Si tempdb est en cours d'utilisation et que vous essayez de le réduire avec les commandes DBCC SHRINKDATABASE ou DBCC SHRINKFILE, vous pouvez recevoir des messages qui ressemblent à la sortie suivante. Le message exact dépend de la version de SQL Server que vous utilisez :

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Cette erreur n’indique pas de corruption réelle dans tempdb. Toutefois, il peut y avoir d’autres raisons pour des erreurs de corruption de données physiques telles que l’erreur 8909 et que ces raisons incluent des problèmes de sous-système d’E/S. Par conséquent, si l’erreur se produit en dehors des opérations de réduction, vous devez examiner plus en détail.

Bien qu’un message 8909 soit retourné à l’application ou à l’utilisateur qui exécute l’opération de réduction, les opérations de réduction ne échouent pas.

Contenu connexe

• Considérations relatives aux paramètres de croissance automatique et de réduction automatique dans SQL Server
• Fichiers et groupes de fichiers de base de données
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• Réduire une base de données
• DBCC SHRINKDATABASE (Transact-SQL)
• FICHIER DE RÉDUCTION DBCC (Transact-SQL)
• Supprimer des données ou des fichiers journaux d’une base de données
• Réduire un fichier