Tempdb

Question

Tempdb

giri10488

Right there with Babe

Points: 729
More actions
December 12, 2017 at 12:00 pm

#104469

Hi All,
We are facing Temp DB full issue at our organisation. for that I want to perform below steps
tempdb getting full so I have to divide the tempdb= no of core like 4 tempdb & the setting
3 datafile = None auto growth setting
last 1 datafile = 10% auto growth
This would resolve tempdb getting full issue but I need Microsoft approved document for this resolution. I have done this setting in my previous environments so many server & I believe this would resolve the issue definitely.
Can some one help me getting any document or article from Microsoft for this fix.
Thanks
Giriraj

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

giri10488 - Tuesday, December 12, 2017 12:00 PM
Hi All,
We are facing Temp DB full issue at our organisation. for that I want to perform below steps
tempdb getting full so I have to divide the tempdb= no of core like 4 tempdb & the setting
3 datafile = None auto growth setting
last 1 datafile = 10% auto growth
This would resolve tempdb getting full issue but I need Microsoft approved document for this resolution. I have done this setting in my previous environments so many server & I believe this would resolve the issue definitely.
Can some one help me getting any document or article from Microsoft for this fix.
Thanks
Giriraj

How much total drive space does TempDB grow to?

Also, that 10% auto-growth thing... that's going to kill you. Both the MDF and LDF files need to be set to a fixed amount of MB and the initial size needs to be something that you would normally expect each file to be.

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

Are any of the new tempdb data files on a different disk? Too much data to fit on the disk is still going to be too big when split into four files on the same disk.

giri10488 Right there with Babe Points: 729 More actions · Answer 3

Total 250 GB space on tempdb consuming whole drive..i have divide the tempdb & its working fine now without any issues. I just need the microsoft document to understand more that how dividing tempdb will resolve the space issue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Dividing TempDB won't do anything about space problems. If TempDB is 250GB, it's 250GB, doesn't matter if it's 1 file of 10.

If TempDB is getting full, your options are to make it larger, or identify code that's using TempDB heavily and change it so that it's not using as much.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 5

giri10488 - Tuesday, December 12, 2017 3:52 PM
Total 250 GB space on tempdb consuming whole drive..i have divide the tempdb & its working fine now without any issues. I just need the microsoft document to understand more that how dividing tempdb will resolve the space issue

Microsoft's documentation about increasing the number of tempdb files is to address contention issues, not space issue. Here is the documentation about that along with links to a few other articles on the same subject of number of tempdb files:
Recommendations to reduce allocation contention in SQL Server tempdb database

Sue

giri10488 Right there with Babe Points: 729 More actions · Answer 6

But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

Sue_H SSC Guru Points: 90860 More actions · Answer 7

giri10488 - Tuesday, December 12, 2017 4:20 PM
But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

Sorry but it does not affect space. There is no documentation for it resolving space issues as it doesn't do that.

Sue

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

giri10488 - Tuesday, December 12, 2017 4:20 PM
But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

I have a couple of Terabyte databases on one server. It not only is subject to a fair bit of OLTP, but also has huge batch file runs for ETL, the related validations, and much more. We have a total of 16GB in eight 2GB files for TempDB and the log file is also 2GB. They've NEVER grown.

If you have 250GB of TempDB, you have some really, really bad code that needs to be identified and fixed. You also need to fix the log file growth to be a fixed amount rather than a percentage. No amount of trickery is going to solve this problem. You need to find the code that's doing this and fix it.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

giri10488 - Tuesday, December 12, 2017 4:20 PM
you can also test it & will definately see that it will resolve space issue.

No, it won't. It can't, because a 250GB tempDB is 250GB, no matter how many data files there are.

Now, if you went and took a 10GB TempDB and added 3 more 10GB files, then sure, that will resolve space issues, but you could have increased the single file to 40GB for the same effect.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass