SQL Server : déplacer les fichiers du tempdb

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.

SQL Server : déplacer les fichiers du tempdb

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

MSSQL  / Formateur SQL