SQL Server : déplacer les fichiers du tempdb
9 juin 2021
Sous SQL Server, la base système tempdb sert à disposer d’un espace de stockage pour y créer des tables temporaires et aussi faire des opérations de tris, de regroupement dans l’exécution des requêtes. Installer cette base dans le dossier par défaut est donc une très mauvaise idée.
La création du répertoire
Sous l’interpréteur de commandes Windows (cmd.exe), créez tout d’abord votre répertoire d’accueil des fichiers associés au tempdb :
md e:\tempdb
Pensez à ce que les utilisateurs NT Service\MSSQLSERVER et NT Service\SQLSERVERAGENT aient des droits en écriture sur ce répertoire fraîchement créé.
La création de la procédure stockée move_tempdb
Je l’ai créée dans la base msdb :
CREATE PROCEDURE move_tempdb(@dest VARCHAR(MAX))
AS
BEGIN
-- Déclaration du curseur des fichiers associés à la base système tempdb
DECLARE cursor_Files CURSOR FOR
SELECT name,physical_name FROM tempdb.sys.database_files;
-- Déclaration des variables utilisées par la procédure stockée
DECLARE @name VARCHAR(MAX);
DECLARE @path VARCHAR(MAX);
-- DECLARE @dest VARCHAR(MAX) = 'e:\tempdb';
DECLARE @file VARCHAR(MAX);
DECLARE @pos INT;
DECLARE @newpath VARCHAR(MAX);
DECLARE @sql VARCHAR(MAX);
OPEN cursor_Files;
FETCH NEXT FROM cursor_Files INTO @name,@path;
WHILE @@FETCH_STATUS <> -1
BEGIN
-- Extraction du nom de fichier du chemin
SET @pos=CHARINDEX('\',REVERSE(@path));
PRINT @pos;
SET @file=RIGHT(@path,@pos)
PRINT @file;
SET @newpath=@dest+@file;
-- Exécution de l'instruction SQL pour déplacer les fichiers
SET @sql='ALTER DATABASE tempdb MODIFY FILE (NAME =''' + @name+ ''', FILENAME=''' + @newpath + ''')';
PRINT @sql;
EXECUTE (@sql);
FETCH NEXT FROM cursor_Files INTO @name,@path;
END;
CLOSE cursor_Files;
DEALLOCATE cursor_Files;
END;
GO
Exécution de la procédure stockée
Vous pouvez exécuter votre procédure stockée sans ou avec l’instruction EXEC :
EXEC msdb.dbo.move_tempdb @dest='e:\tempdb'; EXEC msdb.dbo.move_tempdb 'e:\tempdb'; msdb.dbo.move_tempdb 'e:\tempdb'; msdb.dbo.move_tempdb @dest='e:\tempdb';
La prise en compte du déplacement des fichiers du tempdb se produit à l’occasion du redémarrage de l’instance SQL Server.
Variante : le bloc Transact-SQL
Vous pouvez faire le choix d’exécuter un bloc Transact-SQL en ayant comme constante le répertoire de destination dans le code. De cette façon, vous pouvez l’exécuter dans un job.
-- Déclaration du curseur des fichiers associés à la base système tempdb
DECLARE cursor_Files CURSOR FOR
SELECT name,physical_name FROM tempdb.sys.database_files;
-- Déclaration des variables utilisées par la procédure stockée
DECLARE @name VARCHAR(MAX);
DECLARE @path VARCHAR(MAX);
DECLARE @dest VARCHAR(MAX) = 'e:\tempdb';
DECLARE @file VARCHAR(MAX);
DECLARE @pos INT;
DECLARE @newpath VARCHAR(MAX);
DECLARE @sql VARCHAR(MAX);
OPEN cursor_Files;
FETCH NEXT FROM cursor_Files INTO @name,@path;
WHILE @@FETCH_STATUS <> -1
BEGIN
-- Extraction du nom de fichier du chemin
SET @pos=CHARINDEX('\',REVERSE(@path));
PRINT @pos;
SET @file=RIGHT(@path,@pos)
PRINT @file;
SET @newpath=@dest+@file;
-- Exécution de l'instruction SQL pour déplacer les fichiers
SET @sql='ALTER DATABASE tempdb MODIFY FILE (NAME =''' + @name+ ''', FILENAME=''' + @newpath + ''')';
PRINT @sql;
EXECUTE (@sql);
FETCH NEXT FROM cursor_Files INTO @name,@path;
END;
CLOSE cursor_Files;
DEALLOCATE cursor_Files;
GO