Réduction de la base de données tempdb

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Cet article décrit différentes méthodes que vous pouvez utiliser pour réduire la tempdb base de données 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, suivez les instructions de Réduire une base de données.

Method Nécessite un redémarrage ? Informations complémentaires
ALTER DATABASE Oui Donne un contrôle complet sur la taille des fichiers par défaut tempdb (tempdev et templog).
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éduisez les fichiers de base de données via une interface utilisateur graphique.

Remarques

Par défaut, la tempdb base de données 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 plus volumineuses tempdb 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 model base de données 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 celle qui utilise l’option MODIFY FILE ou les DBCC SHRINKFILE instructions 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 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 tempdb l’activité est en cours. Toutefois, vous pouvez rencontrer d’autres erreurs telles que le blocage, les blocages, etc. qui peuvent empêcher la réduction de la fin. Par conséquent, pour vous assurer qu’une réduction des tempdb réussites aboutit, nous vous recommandons de le faire pendant que le serveur est en mode mono-utilisateur ou lorsque vous avez arrêté toutes les tempdb activités.

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

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

Utiliser la commande ALTER DATABASE

Note

Cette commande fonctionne uniquement sur les fichiers tempdev logiques par défaut tempdb et templog. Si d’autres fichiers sont ajoutés tempdb, vous pouvez les réduire après le redémarrage de SQL Server en tant que service. Tous les tempdb fichiers sont recréé au démarrage. Toutefois, ils sont vides et peuvent être supprimés. Pour supprimer des fichiers supplémentaires dans tempdb, utilisez la ALTER DATABASE commande avec l’option REMOVE FILE .

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

  1. Arrêtez SQL Server.

  2. À l’invite de commandes, démarrez l’instance en mode de configuration minimal. Pour cela, procédez comme suit :

    1. À l’invite de commandes, accédez au dossier où SQL Server est installé (remplacez <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

      Note

      Les -c paramètres et -f les paramètres entraînent le démarrage de SQL Server en mode de configuration minimal dont la tempdb taille est de 1 Mo pour le fichier de données et de 0,5 Mo pour le fichier journal. Le -mSQLCMD paramètre 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 et templog.ldf des tempdb.mdf fichiers.

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 une fois la base de données réduite. Si vous utilisez DBCC SHRINKDATABASE, vous devrez peut-être redémarrer SQL Server.

  1. Déterminez l’espace actuellement utilisé à tempdb l’aide de la sp_spaceused procédure stockée. 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.

    Note

    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 (Transact-SQL).

    Prenons l’exemple suivant :

    Supposons qu’il tempdb comporte deux fichiers : le fichier de données principal (tempdb.mdf) de 1024 Mo et le fichier journal (tempdb.ldf) qui est de 360 Mo. Supposons que sp_spaceused 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 c’est votre target_percent. Le fichier journal des 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 SQL Server Management Studio, Azure Data Studio ou sqlcmd, puis exécutez la commande Transact-SQL suivante. Remplacez par <target_percent> le pourcentage souhaité :

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

Il existe des limitations avec la DBCC SHRINKDATABASE commande 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 celle qui utilise l’option MODIFY FILE . Une autre limitation DBCC SHRINKDATABASE est le calcul du target_percentage paramètre et sa dépendance à l’espace actuel utilisé.

Utiliser la commande DBCC SHRINKFILE

Utilisez la DBCC SHRINKFILE commande pour réduire les fichiers individuels tempdb . DBCC SHRINKFILE offre plus de flexibilité que parce que DBCC SHRINKDATABASE 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 target_size paramètre. 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 SQL Server Management Studio, 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 est DBCC SHRINKFILE qu’il peut réduire la taille d’un fichier à une taille inférieure à sa taille d’origine. Vous pouvez émettre un problème DBCC SHRINKFILE sur l’un des fichiers journaux ou de données. Vous ne pouvez pas réduire la taille de la model base de données.

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

Si tempdb elle est utilisée et si vous essayez de la réduire à l’aide des commandes ou DBCC SHRINKFILE des DBCC SHRINKDATABASE commandes, 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.

Voir aussi

Étapes suivantes