December 30, 2008 at 7:35 am
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:
December 30, 2008 at 7:40 am
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/
December 30, 2008 at 8:11 am
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:
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.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
December 30, 2008 at 8:34 am
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
December 30, 2008 at 11:18 pm
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.
June 29, 2009 at 1:33 pm
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
June 29, 2009 at 2:22 pm
JC (6/29/2009)
Hi JerryI 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
June 29, 2009 at 2:49 pm
Hi Jerry
another question
are those tempdb files 2 thru 8 suppose to be .ndf files instead of .mdf files ??
Thanks
Jim
June 29, 2009 at 2:51 pm
Thanks Jeffery
can you answer that last question for me i just posted by any chance
Thanks
Jim
June 29, 2009 at 3:06 pm
JC (6/29/2009)
Hi Jerryanother 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
June 29, 2009 at 3:38 pm
Thanks Jeffrey
Jim
September 8, 2010 at 5:27 am
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
🙂
September 8, 2010 at 5:38 am
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
October 17, 2010 at 6:32 pm
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
October 17, 2010 at 10:35 pm
JC-3113 (6/29/2009)
Hi JerryI 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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply