May 10, 2018 at 9:14 am
My TempDB Database currently has 4 files associated with it. I want to add 4 more tempdb files(server has 8 cores). Obviously I want all of the files to be allocated the same space and filegrowth=0. Currently the tempdev file is much larger
than the other three files and I want them all the same size. Do I have to do a shrinkfile(tempdev,nn) before add the new files and modify the existing files sizes on the existing tempdb files? I was always under the impression that once you
restart sql server that the tempdb gets recreated with the specified space allocation.
Thanks in advance.
May 10, 2018 at 9:21 am
Add your needed files and to the size specifications you want and you are correct in once you restart SQL Server the temp is recreated.
Also the add of 4 more i would only do that AFTER you have tested to see if there will be any gain but to add 4 more just because you have 8 cores is a slippery slope and a whole new topic.. LOL
DHeath
DHeath
May 10, 2018 at 9:57 am
ericwenger1 - Thursday, May 10, 2018 9:14 AMMy TempDB Database currently has 4 files associated with it. I want to add 4 more tempdb files(server has 8 cores). Obviously I want all of the files to be allocated the same space and filegrowth=0. Currently the tempdev file is much larger
than the other three files and I want them all the same size. Do I have to do a shrinkfile(tempdev,nn) before add the new files and modify the existing files sizes on the existing tempdb files? I was always under the impression that once you
restart sql server that the tempdb gets recreated with the specified space allocation.Thanks in advance.
You may want to keep in mind you don't have to have one file per core and if you haven't already, might want to analyze things for contention with tempdb first. The following is a good article about the subject:
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
Sue
May 10, 2018 at 12:19 pm
Sue_H - Thursday, May 10, 2018 9:57 AMericwenger1 - Thursday, May 10, 2018 9:14 AMMy TempDB Database currently has 4 files associated with it. I want to add 4 more tempdb files(server has 8 cores). Obviously I want all of the files to be allocated the same space and filegrowth=0. Currently the tempdev file is much larger
than the other three files and I want them all the same size. Do I have to do a shrinkfile(tempdev,nn) before add the new files and modify the existing files sizes on the existing tempdb files? I was always under the impression that once you
restart sql server that the tempdb gets recreated with the specified space allocation.Thanks in advance.
You may want to keep in mind you don't have to have one file per core and if you haven't already, might want to analyze things for contention with tempdb first. The following is a good article about the subject:
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor coreSue
I just read that again this morning when I saw this question. I was charging in to supply the link and there it is. Well done.
"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
May 11, 2018 at 8:29 am
Grant Fritchey - Thursday, May 10, 2018 12:19 PMSue_H - Thursday, May 10, 2018 9:57 AMericwenger1 - Thursday, May 10, 2018 9:14 AMMy TempDB Database currently has 4 files associated with it. I want to add 4 more tempdb files(server has 8 cores). Obviously I want all of the files to be allocated the same space and filegrowth=0. Currently the tempdev file is much larger
than the other three files and I want them all the same size. Do I have to do a shrinkfile(tempdev,nn) before add the new files and modify the existing files sizes on the existing tempdb files? I was always under the impression that once you
restart sql server that the tempdb gets recreated with the specified space allocation.Thanks in advance.
You may want to keep in mind you don't have to have one file per core and if you haven't already, might want to analyze things for contention with tempdb first. The following is a good article about the subject:
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor coreSue
I just read that again this morning when I saw this question. I was charging in to supply the link and there it is. Well done.
That one file per core myth just won't go away. Wish there would have been more focus on checking for contention in the first place. Oh well...it's Friday 🙂
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply