SQL Server : réduire et déplacer fichier tempdb.mdf

Microsoft SQL Server travaille avec des bases de données système pour son fonctionnement : master, model, msdbdata et tempdb. Chaque fois avec un fichier .mdf (données) et un .ldf (journal). Et il peut arriver que le fichier tempdb.mdf et/ou templog.ldf prennent une place considérable sur le disque du serveur où est installé le SGBD.

Il ne faut surtout pas supprimer le fichier tempdb.mdf, base de données système essentielle au bon fonctionnement de MSSQLSERVER. On ne peut pas non plus le shrink et encore moins faire un autoshrink sur cette BDD.

Par contre, comme l’indique Microsoft dans sa documentation, on peut déplacer les fichiers tempdb sur un autre disque.

Ainsi, puisqu’on ne peut pas supprimer ce fichier, ce tutoriel explique comment réduire et limiter la taille de tempdb.mdf sur MS SQL Server. Puisqu’on ne peut pas limiter la taille d’une base temp db, on peut la déplacer sur une partition dédiée qui sera limitée en taille.

Cette opération de maintenance n’est pas dénuée de conséquence puisqu’elle impose un redémarrage de l’instance SQLServer (arrêt et démarrage du service principal de l’instance). Il faudra donc réaliser cette action en dehors des heures d’activité dans un environnement de production. Et vu qu’on touche à des éléments du serveur SQL lui-même, il est recommandé de faire une sauvegarde préalable de la VM ou de la machine.

Cette procédure s’applique à toutes les versions de Microsoft SQL Server, de la plus récente 2019 aux plus anciennes. Par contre, cela ne fonctionnera pas sur Azure SQL Database.

Réduire et bloquer tempdb.mdf (SQL Server)

1. Ouvrir une console SQL Management Studio et se connecter au serveur de base de données.

2. Vérifier l’emplacement et les noms qui concernent la BDD tempdb :

SELECT name, physical_name AS CurrentLocation 
FROM sys.master_files 
WHERE database_id = DB_ID(N'tempdb'); 
GO

3. Modifier l’emplacement du fichier tempdb avec un Alter Database. Par exemple, on le passe du disque D au disque E pour les données et sur un disque F pour les logs (les préconisations Microsoft sont de séparer ces types de fichiers sur des partitions ou disques à part).

USE master; 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf'); 
GO

La réponse va être :

Le fichier "tempdev" a été modifié dans le catalogue système. Le nouveau chemin sera utilisé au prochain démarrage de la base de données.
Le fichier "templog" a été modifié dans le catalogue système. Le nouveau chemin sera utilisé au prochain démarrage de la base de données.

4. Arrêter et redémarrer l’instance de SQL Server.

5. Vérifier que le nouvel emplacement a bien été pris en compte depuis le redémarrage :

SELECT name, physical_name AS CurrentLocation, state_desc 
FROM sys.master_files 
WHERE database_id = DB_ID(N'tempdb');

6. On peut maintenant supprimer les fichiers tempdb.mdf et templog.ldf de leur emplacement d’origine et ainsi récupérer de l’espace disque.

Le service ne démarre plus ?

En cas de problème et si le service MSSQLSERVER ou SQLEXPRESS ne démarre plus, voir dans l’Observateur d’événements Windows (eventvwr.msc) où se situe le souci. Cela peut simplement être un problème d’espace disque libre insuffisant.

Evénement 17053 : E:\sqldata\tempdb.mdf : erreur du système d’exploitation 112 (Espace insuffisant sur le disque.).

Evénement 5173 : Un ou plusieurs fichiers ne correspondent pas au fichier primaire de la base de données. Si vous tentez de joindre une base de données, recommencez l’opération avec les fichiers appropriés. S’il s’agit d’une base de données existante, le fichier est peut-être endommagé et doit être restauré à partir d’une sauvegarde.