SQL Server 2008 TempDB on other HD

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

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

How would I use or move TempDB from one drive to a开发者_Python百科nother drive?

------------------------------EDIT---------------------------

After following the tutorial, when restarting the Server I get the message:

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

• I can not start it anymore, any suggestion? Does it have to do with the database path. (the location of the database such as tempdb.mdf is different than the folder 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA’

---

This can be done in the server properties.

1. Right click on your server instance
2. Click "Properties"
3. Click "Database Settings"
4. Change "Log" to whatever path you want (including alternate HDD)

EDIT

I misunderstood the above question... I suppose I should learn to read. The above instructions show how to move the LOG DB to a different hard drive.

The instructions found HERE will show you how to move the TempDB

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

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:

| 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 information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move 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 the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.