Réduction de 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 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.
Arrêtez SQL Server.
À l’invite de commandes, démarrez l’instance en mode de configuration minimal. Pour cela, procédez comme suit :
À 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
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
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 latempdb
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.
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>);
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 ettemplog.ldf
destempdb.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.
Déterminez l’espace actuellement utilisé à
tempdb
l’aide de lasp_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 quesp_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 votretarget_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.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.
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.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
- 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)
Étapes suivantes
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour