Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 27 '11 at 14:23

This question came from our site for professional and enthusiast programmers.

1 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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.