Réduire la taille de tempdb

Article coécrit avec Claude, qui a mené les tests en labo et la lecture de la documentation.

La taille courante et la taille configurée

tempdb est la base de travail de l’instance. SQL Server y stocke les tables temporaires et les variables table, mais aussi ses propres objets internes : les work tables des curseurs et des spools, les tris et les hachages qui ne tiennent pas dans la mémoire allouée à la requête (on dit qu’ils débordent, spill en anglais), et la banque de versions des niveaux d’isolation par versionnement de lignes. Ses fichiers grandissent quand la charge le demande, et ne rétrécissent pas d’eux-mêmes.

Chaque fichier de tempdb a deux tailles :

• la taille courante, celle du fichier sur le disque, visible dans tempdb.sys.database_files ;
• la taille configurée, celle à laquelle SQL Server recrée le fichier à chaque démarrage, visible dans sys.master_files.

Selon la documentation de Microsoft, la taille configurée est la dernière taille posée explicitement par ALTER DATABASE ... MODIFY FILE, DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Une croissance automatique ne la change pas.

La requête suivante compare les deux, avec l’espace réellement utilisé dans chaque fichier :

USE tempdb;

SELECT f.file_id, f.name, f.type_desc,
       f.size / 128 AS taille_courante_mo,
       CAST(FILEPROPERTY(f.name, 'SpaceUsed') / 128.0 AS decimal(12, 1)) AS utilise_mo,
       m.size / 128 AS taille_configuree_mo
FROM tempdb.sys.database_files AS f
JOIN sys.master_files AS m
    ON m.database_id = DB_ID('tempdb') AND m.file_id = f.file_id
ORDER BY f.file_id;

Si l’espace utilisé est faible, que la taille configurée est celle que vous voulez et que vous pouvez attendre le prochain redémarrage, il n’y a rien à faire : tempdb reviendra à sa taille configurée. Si la taille configurée est trop grande, corrigez-la d’abord (voir plus bas). Le shrink à chaud sert quand le disque est plein, ou quand il faut récupérer de l’espace avant une intervention sans arrêter le service.

Réduire les fichiers avec DBCC SHRINKFILE

La commande est la même que pour une base utilisateur, exécutée dans tempdb. Le guide du shrink en décrit le fonctionnement général ; voici ce qui change pour tempdb.

Réduisez tous les fichiers de données à la même cible. SQL Server répartit les allocations entre les fichiers de tempdb selon leur espace libre (remplissage proportionnel, proportional fill). Des fichiers de tailles différentes reçoivent des allocations inégales, et le plus gros concentre la contention sur ses pages d’allocation. Le fichier journal (templog) se réduit à part.

Commencez par TRUNCATEONLY, qui rend au système l’espace libre situé en fin de fichier sans déplacer de page. C’est instantané quand la fin du fichier est vide :

USE tempdb;

DBCC SHRINKFILE (tempdev, TRUNCATEONLY);

Comme tout shrink, TRUNCATEONLY remplace la taille configurée par la taille atteinte. En labo, un fichier de 72 Mo configuré à 8 Mo s’est arrêté à 16 Mo, et sa taille configurée est passée de 8 à 16 Mo. Si vous vous arrêtez après ce passage, contrôlez sys.master_files (voir plus bas).

Puis donnez une taille cible en Mo à chaque fichier de données :

USE tempdb;

DBCC SHRINKFILE (tempdev, 8192);
DBCC SHRINKFILE (temp2, 8192);
-- ... un appel par fichier de données, avec la même cible

Les noms logiques (tempdev, temp2, etc.) sont ceux de la colonne name de la requête précédente : remplacez-les par les vôtres. Lancez ensuite la requête de contrôle pour chaque fichier. Si un fichier n’a pas atteint sa cible, quelque chose occupe des pages qu’il ne peut pas déplacer, ou a bloqué le shrink. Un shrink qui a attendu un tri qui déborde peut aussi s’arrêter au-dessus de sa cible sans aucun message, sur les pages de ce tri : en labo, des fichiers réduits vers 8 Mo se sont arrêtés entre 30 et 48 Mo. Relancé une fois la requête terminée, le shrink est allé à 8 Mo.

Un membre du rôle db_owner de tempdb peut lancer la commande, mais tempdb est recréée à partir de model à chaque démarrage : un utilisateur ajouté dans tempdb disparaît au redémarrage suivant. En pratique, le shrink de tempdb se fait avec un compte sysadmin.

Les blocages du shrink de tempdb

Les blocages décrits ici sont ceux que j’ai rencontrés chez un client. Sur une instance de préproduction SQL Server 2019, tempdb avait grandi jusqu’à remplir son disque de 400 Go. Le shrink restait à 99,99 % depuis quatorze minutes, avec des messages de work table dans la sortie. En production, sur un autre serveur, un DBCC SHRINKFILE à 8 Go n’avait réduit que trois fichiers sur huit au premier passage. Je les ai reproduits sur SQL Server 2025 CU10, dans un conteneur Linux.

Une requête qui déborde dans tempdb

En préproduction, la requête suivante montrait le shrink en attente :

SELECT r.session_id, r.command, r.wait_type, r.wait_resource,
       r.wait_time, r.blocking_session_id, r.percent_complete
FROM sys.dm_exec_requests AS r
WHERE r.command = 'DbccFilesCompact';

La commande DbccFilesCompact (le shrink) attendait sur LCK_M_BU, ressource DATABASE: 2:0 [ENCRYPTION_SCAN], bloquée par une requête de l’application, qui détenait un verrou sur la même ressource. Le nom de la ressource fait penser au chiffrement TDE, qui n’était pas activé. La requête bloquante faisait un tri d’un million de lignes qui débordait dans tempdb.

En labo, j’ai reproduit l’attente à l’identique : un tri qui déborde (une table de deux millions de lignes triée avec une allocation mémoire volontairement réduite) détient un verrou partagé (S) sur la ressource ENCRYPTION_SCAN de tempdb, et le shrink demande un verrou BU sur la même ressource. Vous voyez les deux dans sys.dm_tran_locks. Le shrink n’a attendu que lorsqu’il avait des pages à déplacer : quand la fin du fichier était déjà libre, il s’est terminé en quelques millisecondes malgré le verrou du tri.

SELECT request_session_id, resource_type, resource_subtype,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('tempdb')
  AND resource_type = 'DATABASE';

Le verrou partagé dure le temps de l’instruction : quand le tri s’est terminé, le shrink est reparti 16 millisecondes plus tard, alors que le lot de la session bloquante continuait sur un WAITFOR. Une suite de requêtes qui débordent l’une après l’autre peut le faire attendre bien plus longtemps. Dans une boucle de tris de 18 secondes chacun, le shrink a attendu la fin de la boucle, de 95 à 106 secondes, dans mes premiers essais ; dans d’autres, refaits plus tard sur la même instance, il est passé dès la fin du premier tri. Je n’ai pas trouvé ce qui fait la différence.

Avec WAIT_AT_LOW_PRIORITY

Depuis SQL Server 2022, DBCC SHRINKFILE accepte l’option WAIT_AT_LOW_PRIORITY. Elle est documentée pour le verrou Sch-M que le shrink prend sur les pages IAM : le shrink attend alors à basse priorité pendant une minute, puis abandonne (ABORT_AFTER_WAIT = SELF) ou tue les sessions qui le bloquent (ABORT_AFTER_WAIT = BLOCKERS).

Contre le verrou ENCRYPTION_SCAN, le délai d’une minute ne s’applique pas. Face à un seul tri d’environ deux minutes, le shrink lancé avec ABORT_AFTER_WAIT = SELF a attendu 116 secondes, jusqu’à la fin du tri, sans abandonner ; avec ABORT_AFTER_WAIT = BLOCKERS, il a attendu 116 secondes aussi, sans tuer la session du tri. Aucune erreur 49516 dans les deux cas. Deux relecteurs ont refait chacun l’un de ces essais sur leur propre instance, avec le même résultat. N’attendez donc pas de BLOCKERS qu’il libère un shrink de tempdb bloqué par une requête qui déborde.

Contre une rafale de tris, l’option a parfois permis au shrink de passer à la fin du premier tri (11 à 17 secondes, dans quatre essais), mais le même résultat s’est produit sans l’option dans d’autres essais : je ne peux pas lui attribuer ce gain.

Pour éviter ces attentes, corrigez les requêtes qui débordent. Une requête qui déborde a reçu moins de mémoire qu’il ne lui en fallait, souvent à cause d’une estimation de cardinalité trop basse ou d’un index manquant. En préproduction, l’un des deux plans bloquants signalait un index manquant, l’autre triait toute une table d’historique. C’est aussi ce qui avait fait grossir tempdb.

Les work tables d’une session active

Un curseur statique copie son résultat dans une work table de tempdb, et la garde tant qu’il est ouvert. Quand le shrink atteint une page de cette table, il s’arrête avec un message d’information :

DBCC SHRINKFILE: Page 4:17407 could not be moved because it is a work table page.

C’est le message 5207, de gravité 10 : la commande ne renvoie pas d’erreur, elle réduit le fichier jusqu’à cette page et s’arrête là. Quand la page est la dernière du fichier, comme ici (page 17 407 d’un fichier de 17 408 pages), le fichier ne bouge pas du tout. sys.dm_db_page_info ne vous aide pas ici : pour cette page, elle renvoie un type DATA_PAGE sans object_id, puisqu’une work table n’est pas un objet du catalogue.

Pour trouver la session qui détient des objets internes, comparez les pages allouées et libérées par session (sys.dm_db_session_space_usage) et par tâche en cours (sys.dm_db_task_space_usage) :

SELECT s.session_id, s.login_name, s.program_name,
       CAST((u.internal_objects_alloc_page_count
             - u.internal_objects_dealloc_page_count) / 128.0 AS decimal(12, 1)) AS objets_internes_mo,
       ISNULL(t.internes_en_cours_mo, 0) AS internes_requete_en_cours_mo
FROM sys.dm_db_session_space_usage AS u
JOIN sys.dm_exec_sessions AS s ON s.session_id = u.session_id
LEFT JOIN (SELECT session_id,
                  CAST(SUM(internal_objects_alloc_page_count
                           - internal_objects_dealloc_page_count) / 128.0 AS decimal(12, 1)) AS internes_en_cours_mo
           FROM sys.dm_db_task_space_usage
           GROUP BY session_id) AS t
    ON t.session_id = u.session_id
WHERE s.is_user_process = 1
ORDER BY 4 DESC, 5 DESC;

En labo, la session du curseur ouvert apparaissait avec 436 Mo d’objets internes. Une fois le curseur fermé, le même shrink a ramené le fichier de 136 Mo à 8 Mo.

Les work tables gardées avec un plan en cache

Ici, le message 5207 persiste alors qu’aucune session ne détient d’objet interne. La requête précédente renvoie zéro partout, et pourtant des pages de work tables restent en fin de fichier.

SQL Server garde certaines work tables avec le plan en cache de la requête qui les a créées. La documentation l’évoque à deux endroits : le compteur Worktables From Cache Ratio de l’objet Access Methods indique que deux pages d’une work table supprimée peuvent rester allouées et retourner dans un cache de work tables, et sys.dm_exec_plan_attributes précise que les curseurs inactifs sont gardés dans le plan compilé. En labo, deux cas l’ont montré, une fois la requête terminée et la session inactive :

• un lot qui ouvre, lit, ferme et désalloue un curseur statique ;
• une CTE récursive, dont le plan utilise un spool en tempdb.

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables'), qui vide le cache des tables temporaires, n’a rien changé. Retirer le plan du lot avec DBCC FREEPROCCACHE et son plan_handle a libéré les pages, et le shrink est allé au bout. Le même essai avec un curseur dans une procédure stockée donne la répartition par cache :

Si vous savez quelle requête est en cause, retirez son seul plan. Trouvez son plan_handle dans le cache :

SELECT cp.plan_handle, cp.objtype, cp.usecounts, t.text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS t
WHERE t.text LIKE '%CURSOR%STATIC%'
  AND t.text NOT LIKE '%dm_exec_cached_plans%';

Le filtre vise les curseurs statiques ; pour un autre cas, cherchez un extrait du texte de la requête en cause. La seconde condition écarte la requête de recherche elle-même. Passez ensuite le plan_handle à DBCC FREEPROCCACHE. La valeur ci-dessous est un exemple : remplacez-la par celle que la requête vous a donnée.

DBCC FREEPROCCACHE (0x06000200DD3B4127A0C913C50B00000001000000000000000000000000000000000000000000000000000000);

Sinon, DBCC FREESYSTEMCACHE avec 'SQL Plans' vide les plans ad hoc et préparés, 'Object Plans' ceux des procédures, et 'ALL' tous les caches. La page ne liste pas les noms de cache acceptés : ce sont ceux de la colonne name de sys.dm_os_memory_cache_counters. Chaque plan retiré sera recompilé à sa prochaine exécution : sur une production chargée, un vidage complet provoque une vague de compilations et une pointe de CPU. Choisissez le cache le plus étroit qui suffit.

Le même mécanisme explique probablement le cas de production du client. Après un DBCC FREESYSTEMCACHE ('ALL'), le second passage du shrink a ramené les huit fichiers à 8 Go. Je n’avais pas relevé les messages du premier passage, donc la cause n’est pas prouvée sur ce serveur : elle est cohérente avec les mesures du labo.

La taille de démarrage après un shrink partiel

Un shrink qui s’arrête avant sa cible ne laisse pas la taille configurée en l’état : il la remplace par la taille atteinte. En labo, un fichier configuré à 8 Mo et réduit vers 8 Mo s’est arrêté à 108 Mo, bloqué par la work table d’un curseur ouvert, et sys.master_files indiquait ensuite 108 Mo. La taille configurée peut donc augmenter : un DBCC SHRINKFILE (tempdev, 32) sur un fichier configuré à 8 Mo la fait passer à 32 Mo. Après redémarrage du conteneur, tempdb a été recréée aux tailles configurées, 32 Mo pour ce fichier.

Chez le client, le premier passage avait laissé trois fichiers à 8 Go et cinq entre 34 et 78 Go, tailles configurées comprises. Un redémarrage à ce moment-là aurait recréé ces tailles inégales.

Après un shrink, relancez donc la requête de contrôle et comparez taille_configuree_mo à ce que vous voulez au prochain démarrage. Pour corriger, ALTER DATABASE ... MODIFY FILE accepte sur tempdb une taille inférieure à la taille courante :

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB);

La commande ne touche pas au fichier : la taille courante reste la même, seule la taille configurée change, et elle s’applique au redémarrage suivant. Sur SQL Server 2025 CU10, je l’ai vérifié sur un fichier ajouté (tempdev3, ramené de 136 Mo courants à 16 Mo configurés), redémarrage compris ; SQL Server 2022 CU26 accepte la même commande. La même commande sur une base utilisateur échoue avec l’erreur 5039 (« Specified size is less than or equal to current size »).

C’est aussi la méthode la plus simple quand un redémarrage est prévu : posez la taille voulue sur chaque fichier, puis redémarrez. La documentation décrit cette méthode en mode de configuration minimale (-f), et la dit limitée aux fichiers tempdev et templog. Mesuré, elle fonctionne en fonctionnement normal et sur tous les fichiers.

Le shrink de tempdb avec SqlGoPace

J’ai écrit SqlGoPace, un outil libre qui exécute les opérations lourdes de maintenance en surveillant l’instance pendant qu’elles tournent. Il a une opération dédiée, shrink_tempdb, née du cas de préproduction décrit plus haut :

- operation: shrink_tempdb
  targetsizemb: 8192    # même cible pour chaque fichier de données

Elle procède de la même façon : un passage TRUNCATEONLY sur tous les fichiers de données avant tout déplacement de page, puis un shrink par paliers de chaque fichier vers la même cible. Elle ajoute WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF) à partir de SQL Server 2022, ne tue jamais une session bloquante, s’arrête proprement quand un fichier ne progresse plus, et signale à la fin des fichiers de tailles inégales. Sa documentation détaille ces choix. Contrôlez la taille configurée après son passage, comme après un shrink à la main.

L’option flushcaches, désactivée par défaut, ajoute une escalade quand un fichier ne progresse plus malgré plusieurs reprises (message 5207, palier sans gain, ou erreur 845 de délai d’attente d’un latch) :

- operation: shrink_tempdb
  targetsizemb: 8192
  flushcaches: true     # vidage ciblé si un fichier reste bloqué

SqlGoPace exécute alors, une seule fois par exécution, un CHECKPOINT suivi de DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables'), qui vide le cache des tables temporaires et des variables table. Il ne vide jamais tout le cache des plans, à cause de la vague de recompilations sur l’instance. Dans les cas mesurés plus haut, ce cache ne contenait pas les work tables qui bloquaient le shrink : elles n’ont été libérées qu’en retirant le plan qui les gardait. Si le shrink reste bloqué après ce vidage, cherchez le plan en cause avec la requête sur sys.dm_exec_cached_plans.

Une prochaine version de SqlGoPace prendra en charge ce que les essais de cet article ont montré : chercher d’abord les objets et les plans qui retiennent des pages, puis retirer seulement ces plans avant de reprendre le shrink. Elle corrigera aussi le passage TRUNCATEONLY initial, qui peut relever la taille configurée si l’opération s’arrête ensuite.

Supprimer des fichiers de tempdb

Réduire le nombre de fichiers de tempdb est une autre opération, qui passe par DBCC SHRINKFILE ... EMPTYFILE et se heurte aux mêmes work tables. Elle est décrite dans Supprimer des fichiers de tempdb.