Reg. TEMPDB Files

Question

Reg. TEMPDB Files

Agnii

Ten Centuries

Points: 1192
More actions
September 20, 2017 at 11:53 am

#340173

Hi,
I've a SQL server instance - 2014 and we dedicated a Scsi drive for Tempdb and created 8 different files of equal sizes (i.e. 15gb each 8 files) as server has 8 cpus. Due to some reason, now I found that 2 of tempdb files have different sizes than 15GB...Is this a problem?....if yes, how can it be corrected?...I tried setting back the tempdb file size to 15 GB but it goes back to 16.5 GB. Any help is appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

You need traceflags, 1117 and 1118 - read up on each first before implementing.
1117 would keep your files growing equally - https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Are the growths the same for all files?

I RARELY see the need for 8 files, but I have OFTEN seen too many files (even 8) make IO SLOWER due to head thrashing on too few rotating spindles.

Is trace flag 1117 and 1118 enabled?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Agnii Ten Centuries Points: 1192 More actions · Answer 3

Thanks for the replies...I've enabled trace flag 1117 n 1118