Réduire la base de données tempdb

S’applique à :SQL Server Azure 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.

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 augmenter de façon inattendue dans le temps jusqu’à 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, tempdb il est recréé à l’aide d’une copie de la base de données model et tempdb est réinitialisé à sa dernière taille configurée. La taille configurée est la dernière taille explicite définie à l’aide d’une opération de modification de taille de fichier telle que ALTER DATABASE qui utilise l’option MODIFY FILE ou les instructions DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Par conséquent, sauf si vous devez utiliser différentes valeurs ou obtenir une résolution immédiate sur une base de données tempdb volumineuse, 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. Par conséquent, afin de vous assurer qu’un rétrécissement de tempdb réussit, nous vous recommandons de le faire lorsque le serveur est en mode mono-utilisateur ou lorsque vous avez arrêté toute l’activité 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

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

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 Ctrl+C sur 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.

Utiliser la commande DBCC SHRINKDATABASE

DBCC SHRINKDATABASE reçoit le paramètre target_percent. Il s’agit du pourcentage souhaité d’espace libre laissé dans le fichier de base de données après que la base de données est réduite. Si vous utilisez DBCC SHRINKDATABASE, vous devrez peut-être redémarrer SQL Server.

1. Déterminez l’espace actuellement utilisé dans tempdb l’aide de la procédure stockée sp_spaceused. Ensuite, calculez le pourcentage d’espace libre laissé pour une utilisation en tant que paramètre à 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 pour cent, et vous obtenez 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. Connectez-vous à SQL Server avec SSMS, Azure Data Studio ou sqlcmd, puis exécutez la commande Transact-SQL suivante. Remplacer <target_percent> par le pourcentage souhaité :

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

Il existe des limitations avec la commande DBCC SHRINKDATABASE sur tempdb. La taille cible des fichiers de données et de journaux ne peut pas être inférieure à la taille spécifiée lors de la création de la base de données, ou inférieure à la dernière taille définie explicitement à l’aide d’une opération de modification de taille de fichier telle que ALTER DATABASE qui utilise l’option MODIFY FILE. Une autre limitation de DBCC SHRINKDATABASE est le calcul du paramètre target_percentage et sa dépendance à l’espace actuel utilisé.

Utiliser la commande DBCC SHRINKFILE

Utilisez la commande DBCC SHRINKFILE 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 reçoit le paramètre target_size. Il s’agit de 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, Azure Data Studio ou sqlcmd, puis exécutez 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 de DBCC SHRINKFILE est qu’il peut réduire la taille d’un fichier à une taille inférieure à sa taille d’origine. Vous pouvez émettre DBCC SHRINKFILE sur l’un des fichiers journaux ou de données. 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 utilisé et si vous essayez de le réduire à l’aide des commandes DBCC SHRINKDATABASE ou DBCC SHRINKFILE, vous pouvez recevoir des messages qui ressemblent à ce qui suit, en fonction 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 effectuer davantage d’investigation.

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
• Groupes de fichiers et 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)
• DBCC SHRINKFILE (Transact-SQL)
• Supprimer des données ou des fichiers journaux d’une base de données
• Réduire un fichier