After installing a few standalone SQL Servers yesterday, I today discovered that the tempdb databases were located in invalid directories according to EVRY installing guide. The tempdb should be located on another disk than the data and system databases. In our project we have allocated the k: drive for the tempdb.mdf and the templog.ldf should be located together with the other log files.
You can use the below SELECT to find out where tempdb files currently are located:
SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO
In my case, these where installed in the SQL Server default location on the c: drive, and I needed to move the MDF (data) file to k: drive and LDF (log) to the j: drive. I used the following statements to configure the new directories for tempdb:
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL\Data\templog.ldf'); GO
You need to restart SQL server services, and the temp files will be recreated, and you can delete the old files.