Déplacer le stockage de la base TEMPDB

La base de données système « tempdb » est une base particulière qui est utilisée par tous les objets temporaires explicite (ceux créé avec un nom commençant par #) comme ceux créé de manière implicite, par le moteur SQL Server, notamment au cours de l’exécution d’un requête manipulant une importante quantité de données.
Le stockage de cette base doit être étudiée avec soin, notamment s’il est fait fréquemment recours aux tables temporaires, aux variables de type « table », au curseurs ou si l’on fait appel au mécanisme de verrouillage optimiste (niveau d’isolation « snapshot »).
D’où l’intérêt de soigner son stockage, sur un disque le plus rapide possible (NVMe par exemple) ou au minimum SSD « write intensive ».
Mais comment faire pour déplacer le stockage de la base tempdb si l’on a pas créer les fichiers de la base ou il fallait ?
Cet article répond à cette demande…

En préalable il est important savoir ou sont les fichiers actuels de tempdb. La requête suivante va vous le montrer…

SELECT physical_name
FROM   tempdb.sys.database_files;

Notez ces fichiers et ces emplacement, vous en aurez besoin pour finaliser le processus.

Vérifier bien que le compte de service de SQL Server possède le droit d’écriture et lecture sur l’emplacement de stockage visé.

Le script de déplacement est le suivant :

DECLARE @NEW_PATH_TEMPDB NVARCHAR(2048) = 'E:\SQLPROD\TEMPDB\';
DECLARE @SQL NVARCHAR(max) = N''

SELECT @SQL = @SQL + N'ALTER DATABASE tempdb MODIFY FILE (NAME = ''' 
                   + name + ''', FILENAME = ''' + @NEW_PATH_TEMPDB  
                   + REVERSE(LEFT(REVERSE(physical_name), CHARINDEX('\', REVERSE(physical_name)) - 1)) + ''');'
FROM   tempdb.sys.database_files;
EXEC (@SQL);

La variable @NEW_PATH_TEMPDB est celle qui doit contenir le nouvel emplacement de stockage des fichiers de la base tempdb. Bien entendu vous devez y mettre un chemin existant terminé par un « backslash ».

Après l’exécution de ce script, vous devez redémarrer le service SQL Server.

Après le redémarrage du service SQL Server, vous devez retirer les anciens fichiers utilisés par la base tempdb que vous avez préalablement notés.


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans Procédures MS SQL Server, Requêtes de métadonnées, scripts SQL, SQL Server, avec comme mot(s)-clé(s) , , , . Vous pouvez le mettre en favoris avec ce permalien.