6

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 another 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’
edgarmtze
  • 24,683
  • 80
  • 235
  • 386

1 Answers1

8

This can be done in the server properties.

enter image description here

  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.

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Chase Florell
  • 46,378
  • 57
  • 186
  • 376
  • I am having some trouble, I did that, and now I can not connect to server, What did I do wrong?? – edgarmtze May 07 '11 at 02:43
  • please tell me you read the instructions and didn't just copy and past the script. – Chase Florell May 07 '11 at 02:44
  • 1
    if so, start sql server from command line, with sqlservr -f -m; connect and re-issue the ALTER commands with a valid PATH,e.g. d:\YOUR_PATH_HERE\tempdb.mdf; if d: or e: are valid paths but read-only, i.e.DVD, that could cause a failure to start. – SqlACID May 07 '11 at 11:49
  • I do the procedure again and I got it working, following the link in my answer – edgarmtze May 12 '11 at 00:21
  • and after setting new tempdb location you need to check if SERVICE account has permissions to new file location on read|write – Fragment Jan 27 '15 at 04:54