SQL Server - Moving and Increasing number of TempDB

Question

Based on best practices recommendation, we want to do the following

1. Move tempDb to new dedicated drive
2. Increase number of tempdb to 4 (all equally sized)

Can anyone please share the commands, as I need to do on production want to be extra careful.

Answer 1

1) move tempdb From article http://support.microsoft.com/kb/224071 For SQL2005:

use tempdb
go
sp_helpfile
go
use master
go
--Then for each file You want to move, use: (name is logical name, found in column name from sp_helpfile, filename is new file path)
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go

Please, note that You have to restart sql server after this. After restart, You can delete tempdb files from original locations.

2) You can't increase number of tempdbs, but You can increase number of tempdb data files exactly the same way You add data files to any other database: http://msdn.microsoft.com/en-us/library/ms189253.aspx

ALTER DATABASE TempDb
ADD FILE 
( NAME = logical_test_name1, -- here goes logical file name
  FILENAME = 'e:\your_location\tempdbfile1.ndf', -- here goes path to data file
  SIZE = 5000MB,
  FILEGROWTH = 500MB)