tempdb multiple files

Question

We've been advised to create more tempdb files for our prod server as currently we have only 1 tempdb file.

Question:

1/ How do I create multiple tempdb files ? Is it correct that a 1st tempdb file (tempdev) is primary with .mdf, then add a 2nd tempdb file with .ndf, and so on ??

2/ Does it require any down time to relocate temdb file to a different drive ?

Answer 1

>>1/ How do I create multiple tempdb files ? Is it correct that a 1st tempdb file (tempdev) is primary with .mdf, then add a 2nd tempdb file with .ndf, and so on ??

Yes, make sure that all files are in the same size

>>2/ Does it require any down time to relocate temdb file to a different drive ?

--tempdb
USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO

Then after restart the service (down time) sql server will recognize the new location of tempdb database.

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Would you please check-out below threads

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c803c9f2-2bca-4d4a-a8d1-b914627107dc

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/1c704ab8-0305-4e00-8f12-275eb43325c1

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/ec6f0c06-f7b4-4520-8ae2-3e096941a59d

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
My Blog
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 3

pls show step by step how to create multiple tempdb files ?

The 1st one is tempdb.mdf, how do I find its size ?

Answer 4

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdev1.ndf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdev2.ndf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 5

Hi,

You must read the following article with others advice.

you can create multiple data files of equal size.  It is recommended that you add one file per CPU (per core, not per socket).

http://download.microsoft.com/downlo...WithTempDB.doc