asked
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.
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.
@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.
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.
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.
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
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.
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.
2 People are following this question.
