x

Move tempdb

Can I move the tempdb database to a separate drive and leave the rest of the system databases (master, msdb) on a separate drive? Please advice, I would like to apply this to the production database so that the system does not shutdown.
more ▼

asked Oct 23, 2010 at 03:55 PM in Default

Chitra gravatar image

Chitra
148 15 17 19

@user-489 if my answer got you what you needed, do you mind checking the check box to mark it as the answer.
Oct 25, 2010 at 12:05 PM Tim
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Yes, use the alter database command. Of course change the drive letter and location in the script below to your naming standard and drive(s).

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

Then restart your sql service.

more ▼

answered Oct 23, 2010 at 04:03 PM

Tim gravatar image

Tim
36.4k 38 41 139

Short and sweet.
Oct 23, 2010 at 05:26 PM Blackhawk-17
Thank you so much for the quick response. I will be running the dry run in dev.
Oct 23, 2010 at 06:49 PM Chitra
That is what we are here for. So many others have helped me out on here, it is nice to give back.
Oct 23, 2010 at 06:59 PM Tim
@TRAD +1 I would like to add just one cent: it is a very good idea to have tempdb on the separate drive, and if there is a need to have more than one tempdb file then it is best to store each on the separate physical drive, so each file gets it own spindle. This implies that tempdb file(s) should never be on the same spindle with any non-system database files if possible.
Oct 25, 2010 at 08:48 AM Oleg
Yes @Oleg (+1), and you might consider using a SSD for the tempdb.
Oct 25, 2010 at 08:54 AM Mark
(comments are locked)
10|1200 characters needed characters left

Just to add...tempdb is recreated each time the SQL Service starts, hence why you have to restart after changing the locations, so be aware that the 'old' tempdb files will still exist in the original location once you have restarted.

These can be safely deleted once the 'new' tempdb is being used.
more ▼

answered Oct 24, 2010 at 06:45 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Good follow-up. Seeing the leftover files can be a bit misleading.
Oct 25, 2010 at 09:27 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Also, just to add, size the tempdb data files properly so they do not autogrow in tiny chunks every time causing unnecessary fragmentation. Same goes for the log file. Take into consideration that the log file can take some time to create at service startup if its large, as the database will zero-out the file.
more ▼

answered Oct 25, 2010 at 12:33 PM

ozamora gravatar image

ozamora
1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left

Just one more note, 1 tempdb file per processor core is a [myth][1]. You can start with 1/4 and then go up with 1/2; needs to be tested.

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
more ▼

answered Oct 25, 2010 at 12:42 PM

ozamora gravatar image

ozamora
1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left
I recently read that if your cores are less than 8 go with one per core, then if you have more than 8 start with 8 and increase in units of 4.
more ▼

answered Oct 03, 2012 at 04:29 PM

Scotty gravatar image

Scotty
350 3 5 5

It depends... if you tie your cores up with I/O then they can't do other processing at the same time. Each workload will require analysis and a balance.
Oct 03, 2012 at 05:46 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x50
x19

asked: Oct 23, 2010 at 03:55 PM

Seen: 2023 times

Last Updated: Oct 03, 2012 at 05:46 PM