Il arrive parfois en tant qu’administrateur de base de données, d’avoir besoin de déplacer la base de données TempDB et les fichiers journaux vers un nouveau disque dur.
Ceci arrive par exemple si vous avez installé SQL Server sur le C:\ et que vous n’avez plus d’espace pour traiter vos requêtes. Il est donc nécessaire de déplacer TempDB vers un disque avec plus d’espace libre.
Cet article explique toutes les étapes à suivre pour déplacer les fichiers TempDB.
Vue d’ensemble des étapes pour déplacer TempDB et les fichiers journaux vers un nouvel emplacement
- Identifier l’emplacement des données TempDB et des fichiers journaux
- Modifier l’emplacement des données TempDB et des fichiers journaux à l’aide de ALTER DATABASE
- Arrêter et redémarrer le service SQL Server
- Vérifier la modification du chemin
- Supprimer l’ancien TempDB ainsi que les fichiers .mdf et .ldf
Identifier l’emplacement des données TempDB et des fichiers journaux
Dans la fenêtre requête de SQL Server Management Studio, exécutez le script ci-dessous pour identifier l’emplacement des données TempDB et du fichier journal :
Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
Le résultat de cette requête nous montre donc la présence du TempDB dans le dossier par défaut de l’installation de SQL Server
Une fois que vous avez identifié l’emplacement des fichiers TempDB, l’étape suivante consistera à créer les dossiers respectifs sur le nouveau disque dur où vous souhaitez stocker les données TempDB et le fichier journal.
Cependant, vous devez vous assurer que le nouvel emplacement où les fichiers TempDB sont stockés est accessible par SQL Server. C’est-à-dire que vous devez vous assurer que le compte sous lequel le service SQL Server s’exécute dispose des autorisations de lecture et d’écriture sur le dossier dans lequel les fichiers sont stockés.
Modifiez l’emplacement des fichiers de données et des fichiers journaux TempDB à l’aide de ALTER DATABASE
Exécutez la commande ALTER DATABASE ci-dessous pour modifier l’emplacement des données TempDB et du fichier journal dans SQL Server :
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO
Astuce
Il se trouve que parfois on a plusieurs bases tempDB à déplacer, comme le montre notre exemple au-dessus. Au lieu de faire plusieurs copier / coller fastidieuses, un petit script pour générer toutes les requêtes de déplacements TempDB :
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
Et le résultat est :
Ainsi, un simple copier / coller de l’ensemble des requêtes pour les exécuter en une fois !
Résultat de l’exécution :
Arrêter et redémarrer le service SQL Server
Arrêtez et redémarrez l’instance de SQL Server pour que les modifications prennent effet.
Bouton droit sur le nom de l’instance, et choisir « Redémarrer ».
Vérifier le changement de l’emplacement des fichiers de données et des fichiers journaux TempDB
Il suffit alors de ré-exécuter la toute première requête
Supprimer les anciens fichiers tempdb.mdf et templog.ldf
La dernière étape consistera à supprimer les fichiers tempdb.mdf et templog.ldf de l’emplacement d’origine. Il suffit donc de s’y rendre à l’emplacement et de les supprimer avec la touche SUPPR de votre clavier ou avec le bouton droit de la souris.
Remarque : SQL Server ne prend pas en charge le déplacement de la base de données TempDB à l’aide de la sauvegarde/restauration et en utilisant les méthodes de détachement de la base de données. La seule façon de le faire est par code Transact SQL comme montré dans cet article.