MS SQL Server : Move TempDB to another location

First use the following codes to check the name of the files.
 
USE TempDB
GO
EXEC sp_helpfile
GO

By default, the names of the files are : tempdev and templog.

Then, you could run the following codes to move .mdf and .ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:tempdb_data.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:tempdb_log.ldf')
GO

Grant EXECUTE permission to all stored procedures

SELECT 'GRANT EXECUTE ON OBJECT::[' + ROUTINE_NAME + '] TO ' + @TO + ';'
FROM INFORMATION_SCHEMA.ROUTINES


Where @TO is the database role or user

Remove Hypothetical Indexes Generated by SQL DTA

Here is the sql statement to generate the scripts to drop hypothetical indexes that were generated by SQL Database Engine Tunning Advisor (DTA).


SELECT 'DROP INDEX [' + s.[name] + '] ON [' + object_name(s.[object_id]) +'] '
FROM sys.stats AS s
INNER JOIN sys.indexes AS i
    ON s.name=i.name
WHERE i.is_hypothetical=1 and s.[name] LIKE '_dta_ind%'


SELECT 'DROP STATISTICS  [' + object_name(s.[object_id]) + '].[' + s.[name] + '] '
FROM sys.stats AS s
WHERE s.name like '_dta_stat%'