Tempdb and prop fill

Question

Tempdb and prop fill

sqlguy7777

Right there with Babe

Points: 720
More actions
November 22, 2018 at 6:32 pm

#378707

Hi
We have a SQL 2012 server with Tempdb that has 4 data files.
The Tempdb disk is 200 GB.
We have traceflag 1117 & 1118 turned off.
One data file is 20 GB ( max size limited to 50GB )
Three data files are 2 GB each ( max size limited to 50GB )
We ran out of Tempdb space
What I'm asking is :
Will SQL fill the larger ( 20 GB ) file first due to SQLs proportional fill algorithm, and when it gets to its max size of 50 GB then data will overflow into the other smaller Tempdb data files ,
or
will SQL start filling the 40 GB file and grow it up to around 50 Gb ( max size allowed for that file ) and then SQL will say the disk is full?
My hope is that SQL is smart enough to allow data to overflow into the smaller data files once the first data file fills up.
Thoughts welcome. 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 1

sqlguy7777 - Thursday, November 22, 2018 6:32 PM
Hi
We have a SQL 2012 server with Tempdb that has 4 data files.
The Tempdb disk is 200 GB.
We have traceflag 1117 & 1118 turned off.
One data file is 20 GB ( max size limited to 50GB )
Three data files are 2 GB each ( max size limited to 50GB )
We ran out of Tempdb space
What I'm asking is :
Will SQL fill the larger ( 20 GB ) file first due to SQLs proportional fill algorithm, and when it gets to its max size of 50 GB then data will overflow into the other smaller Tempdb data files ,
or
will SQL start filling the 40 GB file and grow it up to around 50 Gb ( max size allowed for that file ) and then SQL will say the disk is full?
My hope is that SQL is smart enough to allow data to overflow into the smaller data files once the first data file fills up.
Thoughts welcome. 🙂

What were the size of the each of the files when it ran out of space?

--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)

sqlguy7777 Right there with Babe Points: 720 More actions · Answer 2

Hi Jeff,

I am not 100% sure, as an operator restarted SQL to clear tempdb.

I guess what I'm trying to work out is whether one file hitting its maximum limit can stop whats happening, or whether the work in tempdb overflows from one full file to the next to the next to the next ( like log files ) as they fill up?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Will SQL fill the larger ( 20 GB ) file first due to SQLs proportional fill algorithm, and when it gets to its max size of 50 GB then data will overflow into the other smaller Tempdb data files ,

Proportional fill works based on the free space within a file, if you have a large free space in that 50GB file it grow first, when it is comes all equal with free space, it will use the round robin.We can also ignore the Proportional fill this by using a trace flag 1118.

will SQL start filling the 40 GB file and grow it up to around 50 Gb ( max size allowed for that file ) and then SQL will say the disk is full?

No.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

If you have 4 data files for the tempdb, your best configuration is to have them the same size.

Jeff Moden SSC Guru Points: 1003874 More actions · Answer 5

muthukkumaran Kaliyamoorthy - Friday, November 23, 2018 1:24 AM
Will SQL fill the larger ( 20 GB ) file first due to SQLs proportional fill algorithm, and when it gets to its max size of 50 GB then data will overflow into the other smaller Tempdb data files ,

Proportional fill works based on the free space within a file, if you have a large free space in that 50GB file it grow first, when it is comes all equal with free space, it will use the round robin.We can also ignore the Proportional fill this by using a trace flag 1118.

will SQL start filling the 40 GB file and grow it up to around 50 Gb ( max size allowed for that file ) and then SQL will say the disk is full?

No.

Not quite right. Trace flag 1117 is for proportional fill, not 1118. Trace flag 1118 changes the allocations in TempDB from "page" to "extent".

The real problem here has little to do with proportional fill, IMHO. The real problem is how much TempDB is being used by a single action. Someone needs to find that single action and fix it.

Also, prior to SQL Server 2016, TF 1117 was global in nature so I don't use it on things prior to 2016. There are places (partitioned tables, for example) where I don't actually want all of the file groups to be the same size or evenly grown.

--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)

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

Thanks Jeff for the correction TF 1117. I was used a long time back a combined FG+ partitioned tables.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/