SQL Server 2008 TempDB on another HD

Question

I would like all TempDB related TempDB be stored on a separate HD.

I have this new HD with a size of 500 GB as my E:\ drive.

How to use or move TempDB from one drive to another drive?

------------------------------ ------------- EDIT ------ --------
After completing the tutorial, when the server restarts, I get the message:

Request failed or service did not respond in a timely manner. Consult the event log or other application error logs for details.

• I can’t start anymore, any suggestion? Does it relate to the database path. (the location of the database, for example tempdb.mdf, is different from the folder 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'

Answer 1

This can be done in the server properties.

• Right click on server instance
• Click Properties
• Click "Database Settings"
• Change “Log” to any desired path (including alternate hard drive)

EDIT

I misunderstood the above question ... I suppose I must learn to read. The instructions above show how to move LOG DB to another hard drive.

Instructions found HERE will show you how to move TempDB

Open Query Analyzer and connect to the server. Run this script to get the file names used for TempDB.

 USE TempDB GO EXEC sp_helpfile GO 

The results will be something like this:

 | name | fileid | filename | filegroup | size | |----------|---------|---------------------------------------------------------|------------|----------| | tempdev | 1 | C:Program FilesMicrosoft SQLServerMSSQLdatatempdb.mdf | PRIMARY | 16000 KB | | templog | 2 | C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf | NULL | 1024 KB | 

Along with other database related information. File names are usually tempdev and demplog by default. These names will be used in the following expression. Run the following code to move the mdf and ldf files.

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

The definition of TempDB has changed. However, no change to TempDB occurs until SQL Server is restarted. Please stop and restart SQL Server and it will create TempDB files in new places.