January 12, 2015 at 3:51 pm
Hi,
I am hoping someone can answer the question of whether it is better to split tempdb or add another tempdb file? Which is better and why?
January 12, 2015 at 4:11 pm
Since you can't add a second tempdb, adding a file is the way to go. Here's a good review[/url] on how to pick tempdb file settings. There are a bunch of other links there too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2015 at 4:19 pm
The reason for asking is that I see NDF and MDF in various examples:
ALTER DATABASE [tempdb] ADD FILE (NAME = tempdb_2, FILEGROWTH = 10%, MAXSIZE = UNLIMITED, SIZE=300MB , FILENAME = 'C:\MSSQL\MyTempDB\tempdev_Data02.mdf')
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev_Data02', FILENAME = N'C:\MSSQL\MyTempDB\tempdev_Data02.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
January 12, 2015 at 6:48 pm
ndfs are just additional files.
ALWAYS have EQUAL SIZED AND GROWTH FACTOR tempdb files, no matter how many you have.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 13, 2015 at 7:28 am
Why do some people end their second etc. tempdb files with NDF and others with MDF?
January 13, 2015 at 7:31 am
You could call the files .txt and it would work the same. SQL doesn't care in the slightest what the extensions of its data files are.
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
January 13, 2015 at 8:00 am
What Gail said.
I don't know how that started, but I've seen examples of that approach to naming files going back to when I started working with SQL Server 4.2... Sybase SQL Server 4.2. I suspect it's a standard that someone at Sybase started and everyone emulated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply