Today, we are going to see how to move the TEMPDB files from one location to another.
First off, we should know the number of files and the its current location.
SELECT NAME,PHYSICAL_NAME FROM SYS.MASTER_FILES WHERE DATABASE_ID=2
Query to move the files to new location
You may change the @NEWPATH variable value as per your new location.
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @NEWPATH VARCHAR(MAX) = 'D:\DATA\DATA'
SET @SQL= @SQL + (SELECT 'ALTER DATABASE TEMPDB MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' +
@NEWPATH + RIGHT(PHYSICAL_NAME,CHARINDEX('\',REVERSE(PHYSICAL_NAME))) + ''');'
FROM SYS.MASTER_FILES WHERE DATABASE_ID = 2 FOR XML PATH(''))
EXEC ( @SQL )
Once you executed the above query, you need to restart the server to see the changes.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
You can also go to the previous file location and delete the old files from the folder.
It is important to know that the moving of TEMPDB files no longer supporting using backup & restore OR detaching & attaching(system databases).
Today, We faced an issue with one of the projects we engaged with.Project team was experiencing issues having different collation for TEMPDB and USERDB.The error message is given below:
“Cannot resolve the collation conflict between “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI” in the equal to operation”.
This was a clear indication that the issue is with the collation mismatch. When we further analysed the procedure, we could identify there is a temp table used and being used to match the records with USERDB table.
To understand the issue with collation in detail, we checked the collation of TEMPDB database and USERDB and found the collation is set as “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI”.
Use the below script to know the collation for databases:
Select name,COLLATION_NAME from sys.databases
As long as we are not sure of the difference in Collation between TEMPDB and USERDBs, as a quick fix, we recommanded to change the SQL Query as below while creating the temp tables, this would by default take the user database’s collation during the temp table creation:
CREATE TABLE #Table
VoucherID NVARCHAR(10) COLLATE database_default,
VocherName NVARCHAR(100) COLLATE database_default,
Call To Action:
1. Please make sure your USERDBs and systemDBs are in the same collation unless there is a requirement for explicit change.
2. When you want to change the Collation for a USERDB, Please check the Collation of TEMPDB as well.