How to make tempdb use > 1 datafile

Question

How to make tempdb use > 1 datafile

FNS

SSCrazy

Points: 2015
More actions
December 30, 2008 at 7:35 am

#197625

If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀
However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?
Will tempdb use this file if the primary (mdf) is full?
Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?
Will it all go horribly wrong? :crazy:

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I usually run tempdb with files = number of proc cores/2. Once I decide what the usual growth is I fix the file sizes so that tempdb does not normally ever grow.

as to where you put the files - your choice, I prefer dedicated drives, but make sure you don't use your slowest or most io restrictive drive

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

FNS (12/30/2008)
If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀
However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?
Will tempdb use this file if the primary (mdf) is full?
Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?
Will it all go horribly wrong? :crazy:

It won't go horribly wrong, but it is not a recommended configuration. If you have multiple CPU's and your workload causes bitmap contention from rapidly creating/dropping temp tables then adding multiple files can be helpful, see Paul Randals blog on the subject of multiple database files:

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

By adding a second file to a different drive only, your files are not going to be sized the same when the one drive runs out of space or the file is filled. The general recommendation is that the files be sized the same since SQL Server uses a proportional fill algorithm to stripe the data across the files based on file size and free space in the file.

Moving tempdb is a really easy process and generally only requires a quick restart of the SQL Services. If you are having space issues on your C Drive, then you should probably move tempdb to a different drive altogether. You likely have fragmentated free space and ar spreading the file across the fragmentated space since it is created at SQL Startup.

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

I am about to move our tempdb soon as well (due to unexpected crazy growth that fills up C drive)

move tempdb code

USE [master]

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\TempDB\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\TempDB\templog.ldf')

GO

select * from tempdb.dbo.sysfiles

add multiple files (1 per CPU) sample

-- 5GB file, grows at 100MB

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'T:\MSSQL\DATA\tempdb2.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'T:\MSSQL\DATA\tempdb3.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb4', FILENAME = N'T:\MSSQL\DATA\tempdb4.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb5', FILENAME = N'T:\MSSQL\DATA\tempdb5.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb6', FILENAME = N'T:\MSSQL\DATA\tempdb6.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb7', FILENAME = N'T:\MSSQL\DATA\tempdb7.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb8', FILENAME = N'T:\MSSQL\DATA\tempdb8.mdf' , SIZE = 5000MB , FILEGROWTH = 100MB )

GO

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 4

FNS (12/30/2008)
If a server keeps running out of space for the tempdb one option seems to be, move tempdb to a drive with more space. 😀
However, what's wrong with just adding another datafile (tempdb_data2.ndf) on a secondary drive?
Will tempdb use this file if the primary (mdf) is full?
Should I limit the mdf file to say 2Gb thus forcing SQL to use the ndf once this limit is reached?
Will it all go horribly wrong? :crazy:

Yes, u can use set size of perticular database files (.mdf,.ndf,.ldf) by altering/creating database.

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 5

Hi Jerry

I am looking at your code for tempdb files

I am new to sqlserver 2005

Not up to speed on TEMPDB files yet

i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?

is there one per tempddb file you created of just one for the entire set ?

Thanks

Jim

Jeffrey Williams SSC Guru Points: 90256 More actions · Answer 6

JC (6/29/2009)
Hi Jerry
I am looking at your code for tempdb files
I am new to sqlserver 2005
Not up to speed on TEMPDB files yet
i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?
is there one per tempddb file you created of just one for the entire set ?
Thanks
Jim

You only need one log file per database - and having additional files does not improve performance at all. Log files are written to sequentially so having another log file does not make sense.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

Hi Jerry

another question

are those tempdb files 2 thru 8 suppose to be .ndf files instead of .mdf files ??

Thanks

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Thanks Jeffery

can you answer that last question for me i just posted by any chance

Thanks

Jim

Jeffrey Williams SSC Guru Points: 90256 More actions · Answer 9

JC (6/29/2009)
Hi Jerry
another question
are those tempdb files 2 thru 8 suppose to be .ndf files instead of .mdf files ??
Thanks
Jim

The generally accepted naming standard is to name secondary data files using .ndf for the extension. However, SQL Server does not care what extension is actually used.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

JC-3113

SSCrazy Eights

Points: 8371

June 29, 2009 at 3:38 pm

#1017263

Thanks Jeffrey

Jim

sujiakm SSC Eights! Points: 955 More actions · Answer 11

Hi Jerry,

All the secondary datafiles of Tempdb are located in the same T:\ drive. Do we really require those 8 secondary tempdb files, while the database is created with unrestricted file growth on T:\.

How does SQL Server utilizes these T:\ .mdf(.ndf) files one by one.

Thanks

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Please post new questions in a new thread. Thank you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yu.lin Grasshopper Points: 15 More actions · Answer 13

Have you test this configuration on the tempdb?

How does the files group works if not configure the same size

Primary mdf data 1 2048 MB

ndf data 2 2048 MB

ndf data 3 9600 MB

when the data fill up the data 1 and data 2. How it works in data 3? Fully uses the 9600 MB or use 2048 only in SQL server 2005\2008?

Thank

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

JC-3113 (6/29/2009)
Hi Jerry
I am looking at your code for tempdb files
I am new to sqlserver 2005
Not up to speed on TEMPDB files yet
i understand the creation of the multiple tempdb#.mdf files, but what about the templog.ldf file ?
is there one per tempddb file you created of just one for the entire set ?
Thanks
Jim

There's actually a topic in Books Online (the free help-system that comes with SQL Server) on how to move TempDB step by step. My recommendation is to read up on that bad boy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)